The Trustworthy option should be disabled but what is an alternative?

Question

I run vulnerability assessment report in SQL Server 2016 and I received this issue

VA1102 The Trustworthy bit should be disabled on all databases except MSDB

The reason of setting Trustworthy to ON in my case is that DB Owner of the database has to have access to different database for data read (specifically the stored procedure is executed as owner and read the data from another database).

My question is how to achieve the same without setting up trustworthy to on which may expose server to vulnerabilities?

Answer 1

The Trustworthy bit should be disabled on all databases except MSDB

That is true.

The reason for setting Trustworthy to ON in my case is that DB Owner of the database has to have access to different database for data read

That is not true.

how to achieve the same without setting up trustworthy?

Fortunately, that is super-easy, barely an inconvenience 😉 thanks to Module Signing. All you need to do is:

1. Create a certificate in the DB containing the code that reads from another DB
2. Sign the code (proc, function, trigger) that reads from the other DB with the certificate
3. Copy the certificate (no need to copy private key) to the DB being read from
4. Create a user in the read-from DB from the certificate
5. Grant the new user in the read-from DB whatever permissions are necessary

That's it! Of course, this does assume that you are not using WITH EXECUTE AS ... in your proc / function / trigger definition as that does complicate things a little (and I will soon address that in your related question). But, that assumption should be fine since the reason stated for needing it (in order to have read access on the other DB) is a non-issue at this point since Module Signing takes care of that.

Please see the demo below, which borrows heavily from your repro code posted in your related question (for consistency)

Initial Setup

/* -- Clean Up
USE [master];
DROP DATABASE SourceDatabase;
DROP DATABASE TargetDatabase;

DROP LOGIN SourceDatabaseOwner;
DROP LOGIN TargetDatabaseOwner;
DROP LOGIN ServiceAccount;
*/


-- Create logins
CREATE LOGIN SourceDatabaseOwner WITH PASSWORD = 'Pa$$w0rd';
CREATE LOGIN TargetDatabaseOwner WITH PASSWORD = 'Pa$$w0rd';
CREATE LOGIN ServiceAccount WITH PASSWORD = 'Pa$$w0rd';

-- Create databases
CREATE DATABASE SourceDatabase;
CREATE DATABASE TargetDatabase;

-- Do NOT Set up trustworthy

-- Setup database owners
-- ( sp_changedbowner is deprecated )
ALTER AUTHORIZATION
    ON DATABASE::[SourceDatabase]
    TO [SourceDatabaseOwner];
ALTER AUTHORIZATION
    ON DATABASE::[TargetDatabase]
    TO [TargetDatabaseOwner];


-- Add ServiceAccount to source database
USE SourceDatabase
CREATE USER ServiceAccount FOR LOGIN ServiceAccount;  
GO


-- Create table with data
USE TargetDatabase;
GO
CREATE TABLE dbo.InterestingData (
    Id INT IDENTITY PRIMARY KEY,
    Content NVARCHAR(255)
    );
INSERT INTO dbo.InterestingData (Content) VALUES ('Foo'), ('Bar');
GO

-- Create stored procedure executing as CALLER and accessing target database
USE SourceDatabase;
GO
CREATE OR ALTER PROCEDURE dbo.GetData
--with execute as 'ServiceAccount' -- Do NOT use this option
AS
BEGIN
    SELECT SESSION_USER AS [CurrentUser];
    SELECT Id, Content
    FROM TargetDatabase.dbo.InterestingData;
END;
GO
GRANT EXECUTE ON dbo.GetData TO [ServiceAccount];
GO

Test

-- Execution does not work under service account
-- The server principal "ServiceAccount" is not able to access the database
--       "TargetDatabase" under the current security context.

USE [SourceDatabase];
EXECUTE AS LOGIN = N'ServiceAccount';

EXEC dbo.GetData;
GO
REVERT;
GO

Set up Module Signing

USE [SourceDatabase];

-- Create certificate
CREATE CERTIFICATE [SignProcedureCert]
    ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
    WITH SUBJECT = 'Certificate for signing stored procedures';
GO

-- Sign the procedure
ADD SIGNATURE TO dbo.GetData   
    BY CERTIFICATE SignProcedureCert  
    WITH PASSWORD = 'Pa$$w0rd';  
GO  

-----------------

-- Copy certificate to target DB
-- ( CERTENCODED in SQL Server 2012+ )
DECLARE @Cert NVARCHAR(4000);
SET @Cert = CONVERT(NVARCHAR(4000), CERTENCODED(CERT_ID(N'SignProcedureCert')), 1);

EXEC (N'USE [TargetDatabase];
CREATE CERTIFICATE [SignProcedureCert] FROM BINARY = ' + @Cert);

-- Create certificate-based User to allow access to DB
USE [TargetDatabase];
CREATE USER [SignProcedureCert] FROM CERTIFICATE [SignProcedureCert];

-- Allow access to target object in target DB
USE [TargetDatabase];
GRANT SELECT ON SCHEMA::dbo TO [SignProcedureCert];

Test Again

USE [SourceDatabase];
EXECUTE AS LOGIN = N'ServiceAccount';

EXEC dbo.GetData;
-- Success!!
GO
REVERT;
GO

---

For more info on Module Signing in general, please see my site:
https://ModuleSigning.Info/