Cross database access with trustworthy on does not work for service account neither with certificate

Question

I have this setup

• I created two databases (source and target).
• Source database has stored procedure that is accessing table in target database.
• Stored procedure is executed as service account
• Service account login has user account in both databases
• Service account has connect and authenticate permission to target database
• Service account has read permission on target database schema
• Both databases has trustworthy on

still I got an error

The server principal "ServiceAccount" is not able to access the database "TargetDatabase" under the current security context.

SQL script

-- 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

-- Setup trustworthy
ALTER DATABASE SourceDatabase SET TRUSTWORTHY ON;
ALTER DATABASE TargetDatabase SET TRUSTWORTHY ON;

-- Setup database owners
USE SourceDatabase
GO
EXEC dbo.sp_changedbowner @loginame = N'SourceDatabaseOwner'

USE TargetDatabase
GO
EXEC dbo.sp_changedbowner @loginame = N'TargetDatabaseOwner'

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

-- Add ServiceAccount to target database
USE TargetDatabase
GO
CREATE USER ServiceAccount FOR LOGIN ServiceAccount;  

-- Enable ServiceAccount to authenticate to target database
USE TargetDatabase
GO
GRANT AUTHENTICATE TO ServiceAccount;

-- Grant permissions
USE TargetDatabase
GO
GRANT SELECT ON SCHEMA::dbo TO ServiceAccount

-- 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')

-- Create stored procedure executing under service account and accessing target database
USE SourceDatabase
GO
create or alter procedure dbo.GetData
with execute as 'ServiceAccount' as
begin
    select id, content from TargetDatabase.dbo.InterestingData
end

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

So I continued and tried to sign the procedure with the certificate and setup permissions on the target table to the login of that certificate

-- create certificate in master
use master 
go

CREATE CERTIFICATE SignProcedureCert
ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
WITH SUBJECT = 'Certificate for signing stored procedures'
GO

-- backup certificate
BACKUP CERTIFICATE SignProcedureCert TO FILE ='C:\Certs\SignProcedureCert.cer'
WITH PRIVATE KEY
(       
    FILE = 'C:\Certs\SignProcedureCert.pvk',
    DECRYPTION BY PASSWORD = 'Pa$$w0rd',
    ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
)
GO

-- create login from the certificate
create login SignProcedureLogin from certificate SignProcedureCert

-- Import certificate to the source database
CREATE CERTIFICATE SignProcedureCert
FROM FILE = 'C:\Certs\SignProcedureCert.cer'
WITH PRIVATE KEY (FILE = 'C:\Certs\SignProcedureCert.pvk', 
ENCRYPTION BY PASSWORD = 'Pa$$w0rd', 
DECRYPTION BY PASSWORD = 'Pa$$w0rd')

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

-- Setup permission for SignProcedureLogin to target database
USE TargetDatabase
GO
CREATE USER SignProcedureLogin FOR LOGIN SignProcedureLogin;  
GRANT SELECT ON SCHEMA::dbo TO SignProcedureLogin

so now I should have this setup

but when I execute

USE SourceDatabase
GO
exec dbo.GetData

I got the same error

The server principal "ServiceAccount" is not able to access the database "TargetDatabase" under the current security context.

What am I missing?

Update

When I remove the with execute as clause it does work

-- Setup trustworthy
ALTER DATABASE SourceDatabase SET TRUSTWORTHY OFF;
ALTER DATABASE TargetDatabase SET TRUSTWORTHY OFF;

USE SourceDatabase
GO

create or alter procedure dbo.GetData
--with execute as 'ServiceAccount' 
as
begin
    select id, content from TargetDatabase.dbo.InterestingData
end

GRANT EXECUTE ON SCHEMA::dbo TO ServiceAccount

execute as login = 'ServiceAccount'
exec dbo.GetData
revert

but still it's possible to somehow execute the procedure with the with execute as clause?

Answer 1

You were actually very close with your initial setup. You were just missing 1 step (and have 2 extraneous steps). However, before I explain how to make the original scenario work, I need to mention / warn that the original scenario is non-ideal and should not be used, even if fixed, as there is a better and more secure method: Module Signing. I explained the preferred solution to the overall problem being addressed by the original setup in my answer to your related question: The Trustworthy option should be disabled but what is an alternative?.

As you found out (based on your Update in the question), the problem is due to using the WITH EXECUTE AS ... clause (i.e. impersonation) in the proc definition. This quarantines the process to the current database. Yes, enabling TRUSTWORTHY (as you initially tried) can help overcome this, but the rules are not always straight-forward. First, TRUSTWORTHY only affects where a process is being initiated, hence there is no reason to enable TRUSTWORTHY in the target database (that is one of the two extraneous steps). Second, when enabling TRUSTWORTHY, the focus of security becomes the owner of the current database (i.e. where the process is being initiated). Hence, the issue is whether or not SourceDatabaseOwner has permission to act as authenticator on [TargetDatabase], not ServiceAccount (meaning, the missing step is granting AUTHENTICATE to SourceDatabaseOwner — which will require adding SourceDatabaseOwner as a user in [TargetDatabase] — and the second extraneous step is granting AUTHENTICATE to ServiceAccount).

Here is a working demo, based on your example code from the question:

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;

-- Setup trustworthy
ALTER DATABASE SourceDatabase SET TRUSTWORTHY ON;
--------ALTER DATABASE TargetDatabase SET TRUSTWORTHY ON; -- 𝙐𝙉𝙉𝙀𝘾𝙀𝙎𝙎𝘼𝙍𝙔!!

-- 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

-- Add ServiceAccount to target database
USE TargetDatabase
CREATE USER ServiceAccount FOR LOGIN ServiceAccount;  
GO

---------- Enable ServiceAccount to authenticate to target database
--------USE TargetDatabase
--------GO
--------GRANT AUTHENTICATE TO ServiceAccount; -- 𝙐𝙉𝙉𝙀𝘾𝙀𝙎𝙎𝘼𝙍𝙔!!

-- Grant permissions
USE TargetDatabase
GRANT SELECT ON SCHEMA::dbo TO 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 under service account and
-- accessing target database
USE SourceDatabase;
GO
CREATE OR ALTER PROCEDURE dbo.GetData
WITH EXECUTE AS 'ServiceAccount'
AS
BEGIN
    SELECT SESSION_USER AS [CurrentUser];
    SELECT Id, Content FROM TargetDatabase.dbo.InterestingData;
END;
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];
EXEC dbo.GetData;
GO

FIX ...

• to allow access to individual database(s)

  USE [TargetDatabase];
  CREATE USER [SourceDatabaseOwner];
  GRANT AUTHENTICATE TO [SourceDatabaseOwner];
  

  — 𝓧𝓞𝓡 —

• to allow access to all databases

  USE [master];
  GRANT AUTHENTICATE SERVER TO [SourceDatabaseOwner];
  

Test again

USE [SourceDatabase];
EXEC dbo.GetData;
-- Success!!
GO

---

If, for some reason, you really really needed/wanted to use WITH EXECUTE AS (though there is typically no reason to do so since Module Signing allows the proc / function / trigger to work consistently regardless of who executes it), then you can still accomplish this without enabling TRUSTWORTHY via Module Signing. As with your original setup, you were again close with the additional Module Signing steps, but there was again a missing step and an extraneous step. Module Signing shifts the focus of security to the certificate (and hence the certificate-based Login and/or User). So, the missing step was not granting AUTHENTICATE to the certificate-based User in [TargetDatabase]. And, the extraneous step was creating a Login for the Certificate as there are no server-level permissions being granted in this scenario.

Here is a working demo, continuing the demo from above (i.e. not self-contained):

Undo extra step from demo above that made things work (to show that Module Signing also fixes this scenario)

USE [TargetDatabase];
DROP USER [SourceDatabaseOwner];

USE [master];
REVOKE AUTHENTICATE SERVER TO [SourceDatabaseOwner];

Test again (verify that proc no longer works)

USE [SourceDatabase];
EXEC dbo.GetData;
-- error
GO

Set Up Module Signing

USE [SourceDatabase];

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

-- 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);
SELECT CERT_ID(N'SignProcedureCert'), @Cert;

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];
GO

Test again (verify that proc still does not work)

Module Signing setup that worked in related question (linked towards the top) does not work here.

USE [SourceDatabase];
EXEC dbo.GetData;
-- error
GO

The problem is the WITH EXECUTE AS clause in the proc definition.

FIX

USE [TargetDatabase];
GRANT AUTHENTICATE TO [SignProcedureCert];

Test again

USE [SourceDatabase];
SELECT SESSION_USER AS [CurrentUser];
EXEC dbo.GetData;
-- Success!!
GO

---

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