In SQL Server, the TRUSTWORTHY<\/strong> database setting can grant elevated permissions across a database boundary, influencing how certain modules execute, particularly those using In this blog, we\u2019ll dive into the purpose of the TRUSTWORTHY<\/strong> setting, examine potential security implications, and explore secure alternatives using certificate signing to control module access.<\/p>\n The TRUSTWORTHY<\/strong> setting is a database property that allows SQL Server modules to inherit security context from the database level, granting permissions that might extend beyond the database\u2019s boundary. This feature is particularly useful when you need a stored procedure or assembly within a database to interact with other databases or require permissions at a server level.<\/p>\n Consider a scenario where you have a database, TrustyDB<\/strong>, that hosts a stored procedure requiring access to data in another database, FinanceDB<\/strong>. By setting TRUSTWORTHY<\/strong> to The TRUSTWORTHY<\/strong> setting broadens the permissions scope of the database, creating the potential for privilege escalation. For instance, if an attacker gains control over a user or module within a TRUSTWORTHY<\/strong>-enabled database, they may exploit these elevated permissions to access sensitive data or perform unauthorized actions across the SQL Server instance.<\/p>\n Consider the following risks:<\/p>\n For these reasons, enabling TRUSTWORTHY<\/strong> should only be done as a last resort, after evaluating security requirements and considering alternative solutions.<\/p>\n The recommended alternative to using the TRUSTWORTHY<\/strong> setting is certificate signing. By signing specific modules with a certificate, you can grant only the required permissions to the exact code needing elevated access, all without needing to mark the entire database as TRUSTWORTHY<\/strong>.<\/p>\n Using certificates provides these benefits:<\/p>\n Let\u2019s say you have a stored procedure in TrustyDB<\/strong> that needs to access data in another database, FinanceDB<\/strong>. Instead of enabling TRUSTWORTHY<\/strong> on TrustyDB<\/strong>, you can use certificate signing to grant cross-database permissions only to the specific procedure.<\/p>\n This stored procedure queries FinanceDB.dbo.EmployeeSalary<\/strong> for sensitive data, requiring elevated cross-database permissions.<\/p>\n Generate a certificate specifically for signing this stored procedure.<\/p>\n Use the certificate to sign Switch to the FinanceDB<\/strong> database.<\/p>\n Now, when Suppose you have a diagnostic stored procedure in TrustyDB<\/strong> that needs server-level permissions, like Use the certificate to create a login in the master<\/strong> database.<\/p>\n Now, Let\u2019s say you have a CLR assembly in TrustyDB<\/strong> that requires Deploy the assembly, marking it as This setup allows MyExternalAssembly<\/strong> to execute with The TRUSTWORTHY<\/strong> setting in SQL Server enables elevated permissions but comes with significant security risks. Instead of enabling TRUSTWORTHY<\/strong>, certificate signing provides a secure alternative that grants precise permissions to specific modules. By signing modules with certificates, you reduce the scope of potential misuse, making your SQL Server environment more secure. With these best practices, you can achieve elevated permissions where needed, without the security trade-offs that TRUSTWORTHY<\/strong> introduces.<\/p>\n","protected":false},"excerpt":{"rendered":" In SQL Server, the TRUSTWORTHY database setting can grant elevated permissions across a database boundary, influencing how certain modules execute, particularly those using WITH EXECUTE AS or unsafe CLR assemblies. Enabling TRUSTWORTHY allows SQL Server to “trust” that the database owner and associated users won\u2019t misuse elevated permissions to perform unauthorized actions. However, this setting comes with significant security risks if used improperly.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[132,35,82,29],"tags":[377,378,380,196,379,131,381,376],"class_list":["post-878","post","type-post","status-publish","format-standard","hentry","category-clr-integration","category-data-integrity","category-database-configuration","category-security","tag-certificate-signing","tag-clr-assemblies","tag-cross-database-access","tag-database-security","tag-permission-management","tag-sql-server","tag-sql-server-permissions","tag-trustworthy-setting"],"yoast_head":"\nWITH EXECUTE AS<\/code> or unsafe CLR assemblies. Enabling TRUSTWORTHY<\/strong> allows SQL Server to “trust” that the database owner and associated users won\u2019t misuse elevated permissions to perform unauthorized actions. However, this setting comes with significant security risks if used improperly.<\/p>\nThe Role of TRUSTWORTHY in SQL Server<\/h2>\n
Example Use Case:<\/h3>\n
ON<\/code> for TrustyDB<\/strong>, you can allow the stored procedure to interact with FinanceDB<\/strong> using elevated permissions.<\/p>\nUSE TrustyDB;\nGO\n\nCREATE PROCEDURE dbo.usp_GetEmployeeSalary\nAS\nBEGIN\n SELECT Salary FROM FinanceDB.dbo.EmployeeSalary;\nEND;<\/code><\/pre>\nSecurity Risks with TRUSTWORTHY<\/h2>\n
\n
Secure Alternatives to TRUSTWORTHY<\/h2>\n
\n
Scenario 1: Executing a Cross-Database Query with Elevated Permissions<\/h3>\n
Step-by-Step Solution<\/h4>\n
Create the Stored Procedure in TrustyDB<\/h5>\n
USE TrustyDB;\nGO\n\nCREATE PROCEDURE dbo.usp_GetEmployeeSalary\nAS\nBEGIN\n SELECT Salary FROM FinanceDB.dbo.EmployeeSalary;\nEND;<\/code><\/pre>\nCreate a Certificate in TrustyDB<\/h5>\n
CREATE CERTIFICATE CertForCrossDbAccess\n WITH SUBJECT = 'Access FinanceDB for salary data';<\/code><\/pre>\nSign the Stored Procedure<\/h5>\n
usp_GetEmployeeSalary<\/code>.<\/p>\nADD SIGNATURE TO dbo.usp_GetEmployeeSalary\n BY CERTIFICATE CertForCrossDbAccess;<\/code><\/pre>\nCreate a Login from the Certificate in the FinanceDB Database<\/h5>\n
USE FinanceDB;\nGO\n\nCREATE LOGIN CertLogin FROM CERTIFICATE CertForCrossDbAccess;\nGRANT SELECT ON dbo.EmployeeSalary TO CertLogin;<\/code><\/pre>\nVerify Cross-Database Access<\/h5>\n
usp_GetEmployeeSalary<\/code> runs, it will execute with the permissions granted to CertLogin<\/code>, allowing it to access FinanceDB<\/strong> without requiring the TRUSTWORTHY<\/strong> setting.<\/p>\nScenario 2: Granting Elevated Server Permissions for a Diagnostic Procedure<\/h3>\n
VIEW SERVER STATE<\/code>, to retrieve performance metrics.<\/p>\nCreate the Diagnostic Stored Procedure<\/h4>\n
USE TrustyDB;\nGO\n\nCREATE PROCEDURE dbo.usp_ServerDiagnostics\nAS\nBEGIN\n SELECT * FROM sys.dm_exec_requests;\nEND;<\/code><\/pre>\nCreate a Certificate in TrustyDB and Sign the Procedure<\/h4>\n
CREATE CERTIFICATE CertForDiagnostics\n WITH SUBJECT = 'Diagnostics certificate';\nGO\n\nADD SIGNATURE TO dbo.usp_ServerDiagnostics\n BY CERTIFICATE CertForDiagnostics;<\/code><\/pre>\nCreate a Login in master and Assign Required Permissions<\/h4>\n
USE master;\nGO\n\nCREATE LOGIN CertLogin_Diagnostics FROM CERTIFICATE CertForDiagnostics;\nGRANT VIEW SERVER STATE TO CertLogin_Diagnostics;<\/code><\/pre>\nusp_ServerDiagnostics<\/code> can execute VIEW SERVER STATE<\/code> without requiring the TRUSTWORTHY<\/strong> setting on TrustyDB<\/strong>.<\/p>\nScenario 3: Granting CLR Assembly Permissions with Certificate Signing<\/h3>\n
EXTERNAL_ACCESS<\/code> to interact with external files.<\/p>\nDeploy the CLR Assembly with EXTERNAL_ACCESS<\/h4>\n
EXTERNAL_ACCESS<\/code>.<\/p>\nCREATE ASSEMBLY MyExternalAssembly\nFROM 'C:\\path\\to\\assembly.dll'\nWITH PERMISSION_SET = EXTERNAL_ACCESS;<\/code><\/pre>\nCreate a Certificate and Sign the Assembly<\/h4>\n
CREATE CERTIFICATE CertForCLRAccess\n WITH SUBJECT = 'Certificate for CLR EXTERNAL_ACCESS';\nGO\n\nADD SIGNATURE TO ASSEMBLY MyExternalAssembly\n BY CERTIFICATE CertForCLRAccess;<\/code><\/pre>\nCreate a Login from the Certificate in master and Grant Access<\/h4>\n
USE master;\nGO\n\nCREATE LOGIN CertLogin_CLR FROM CERTIFICATE CertForCLRAccess;\nGRANT EXTERNAL ACCESS ASSEMBLY TO CertLogin_CLR;<\/code><\/pre>\nEXTERNAL_ACCESS<\/code> permissions without needing TRUSTWORTHY<\/strong> enabled on TrustyDB<\/strong>.<\/p>\nAdditional Tips<\/h2>\n
\n
USE TrustyDB;\nDROP CERTIFICATE OldCertForAccess;\nCREATE CERTIFICATE NewCertForAccess WITH SUBJECT = 'Updated Certificate';<\/code><\/pre>\n<\/li>\nConclusion<\/h2>\n