Understanding SQL Server’s TRUSTWORTHY Database Setting

Introduction

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’t misuse elevated permissions to perform unauthorized actions. However, this setting comes with significant security risks if used improperly.

In this blog, we’ll dive into the purpose of the TRUSTWORTHY setting, examine potential security implications, and explore secure alternatives using certificate signing to control module access.

The Role of TRUSTWORTHY in SQL Server

The TRUSTWORTHY 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’s 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.

Example Use Case:

Consider a scenario where you have a database, TrustyDB, that hosts a stored procedure requiring access to data in another database, FinanceDB. By setting TRUSTWORTHY to ON for TrustyDB, you can allow the stored procedure to interact with FinanceDB using elevated permissions.

USE TrustyDB;
GO

CREATE PROCEDURE dbo.usp_GetEmployeeSalary
AS
BEGIN
    SELECT Salary FROM FinanceDB.dbo.EmployeeSalary;
END;

Security Risks with TRUSTWORTHY

The TRUSTWORTHY 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-enabled database, they may exploit these elevated permissions to access sensitive data or perform unauthorized actions across the SQL Server instance.

Consider the following risks:

• Privilege Escalation: Users with elevated permissions within a TRUSTWORTHY database can execute code across other databases or even access server-level resources.
• CLR Assemblies: Unsafe CLR assemblies can be executed within the database, allowing potentially risky interactions with external systems.
• Unauthorized Cross-Database Access: Modules can access data in other databases without explicit permissions, which could lead to data exposure.

For these reasons, enabling TRUSTWORTHY should only be done as a last resort, after evaluating security requirements and considering alternative solutions.

Secure Alternatives to TRUSTWORTHY

The recommended alternative to using the TRUSTWORTHY 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.

Using certificates provides these benefits:

• Granular Permissions: Permissions are limited to specific modules.
• Minimal Security Scope: Only the modules that truly need access are granted the necessary permissions.
• Controlled Cross-Database Access: Certificates allow controlled permissions at the module level, reducing the risk of unintended privilege escalation.

Scenario 1: Executing a Cross-Database Query with Elevated Permissions

Let’s say you have a stored procedure in TrustyDB that needs to access data in another database, FinanceDB. Instead of enabling TRUSTWORTHY on TrustyDB, you can use certificate signing to grant cross-database permissions only to the specific procedure.

Step-by-Step Solution

Create the Stored Procedure in TrustyDB

This stored procedure queries FinanceDB.dbo.EmployeeSalary for sensitive data, requiring elevated cross-database permissions.

USE TrustyDB;
GO

CREATE PROCEDURE dbo.usp_GetEmployeeSalary
AS
BEGIN
    SELECT Salary FROM FinanceDB.dbo.EmployeeSalary;
END;

Create a Certificate in TrustyDB

Generate a certificate specifically for signing this stored procedure.

CREATE CERTIFICATE CertForCrossDbAccess
    WITH SUBJECT = 'Access FinanceDB for salary data';

Sign the Stored Procedure

Use the certificate to sign usp_GetEmployeeSalary.

ADD SIGNATURE TO dbo.usp_GetEmployeeSalary
    BY CERTIFICATE CertForCrossDbAccess;

Create a Login from the Certificate in the FinanceDB Database

Switch to the FinanceDB database.

USE FinanceDB;
GO

CREATE LOGIN CertLogin FROM CERTIFICATE CertForCrossDbAccess;
GRANT SELECT ON dbo.EmployeeSalary TO CertLogin;

Verify Cross-Database Access

Now, when usp_GetEmployeeSalary runs, it will execute with the permissions granted to CertLogin, allowing it to access FinanceDB without requiring the TRUSTWORTHY setting.

Scenario 2: Granting Elevated Server Permissions for a Diagnostic Procedure

Suppose you have a diagnostic stored procedure in TrustyDB that needs server-level permissions, like VIEW SERVER STATE, to retrieve performance metrics.

Create the Diagnostic Stored Procedure

USE TrustyDB;
GO

CREATE PROCEDURE dbo.usp_ServerDiagnostics
AS
BEGIN
    SELECT * FROM sys.dm_exec_requests;
END;

Create a Certificate in TrustyDB and Sign the Procedure

CREATE CERTIFICATE CertForDiagnostics
    WITH SUBJECT = 'Diagnostics certificate';
GO

ADD SIGNATURE TO dbo.usp_ServerDiagnostics
    BY CERTIFICATE CertForDiagnostics;

Create a Login in master and Assign Required Permissions

Use the certificate to create a login in the master database.

USE master;
GO

CREATE LOGIN CertLogin_Diagnostics FROM CERTIFICATE CertForDiagnostics;
GRANT VIEW SERVER STATE TO CertLogin_Diagnostics;

Now, usp_ServerDiagnostics can execute VIEW SERVER STATE without requiring the TRUSTWORTHY setting on TrustyDB.

Scenario 3: Granting CLR Assembly Permissions with Certificate Signing

Let’s say you have a CLR assembly in TrustyDB that requires EXTERNAL_ACCESS to interact with external files.

Deploy the CLR Assembly with EXTERNAL_ACCESS

Deploy the assembly, marking it as EXTERNAL_ACCESS.

CREATE ASSEMBLY MyExternalAssembly
FROM 'C:\path\to\assembly.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;

Create a Certificate and Sign the Assembly

CREATE CERTIFICATE CertForCLRAccess
    WITH SUBJECT = 'Certificate for CLR EXTERNAL_ACCESS';
GO

ADD SIGNATURE TO ASSEMBLY MyExternalAssembly
    BY CERTIFICATE CertForCLRAccess;

Create a Login from the Certificate in master and Grant Access

USE master;
GO

CREATE LOGIN CertLogin_CLR FROM CERTIFICATE CertForCLRAccess;
GRANT EXTERNAL ACCESS ASSEMBLY TO CertLogin_CLR;

This setup allows MyExternalAssembly to execute with EXTERNAL_ACCESS permissions without needing TRUSTWORTHY enabled on TrustyDB.

Additional Tips

• Periodic Certificate Renewal: Rotate certificates periodically to enhance security. Drop the old certificate and replace it with a new one.

  USE TrustyDB;
  DROP CERTIFICATE OldCertForAccess;
  CREATE CERTIFICATE NewCertForAccess WITH SUBJECT = 'Updated Certificate';

• Auditing and Monitoring: Keep an eye on which modules have been signed and which certificates and logins are in place. Regularly audit permissions to ensure adherence to the principle of least privilege.

Conclusion

The TRUSTWORTHY setting in SQL Server enables elevated permissions but comes with significant security risks. Instead of enabling TRUSTWORTHY, 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 introduces.