Stored procedure security with execute as, cross database queries, and module signing

Question

I have a situation that, while I was able to work around it (as the repro will show), I don't understand. Here are the high points

Two databases, ChainingSource and ChainDestination, both of which have cross database chaining set to true
A stored procedure in ChainingSource accesses, through an EXEC(@sql), accesses a table in ChainingDestination
The stored procedure is defined with an execute as clause
If I try to execute the procedure as is, it says the server principal of the execution context is unable to access ChainingDestination
So I add a certificate and code signing into the mix. That is, I add a certificate mapped login to the server, mapped user to each of the databases, and grant permissions to the certificate mapped user accordingly
If I leave the execute as clause in place, I get the same error.
If I remove the execute as clause, everything is fine.

It's the second-to-last point that I'm confused about. Or, specifically, why that one doesn't work and the last one does.

/******************************

            Setup

******************************/
USE [master];
go
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = 'ChainingSource')
BEGIN
    ALTER DATABASE [ChainingSource] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [ChainingSource] SET ONLINE;
    DROP DATABASE [ChainingSource];
END
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = 'ChainingDestination')
BEGIN
    ALTER DATABASE [ChainingDestination] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [ChainingDestination] SET ONLINE;
    DROP DATABASE [ChainingDestination];
END
GO

EXECUTE AS LOGIN = 'sa';
CREATE DATABASE [ChainingSource];
CREATE DATABASE [ChainingDestination];
GO
REVERT;
GO

ALTER DATABASE [ChainingSource] SET DB_CHAINING ON;
ALTER DATABASE [ChainingDestination] SET DB_CHAINING ON;

IF SUSER_ID('myAppUser') IS null
    CREATE LOGIN [myAppUser] WITH password = 'p@ssw0rd!23';

IF SUSER_ID('myAppUserEscalated') IS null
    CREATE LOGIN [myAppUserEscalated] WITH password = 'p@ssw0rd!23';

IF NOT EXISTS (
    SELECT * FROM sys.[symmetric_keys] AS [sk]
    WHERE name = '##MS_DatabaseMasterKey##'
)
BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'f00bar!23';
    PRINT 'Created master key in databse [master]';
END

IF CERT_ID('myAppCert') IS NULL
    CREATE CERTIFICATE [myAppCert] AUTHORIZATION dbo FROM BINARY = 0x308201BD30820126A003020102021061AF3EB269776BB74629F44629EF9216300D06092A864886F70D0101050500301C311A301806035504031311436F6465205369676E696E6720436572743020170D3136313032303232303932335A180F32303939303130313030303030305A301C311A301806035504031311436F6465205369676E696E67204365727430819F300D06092A864886F70D010101050003818D0030818902818100BCCA7DC1CF2F4874F341AF3C586F0B023CAAD16986ADDAC2F7BFB3BCE590F2A952218F51298067CE3BE9ED695A229DADD029510F0927F30484A587024E0F58EC83924BE49D227D2FE1FCCA0C682528D6A0658AAA6CA5D9F2405AB6950B7D5BA672BB971910D71CEE3B77FF0A4EF59F010AAB445FD127944966C141F7CFC3D5790203010001300D06092A864886F70D010105050003818100011525EDD191767041659701F13F4370F803E6C981A6E33D68863FDACADE709926AB7E3BC8D618EDD07FC52058EFE42D96CA49961CF2936F446EDC4B7D55725FF2F1B37B326D564941CF6A7424551828FE198335AEFA0C892B375D3B676F35B708A48C67F80714643A34050CF9C557FBDD01274BC1ACCCA9A7AD3EC37DD2DA31 WITH PRIVATE KEY (BINARY = 0x1EF1B5B00000000001000000010000001000000054020000CF21B85A9464B60CDAB9F2E419B341490702000000A4000002E67BDF3CE02406E4D69A760D519B3BB6DA77FAFA7D710936EAE5267F072F98A1F7521110EB03955427B79FA386F7D70EDF6E977E92E59761DE0EB186F895AD975CE63C4D8A8B67BA487B9807EDB8B33C7C08EABCCD716E9505170A9729B6E165CE0ED0CA35B5C62D548367FEFC2C694060184D9185331466A0C64A9CB7BF8CA7AC0946A54091A4626978320C7290A784C6147BAF23FD866D9D1D4D1D79DA1B4D2DE213D11299F1417D8C421CC25A2E851FF9CEFA0ECA2006186C787692FDC28F2E702FCC7E76AFBEB95B954B50AA3697E60FC6928392664CE0EDB794AA392C1CC6326102B7CE8A02B367D2F416269DBBF4C16F096780517D32B4653E94DE5C24CE9D39EBC8E6A4EF1B9217F1B4F098F4F77F88CC11C40DDB312BE87CC1430C16AED8773E7691ADE8472BFC02B458B09B40404F61D2E02746AE576582DEE3EC5C09077E127BB4E996A9C4A840E6E0F59D85A3FC4E2844679927DBA6A571927A1F1C938716B8FC922B1D77FAA90BDBA49D1084081E4198A50506C5F6FE87F81B759EE0688428ABA7B2E8CC7D96AC6409DAE41937DB9C1E1CACCD7AE86A8F161316A07B05D523A116AB87022978312EE9853AE9FFA44FFF52114D084934D86D0FFD2D47B974769812BF0F4FE8276FD0DCE4069F11EC3915A68F4454166E3ABAAB9539530117597EE52213FEC7C87254634F10062C5C1D97CE5FEABB13252B22E210F56DB281FC1CE5432A7144FB4B89D00B4F8BC876C8C0F397DB9D22E15E2B07FBB44ADDDFBB6A75728917AC330E3A9F978847AC2D27913B3B6CBF54F1BAEF06072D15050ED1CA7BF9C5763A, DECRYPTION BY PASSWORD = 'f00bar!23')
IF SUSER_ID('myAppCert') IS NULL
    CREATE LOGIN [myAppCert] FROM CERTIFICATE [myAppCert];


USE [ChainingDestination];
CREATE USER [myAppUser];
CREATE USER [myAppUserEscalated];

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'f00bar!23';
CREATE CERTIFICATE [myAppCert] AUTHORIZATION dbo FROM BINARY = 0x308201BD30820126A003020102021061AF3EB269776BB74629F44629EF9216300D06092A864886F70D0101050500301C311A301806035504031311436F6465205369676E696E6720436572743020170D3136313032303232303932335A180F32303939303130313030303030305A301C311A301806035504031311436F6465205369676E696E67204365727430819F300D06092A864886F70D010101050003818D0030818902818100BCCA7DC1CF2F4874F341AF3C586F0B023CAAD16986ADDAC2F7BFB3BCE590F2A952218F51298067CE3BE9ED695A229DADD029510F0927F30484A587024E0F58EC83924BE49D227D2FE1FCCA0C682528D6A0658AAA6CA5D9F2405AB6950B7D5BA672BB971910D71CEE3B77FF0A4EF59F010AAB445FD127944966C141F7CFC3D5790203010001300D06092A864886F70D010105050003818100011525EDD191767041659701F13F4370F803E6C981A6E33D68863FDACADE709926AB7E3BC8D618EDD07FC52058EFE42D96CA49961CF2936F446EDC4B7D55725FF2F1B37B326D564941CF6A7424551828FE198335AEFA0C892B375D3B676F35B708A48C67F80714643A34050CF9C557FBDD01274BC1ACCCA9A7AD3EC37DD2DA31 WITH PRIVATE KEY (BINARY = 0x1EF1B5B00000000001000000010000001000000054020000CF21B85A9464B60CDAB9F2E419B341490702000000A4000002E67BDF3CE02406E4D69A760D519B3BB6DA77FAFA7D710936EAE5267F072F98A1F7521110EB03955427B79FA386F7D70EDF6E977E92E59761DE0EB186F895AD975CE63C4D8A8B67BA487B9807EDB8B33C7C08EABCCD716E9505170A9729B6E165CE0ED0CA35B5C62D548367FEFC2C694060184D9185331466A0C64A9CB7BF8CA7AC0946A54091A4626978320C7290A784C6147BAF23FD866D9D1D4D1D79DA1B4D2DE213D11299F1417D8C421CC25A2E851FF9CEFA0ECA2006186C787692FDC28F2E702FCC7E76AFBEB95B954B50AA3697E60FC6928392664CE0EDB794AA392C1CC6326102B7CE8A02B367D2F416269DBBF4C16F096780517D32B4653E94DE5C24CE9D39EBC8E6A4EF1B9217F1B4F098F4F77F88CC11C40DDB312BE87CC1430C16AED8773E7691ADE8472BFC02B458B09B40404F61D2E02746AE576582DEE3EC5C09077E127BB4E996A9C4A840E6E0F59D85A3FC4E2844679927DBA6A571927A1F1C938716B8FC922B1D77FAA90BDBA49D1084081E4198A50506C5F6FE87F81B759EE0688428ABA7B2E8CC7D96AC6409DAE41937DB9C1E1CACCD7AE86A8F161316A07B05D523A116AB87022978312EE9853AE9FFA44FFF52114D084934D86D0FFD2D47B974769812BF0F4FE8276FD0DCE4069F11EC3915A68F4454166E3ABAAB9539530117597EE52213FEC7C87254634F10062C5C1D97CE5FEABB13252B22E210F56DB281FC1CE5432A7144FB4B89D00B4F8BC876C8C0F397DB9D22E15E2B07FBB44ADDDFBB6A75728917AC330E3A9F978847AC2D27913B3B6CBF54F1BAEF06072D15050ED1CA7BF9C5763A, DECRYPTION BY PASSWORD = 'f00bar!23')
CREATE USER [myAppCert];
GO


CREATE TABLE [dbo].[topSecret] ([ID] INT IDENTITY, [Secrets] NVARCHAR(100));
INSERT INTO [dbo].[topSecret] ([Secrets]) VALUES ('Nuke Codes!');

GRANT SELECT ON [dbo].[topSecret] TO [myAppUserEscalated];
GRANT SELECT ON [dbo].[topSecret] TO [myAppCert];

GO

USE [ChainingSource];
GO
CREATE USER [myAppUser]
CREATE USER [myAppUserEscalated];

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'f00bar!23';
CREATE CERTIFICATE [myAppCert] AUTHORIZATION dbo FROM BINARY = 0x308201BD30820126A003020102021061AF3EB269776BB74629F44629EF9216300D06092A864886F70D0101050500301C311A301806035504031311436F6465205369676E696E6720436572743020170D3136313032303232303932335A180F32303939303130313030303030305A301C311A301806035504031311436F6465205369676E696E67204365727430819F300D06092A864886F70D010101050003818D0030818902818100BCCA7DC1CF2F4874F341AF3C586F0B023CAAD16986ADDAC2F7BFB3BCE590F2A952218F51298067CE3BE9ED695A229DADD029510F0927F30484A587024E0F58EC83924BE49D227D2FE1FCCA0C682528D6A0658AAA6CA5D9F2405AB6950B7D5BA672BB971910D71CEE3B77FF0A4EF59F010AAB445FD127944966C141F7CFC3D5790203010001300D06092A864886F70D010105050003818100011525EDD191767041659701F13F4370F803E6C981A6E33D68863FDACADE709926AB7E3BC8D618EDD07FC52058EFE42D96CA49961CF2936F446EDC4B7D55725FF2F1B37B326D564941CF6A7424551828FE198335AEFA0C892B375D3B676F35B708A48C67F80714643A34050CF9C557FBDD01274BC1ACCCA9A7AD3EC37DD2DA31 WITH PRIVATE KEY (BINARY = 0x1EF1B5B00000000001000000010000001000000054020000CF21B85A9464B60CDAB9F2E419B341490702000000A4000002E67BDF3CE02406E4D69A760D519B3BB6DA77FAFA7D710936EAE5267F072F98A1F7521110EB03955427B79FA386F7D70EDF6E977E92E59761DE0EB186F895AD975CE63C4D8A8B67BA487B9807EDB8B33C7C08EABCCD716E9505170A9729B6E165CE0ED0CA35B5C62D548367FEFC2C694060184D9185331466A0C64A9CB7BF8CA7AC0946A54091A4626978320C7290A784C6147BAF23FD866D9D1D4D1D79DA1B4D2DE213D11299F1417D8C421CC25A2E851FF9CEFA0ECA2006186C787692FDC28F2E702FCC7E76AFBEB95B954B50AA3697E60FC6928392664CE0EDB794AA392C1CC6326102B7CE8A02B367D2F416269DBBF4C16F096780517D32B4653E94DE5C24CE9D39EBC8E6A4EF1B9217F1B4F098F4F77F88CC11C40DDB312BE87CC1430C16AED8773E7691ADE8472BFC02B458B09B40404F61D2E02746AE576582DEE3EC5C09077E127BB4E996A9C4A840E6E0F59D85A3FC4E2844679927DBA6A571927A1F1C938716B8FC922B1D77FAA90BDBA49D1084081E4198A50506C5F6FE87F81B759EE0688428ABA7B2E8CC7D96AC6409DAE41937DB9C1E1CACCD7AE86A8F161316A07B05D523A116AB87022978312EE9853AE9FFA44FFF52114D084934D86D0FFD2D47B974769812BF0F4FE8276FD0DCE4069F11EC3915A68F4454166E3ABAAB9539530117597EE52213FEC7C87254634F10062C5C1D97CE5FEABB13252B22E210F56DB281FC1CE5432A7144FB4B89D00B4F8BC876C8C0F397DB9D22E15E2B07FBB44ADDDFBB6A75728917AC330E3A9F978847AC2D27913B3B6CBF54F1BAEF06072D15050ED1CA7BF9C5763A, DECRYPTION BY PASSWORD = 'f00bar!23')
CREATE USER [myAppCert];
GO

CREATE SYNONYM [dbo].[topSecret] FOR [ChainingDestination].[dbo].[topSecret];
GRANT SELECT ON [dbo].[topSecret] TO [myAppUserEscalated];
GRANT SELECT ON [dbo].[topSecret] TO [myAppCert];

GO

IF OBJECT_ID('[dbo].[getSecrets]') IS NOT null
    DROP PROCEDURE [dbo].[getSecrets]
GO

CREATE PROCEDURE [dbo].[getSecrets]
WITH EXECUTE AS 'myAppUserEscalated'
AS
BEGIN

    SELECT * FROM sys.login_token;
    SELECT * FROM sys.user_token;
    EXEC('SELECT * FROM [dbo].[topSecret] AS [ts];');
END
GO
GRANT EXECUTE ON [dbo].[getSecrets] TO [myAppUser];
GO

/******************************

            DEMO

******************************/

-- EXECUTE AS clause only
EXECUTE AS LOGIN = 'myAppUser';
GO
EXEC dbo.[getSecrets]
GO
REVERT;
GO

-- no bueno. let's try to add a signature!

ADD SIGNATURE TO [dbo].[getSecrets]
    BY CERTIFICATE [myAppCert];

EXECUTE AS LOGIN = 'myAppUser';
GO
EXEC dbo.[getSecrets]
GO
REVERT;
GO

-- still no bueno. 
-- let's take off the EXECUTE AS clause and sign

ALTER PROCEDURE [dbo].[getSecrets]
AS
BEGIN

    SELECT * FROM sys.login_token;
    SELECT * FROM sys.user_token;
    EXEC('SELECT * FROM [dbo].[topSecret] AS [ts];');
END
GO

ADD SIGNATURE TO [dbo].[getSecrets]
    BY CERTIFICATE [myAppCert];

EXECUTE AS LOGIN = 'myAppUser';
GO
EXEC dbo.[getSecrets]
GO
REVERT;
GO
 -- bueno

The primary issue should be that the Dynamic SQL (i.e. the EXEC() ) breaks ownership-chaining. I say "should be" as I am not able to test it right now, but I would be surprised if that wasn't the main problem. Another potentially complicating factor is the EXECUTE AS clause of the CREATE PROCEDURE, which is Impersonation, which also might not work well via Dynamic SQL. Regardless, you no longer need DB_CHAINING ON in either DB. Module Signing replaces the need for it and EXECUTE AS entirely. I will post an answer with details tomorrow, but it's very late and I'm falling asleep :-(. — Solomon Rutzky, Oct 21 '16 at 05:28
But the part I'm confused by is if I look at sys.login_token while the proc is signed, it shows the certificate based user in both cases, but has "deny only" when the proc also has an execute as clause and "grant or deny"without it. Why doesn't it trust the certificate user under impersonation? Also, chaining is on for other reasons (we're doing dynamic SQL in only a handful of procs; the rest will get their perms through chaining). I just didn't know if it was relevant to the scenario. — Ben Thul, Oct 21 '16 at 06:01

score 8 · Accepted Answer · edited Jun 15 '20 at 09:05

You were headed in the right direction and were very close. Now you just need to view the module signing as a replacement of EXECUTE AS instead of as something that is added to it. Removing EXECUTE AS and myAppUserEscalated entirely, and relying solely upon module signing (and the certificate-based Login and related Users) allows cross-DB permissions and maintaining permissions when ownership-chaining doesn't work (i.e. Dynamic SQL), all while keeping TRUSTWORTHY set to OFF (and even keeping DB_CHAINING set to OFF).

Below is a test script that is based on the script in the question, but modified to start with the minimum options (i.e. DB_CHAINING not turned on, and the Certificate and Certificate-based Login / Users not created). It also has 4 stored procedures to easily test the various combinations of:

Default (No Impersonation and No Dynamic SQL)
Impersonation (but no Dynamic SQL)
Dynamic SQL (but no Impersonation)
Impersonation and Dynamic SQL

There are six tests in the script:

Test 1 shows that by default, none of the combinations works. The stored procedure, getSecrets (no Impersonation or Dynamic SQL), gets farther than the straight SQL due to ownership chaining, but cannot access the other DB due to no Cross-DB Ownership Chaining. The two stored procedures with Dynamic SQL get the same error as the straight SQL due to the Dynamic SQL breaking the ownership-chain.
Test 2 shows that when only DB_CHAINING is set to ON, then the stored procedure, getSecrets (no Impersonation or Dynamic SQL), works as desired between Databases. But the getSecretsWithDynamicSql stored procedure fails due to the Dynamic SQL breaking the ownership-chain, hence it cannot benefit from the Cross-DB Ownership Chaining.
Test 3 shows that when only TRUSTWORTHY is set to ON (just for the "Source" DB), then code using Impersonation (i.e. EXECUTE AS), with or without Dynamic SQL, works as desired between Databases. But code not using Impersonation doesn't work, same as in Test 1. Of course, we don't want TRUSTWORTHY to be ON as it is a security risk. This test is just to show how things used to be, prior to module signing (i.e. that TRUSTWORTHY is required when using Impersonation, which in turn is required when using Dynamic SQL).
Test 4 shows that when both DB_CHAINING and TRUSTWORTHY are set to ON, then code not using Dynamic SQL works without needing Impersonation, and any code using Impersonation, whether or not there is Dynamic SQL, works as desired between Databases. But again, we don't want TRUSTWORTHY to be ON as it is a security risk. This test is just to show how things used to be, prior to module signing.
Test 5 turns DB_CHAINING and TRUSTWORTHY back to OFF, creates the Certificate and the associated Login and Users, and signs the two stored procedures that are not using Impersonation (because there is no longer a need to use Impersonation). Both of the signed stored procedures work as intended :-).
Test 6 removes the two stored procedures that used Impersonation, and even removes the "Escalated" Login and associated Users that were being impersonated. Running Test 5 again proves that all that is needed is the module signing (which is why it is the super-cool way of controlling permissions :-).

The test script:

/******************************
        Setup


******************************/
/*************************  CLEANUP *************************************/
USE [master];
GO
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'ChainingSource')
BEGIN
    PRINT 'Dropping [ChainingSource] DB...';
    ALTER DATABASE [ChainingSource] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [ChainingSource] SET ONLINE;
    DROP DATABASE [ChainingSource];
END;
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'ChainingDestination')
BEGIN
    PRINT 'Dropping [ChainingDestination] DB...';
    ALTER DATABASE [ChainingDestination] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [ChainingDestination] SET ONLINE;
    DROP DATABASE [ChainingDestination];
END;
IF (SUSER_ID(N'myAppUser') IS NOT NULL)
BEGIN
  PRINT 'Dropping [myAppUser] Login...';
  DROP LOGIN [myAppUser];
END;
IF (SUSER_ID(N'myAppUserEscalated') IS NOT NULL)
BEGIN
  PRINT 'Dropping [myAppUserEscalated] Login...';
  DROP LOGIN [myAppUserEscalated];
END;
GO
/*************************  CREATE *************************************/
EXECUTE AS LOGIN = N'sa';
PRINT 'Creating databases...';
CREATE DATABASE [ChainingSource] COLLATE Latin1_General_100_CI_AS_SC;
CREATE DATABASE [ChainingDestination] COLLATE Latin1_General_100_CI_AS_SC;
REVERT;
GO
-- Set up Login/User: [myAppUser]
IF (SUSER_ID(N'myAppUser') IS NULL)
BEGIN
    EXEC(N'
      PRINT ''Creating [myAppUser]...'';
      USE [master];
      CREATE LOGIN [myAppUser] WITH PASSWORD = N''p@ssw0rd!23'';
  USE [ChainingDestination];
  CREATE USER [myAppUser];

  USE [ChainingSource];
  CREATE USER [myAppUser];
 ');

END;
-- Set up Login/User: [myAppUserEscalated]
IF (SUSER_ID(N'myAppUserEscalated') IS NULL)
BEGIN
    EXEC(N'
      PRINT ''Creating [myAppUserEscalated]...'';
      USE [master];
      CREATE LOGIN [myAppUserEscalated] WITH PASSWORD = N''p@ssw0rd!23'';
  USE [ChainingDestination];
  CREATE USER [myAppUserEscalated];

  USE [ChainingSource];
  CREATE USER [myAppUserEscalated];
 ');

END;
GO
USE [ChainingDestination];
CREATE TABLE [dbo].[topSecret] ([ID] INT IDENTITY, [Secrets] NVARCHAR(100));
INSERT INTO [dbo].[topSecret] ([Secrets]) VALUES (N'Nuke Codes!');
GRANT SELECT ON [dbo].[topSecret] TO [myAppUserEscalated];
GO
USE [ChainingSource];
CREATE SYNONYM [dbo].[topSecret] FOR [ChainingDestination].[dbo].[topSecret];
GRANT SELECT ON [dbo].[topSecret] TO [myAppUserEscalated];
GO

IF OBJECT_ID(N'[dbo].[getSecrets]') IS NOT NULL
    DROP PROCEDURE [dbo].[getSecrets]
GO
CREATE PROCEDURE [dbo].[getSecrets]
AS
BEGIN
    SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
    SELECT * FROM sys.login_token;
    SELECT * FROM sys.user_token;
    SELECT * FROM [dbo].[topSecret] AS [ts];
END
GO
GRANT EXECUTE ON [dbo].[getSecrets] TO [myAppUser];
GO

IF OBJECT_ID(N'[dbo].[getSecretsWithDynamicSql]') IS NOT NULL
    DROP PROCEDURE [dbo].[getSecretsWithDynamicSql]
GO
CREATE PROCEDURE [dbo].[getSecretsWithDynamicSql]
AS
BEGIN
    SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
    SELECT * FROM sys.login_token;
    SELECT * FROM sys.user_token;
    EXEC(N'SELECT * FROM [dbo].[topSecret] AS [ts];');
END
GO
GRANT EXECUTE ON [dbo].[getSecretsWithDynamicSql] TO [myAppUser];
GO

IF OBJECT_ID(N'[dbo].[getSecretsWithDynamicSqlAndImpersonation]') IS NOT NULL
    DROP PROCEDURE [dbo].[getSecretsWithDynamicSqlAndImpersonation]
GO
CREATE PROCEDURE [dbo].[getSecretsWithDynamicSqlAndImpersonation]
WITH EXECUTE AS N'myAppUserEscalated'
AS
BEGIN
    SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
    SELECT * FROM sys.login_token;
    SELECT * FROM sys.user_token;
    EXEC(N'SELECT * FROM [dbo].[topSecret] AS [ts];');
END
GO
GRANT EXECUTE ON [dbo].[getSecretsWithDynamicSqlAndImpersonation] TO [myAppUser];
GO

IF OBJECT_ID(N'[dbo].[getSecretsWithImpersonation]') IS NOT NULL
    DROP PROCEDURE [dbo].[getSecretsWithImpersonation]
GO
CREATE PROCEDURE [dbo].[getSecretsWithImpersonation]
WITH EXECUTE AS N'myAppUserEscalated'
AS
BEGIN
    SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
    SELECT * FROM sys.login_token;
    SELECT * FROM sys.user_token;
    SELECT * FROM [dbo].[topSecret] AS [ts];
END
GO
GRANT EXECUTE ON [dbo].[getSecretsWithImpersonation] TO [myAppUser];
GO
/******************************
        DEMO


******************************/
/******************  TEST 1 (both DB_CHAINING and TRUSTWORTHY OFF) ********************/
-- Default is OFF, but make resetting after running Tests 2 and 3 easier
ALTER DATABASE [ChainingSource] SET DB_CHAINING OFF;
ALTER DATABASE [ChainingDestination] SET DB_CHAINING OFF;
ALTER DATABASE [ChainingSource] SET TRUSTWORTHY OFF;
USE [ChainingSource];
EXECUTE AS LOGIN = 'myAppUser';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO
SELECT * FROM [dbo].[topSecret]; -- error:
-- Msg 229, Level 14, State 5, Line XXXXX
-- The SELECT permission was denied on the object 'topSecret',
--    database 'ChainingSource', schema 'dbo'.
EXEC [dbo].[getSecrets]; -- error:
-- Msg 229, Level 14, State 5, Procedure getSecrets, Line XXXXX
-- The SELECT permission was denied on the object 'topSecret',
--    database 'ChainingDestination', schema 'dbo'.
EXEC [dbo].[getSecretsWithImpersonation]; -- error:
-- Msg 916, Level 14, State 1, Procedure getSecretsWithImpersonation, Line XXXXX
-- The server principal "myAppUserEscalated" is not able to access the database
--    "ChainingDestination" under the current security context.
EXEC [dbo].[getSecretsWithDynamicSqlAndImpersonation]; -- error:
-- Msg 229, Level 14, State 5, Line XXXXX
-- The SELECT permission was denied on the object 'topSecret',
--    database 'ChainingSource', schema 'dbo'.
EXEC [dbo].[getSecretsWithDynamicSql]; -- error:
-- Msg 229, Level 14, State 5, Line XXXXX
-- The SELECT permission was denied on the object 'topSecret',
--    database 'ChainingSource', schema 'dbo'.
REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO
/******************  TEST 2 (DB_CHAINING ON ; TRUSTWORTHY OFF) ************************/
ALTER DATABASE [ChainingSource] SET DB_CHAINING ON;
ALTER DATABASE [ChainingDestination] SET DB_CHAINING ON;
ALTER DATABASE [ChainingSource] SET TRUSTWORTHY OFF;
GO
EXECUTE AS LOGIN = 'myAppUser';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO
SELECT * FROM [dbo].[topSecret]; -- error: same as in Test 1
EXEC [dbo].[getSecretsWithImpersonation]; -- error: same as in Test 1
EXEC [dbo].[getSecretsWithDynamicSql]; -- error: same as in Test 1
EXEC [dbo].[getSecrets]; -- (different) success!
EXEC [dbo].[getSecretsWithDynamicSqlAndImpersonation]; -- (different) error:
-- Msg 916, Level 14, State 1, Line XXXXX
-- The server principal "myAppUserEscalated" is not able to access the database
--    "ChainingDestination" under the current security context.
REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO
/******************  TEST 3 (DB_CHAINING OFF ; TRUSTWORTHY ON) **********************/
ALTER DATABASE [ChainingSource] SET DB_CHAINING OFF;
ALTER DATABASE [ChainingDestination] SET DB_CHAINING OFF;
ALTER DATABASE [ChainingSource] SET TRUSTWORTHY ON;
GO
EXECUTE AS LOGIN = 'myAppUser';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO
SELECT * FROM [dbo].[topSecret]; -- error: same as in Tests 1 and 2
EXEC [dbo].[getSecrets]; -- error: same as in Test 1
EXEC [dbo].[getSecretsWithDynamicSql]; -- error: same as in Tests 1 and 2
EXEC [dbo].[getSecretsWithImpersonation]; -- (different) success!
EXEC [dbo].[getSecretsWithDynamicSqlAndImpersonation]; -- (different) success:
REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO
/******************  TEST 4 (both DB_CHAINING and TRUSTWORTHY ON) *********************/
ALTER DATABASE [ChainingSource] SET DB_CHAINING ON;
ALTER DATABASE [ChainingDestination] SET DB_CHAINING ON;
ALTER DATABASE [ChainingSource] SET TRUSTWORTHY ON;
GO
EXECUTE AS LOGIN = 'myAppUser';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO
SELECT * FROM [dbo].[topSecret]; -- error: same as in Tests 1, 2, and 3
EXEC [dbo].[getSecretsWithDynamicSql]; -- error: same as in Tests 1, 2, and 3
EXEC [dbo].[getSecrets]; -- success: same as in Test 2
EXEC [dbo].[getSecretsWithImpersonation]; -- success: same as in Test 3
EXEC [dbo].[getSecretsWithDynamicSqlAndImpersonation]; -- success: same as in Test 3
REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO
/*******************************************************************/
/* BEGIN: set up Certificate and cert-based Users for module signing */
/*******************************************************************/
USE [ChainingDestination];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'f00bar!23';
CREATE CERTIFICATE [myAppCert]
  AUTHORIZATION [dbo]
  FROM BINARY = 0x

308201BD30820126A003020102021061AF3EB269776BB74629F44629EF9216300D06092A864886F70D01

01050500301C311A301806035504031311436F6465205369676E696E6720436572743020170D31363130

32303232303932335A180F32303939303130313030303030305A301C311A301806035504031311436F64

65205369676E696E67204365727430819F300D06092A864886F70D010101050003818D00308189028181

00BCCA7DC1CF2F4874F341AF3C586F0B023CAAD16986ADDAC2F7BFB3BCE590F2A952218F51298067CE3B

E9ED695A229DADD029510F0927F30484A587024E0F58EC83924BE49D227D2FE1FCCA0C682528D6A0658A

AA6CA5D9F2405AB6950B7D5BA672BB971910D71CEE3B77FF0A4EF59F010AAB445FD127944966C141F7CF

C3D5790203010001300D06092A864886F70D010105050003818100011525EDD191767041659701F13F43

70F803E6C981A6E33D68863FDACADE709926AB7E3BC8D618EDD07FC52058EFE42D96CA49961CF2936F44

6EDC4B7D55725FF2F1B37B326D564941CF6A7424551828FE198335AEFA0C892B375D3B676F35B708A48C

67F80714643A34050CF9C557FBDD01274BC1ACCCA9A7AD3EC37DD2DA31;
-- no need for private key: nothing being signed in Destination
CREATE USER [myAppCert] FROM CERTIFICATE [myAppCert];
GRANT SELECT ON [dbo].[topSecret] TO [myAppCert];
GO
USE [ChainingSource];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'f00bar!23';
CREATE CERTIFICATE [myAppCert]
  AUTHORIZATION [dbo]
  FROM BINARY = 0x

308201BD30820126A003020102021061AF3EB269776BB74629F44629EF9216300D06092A864886F70D01

01050500301C311A301806035504031311436F6465205369676E696E6720436572743020170D31363130

32303232303932335A180F32303939303130313030303030305A301C311A301806035504031311436F64

65205369676E696E67204365727430819F300D06092A864886F70D010101050003818D00308189028181

00BCCA7DC1CF2F4874F341AF3C586F0B023CAAD16986ADDAC2F7BFB3BCE590F2A952218F51298067CE3B

E9ED695A229DADD029510F0927F30484A587024E0F58EC83924BE49D227D2FE1FCCA0C682528D6A0658A

AA6CA5D9F2405AB6950B7D5BA672BB971910D71CEE3B77FF0A4EF59F010AAB445FD127944966C141F7CF

C3D5790203010001300D06092A864886F70D010105050003818100011525EDD191767041659701F13F43

70F803E6C981A6E33D68863FDACADE709926AB7E3BC8D618EDD07FC52058EFE42D96CA49961CF2936F44

6EDC4B7D55725FF2F1B37B326D564941CF6A7424551828FE198335AEFA0C892B375D3B676F35B708A48C

67F80714643A34050CF9C557FBDD01274BC1ACCCA9A7AD3EC37DD2DA31
  WITH PRIVATE KEY (
     BINARY = 0x

1EF1B5B00000000001000000010000001000000054020000CF21B85A9464B60CDAB9F2E419B341490702

000000A4000002E67BDF3CE02406E4D69A760D519B3BB6DA77FAFA7D710936EAE5267F072F98A1F75211

10EB03955427B79FA386F7D70EDF6E977E92E59761DE0EB186F895AD975CE63C4D8A8B67BA487B9807ED

B8B33C7C08EABCCD716E9505170A9729B6E165CE0ED0CA35B5C62D548367FEFC2C694060184D91853314

66A0C64A9CB7BF8CA7AC0946A54091A4626978320C7290A784C6147BAF23FD866D9D1D4D1D79DA1B4D2D

E213D11299F1417D8C421CC25A2E851FF9CEFA0ECA2006186C787692FDC28F2E702FCC7E76AFBEB95B95

4B50AA3697E60FC6928392664CE0EDB794AA392C1CC6326102B7CE8A02B367D2F416269DBBF4C16F0967

80517D32B4653E94DE5C24CE9D39EBC8E6A4EF1B9217F1B4F098F4F77F88CC11C40DDB312BE87CC1430C

16AED8773E7691ADE8472BFC02B458B09B40404F61D2E02746AE576582DEE3EC5C09077E127BB4E996A9

C4A840E6E0F59D85A3FC4E2844679927DBA6A571927A1F1C938716B8FC922B1D77FAA90BDBA49D108408

1E4198A50506C5F6FE87F81B759EE0688428ABA7B2E8CC7D96AC6409DAE41937DB9C1E1CACCD7AE86A8F

161316A07B05D523A116AB87022978312EE9853AE9FFA44FFF52114D084934D86D0FFD2D47B974769812

BF0F4FE8276FD0DCE4069F11EC3915A68F4454166E3ABAAB9539530117597EE52213FEC7C87254634F10

062C5C1D97CE5FEABB13252B22E210F56DB281FC1CE5432A7144FB4B89D00B4F8BC876C8C0F397DB9D22

E15E2B07FBB44ADDDFBB6A75728917AC330E3A9F978847AC2D27913B3B6CBF54F1BAEF06072D15050ED1

CA7BF9C5763A,
  DECRYPTION BY PASSWORD = N'f00bar!23');
CREATE USER [myAppCert] FROM CERTIFICATE [myAppCert];
GRANT SELECT ON [dbo].[topSecret] TO [myAppCert];
GO
/*******************************************************************/
/* END: set up Certificate and cert-based Users for module signing */
/*******************************************************************/
-- Sign the two stored procedures that are NOT using Impersonation.
-- Ignore the two stored procedures that ARE using Impersonation.
ADD SIGNATURE TO [dbo].[getSecrets]
    BY CERTIFICATE [myAppCert];
ADD SIGNATURE TO [dbo].[getSecretsWithDynamicSql]
    BY CERTIFICATE [myAppCert];
GO
/******************  TEST 5 (both DB_CHAINING and TRUSTWORTHY OFF) ********************/
ALTER DATABASE [ChainingSource] SET DB_CHAINING OFF;
ALTER DATABASE [ChainingDestination] SET DB_CHAINING OFF;
-- Trustworthy? We don't need no stinkin' trustworthy ;-)
ALTER DATABASE [ChainingSource] SET TRUSTWORTHY OFF;
GO
EXECUTE AS LOGIN = N'myAppUser';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO
SELECT * FROM [dbo].[topSecret]; -- error: same as in Tests 1, 2, 3, and 4
EXEC [dbo].[getSecrets]; -- SUCCESS!!!
EXEC [dbo].[getSecretsWithDynamicSql]; -- SUCCESS!!!
GO
REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO
/************************  TEST 6 *************************************/
-- REMOVE Login/User: [myAppUserEscalated]
EXEC(N'
  USE [ChainingSource];
  IF (OBJECT_ID(N''[dbo].[getSecretsWithDynamicSqlAndImpersonation]'') IS NOT NULL)
  BEGIN
    DROP PROCEDURE [dbo].[getSecretsWithDynamicSqlAndImpersonation]
  END;
  IF (OBJECT_ID(N''[dbo].[getSecretsWithImpersonation]'') IS NOT NULL)
  BEGIN
    DROP PROCEDURE [dbo].[getSecretsWithImpersonation]
  END;
  IF (SUSER_ID(N''myAppUserEscalated'') IS NOT NULL)
  BEGIN
    DROP USER [myAppUserEscalated];
  END;
USE [ChainingDestination];
  IF (SUSER_ID(N''myAppUserEscalated'') IS NOT NULL)
  BEGIN
    DROP USER [myAppUserEscalated];
  END;
USE [master];
  IF (SUSER_ID(N''myAppUserEscalated'') IS NOT NULL)
  BEGIN
    DROP LOGIN [myAppUserEscalated];
  END;
');
GO
-- Now, re-run Test 5, just to be sure that it is only the module-signing that matters
--========================================

Impersonation vs. Module Signing

The part I don't understand is why, when running under impersonation, module signing doesn't work. ... What about impersonation makes the module signing context change to "deny only"?

The problem is that these questions are framed incorrectly. Module signing isn't supposed to be used in addition to Impersonation, but as a replacement of it; they are not complimentary features. The issue here is not how Impersonation affects module signing, but how Impersonation works in general. The structure of the original test script (in the question) is based upon this misunderstanding of the relationship between Impersonation and module signing. It includes module signing too early such that the behavior of Impersonation, by itself, cannot be seen clearly, thus leading to misleading implications.

If you run through the test script posted above, you should see that when Impersonation is used by itself (i.e. TRUSTWORTHY is set to OFF -- tests 1 and 2) then the server-level "usage" is DENY ONLY. Meaning: when you use Database-level Impersonation, the security context is, by default, quarantined to that particular database. It is not allowed to go up to the server level, neither to get server-level permissions of the associated Login, nor to go back down to another database.

This has nothing to do with module signing since the Certificate, Login, and Users haven't even been created yet (assuming you are stepping through the example in order). And module signing -- which does add permissions, and which can allow for cross-database access -- cannot override the DENY since DENY permissions always take precedence over GRANT permissions. That DENY can only be circumvented by TRUSTWORTHY ON.

The only thing that can remove the server-level DENY permission when using Impersonation is setting TRUSTWORTHY to ON for the source database. Tests 3 and 4 show that once TRUSTWORTHY is enabled, then Impersonation is allowed to cross between databases. And again, this has nothing to do with module signing since that does not get set up until after test 4. Module signing isn't necessary to get the overall scenario working; all you need is Impersonation and TRUSTWORTHY ON. However, module signing is required if you don't want to enable TRUSTWORTHY, in which case it replaces the need for the Impersonation.

The following chart shows the various scenarios and what they require:

     Scenario         -->                 Requirements A               XOR   Requirements B
     ----------                ---------------------------------        |    --------------
Scope     Dynamic SQL --> DB_CHAINING    Impersonation   TRUSTWORTHY   XOR   Module Signing
Local      No               No               No              No         |        No
Local      YES              No               YES             No         |        YES
Cross-DB   No               YES              No              No         |        YES
Cross-DB   YES              No               YES             YES        |        YES

Hopefully it is clear that module signing can completely replace the need for DB_CHAINING ON, Impersonation, and TRUSTWORTHY ON. Given the scenario of having both Cross-DB functionality, and some of that involving Dynamic SQL, your choices are:

Set both DB_CHAINING ON and TRUSTWORTHY ON:

This will allow you to not use Impersonation unless it is required due to Dynamic SQL being used. Hence only some modules get the EXECUTE AS clause.
Set only TRUSTWORTHY ON:

This requires that all modules use Impersonation (i.e. have the EXECUTE AS clause). But, you can set DB_CHAINING to OFF.
Use only module signing:

This requires that the Certificate and User be created in both DBs, and that all Cross-DB modules in the source DB get signed. But, you can set both DB_CHAINING and TRUSTWORTHY to OFF!! And there is no need for Impersonation, even for local Dynamic SQL. This option handles everything more cleanly and more securely.

Confirmation from Microsoft

Enabling Cross-Database Access in SQL Server

Dynamic SQL

Cross-database ownership chaining does not work in cases where dynamically created SQL statements are executed unless the same user exists in both databases. You can work around this in SQL Server by creating a stored procedure that accesses data in another database and signing the procedure with a certificate that exists in both databases. This gives users access to the database resources used by the procedure without granting them database access or permissions.
Extending Database Impersonation by Using EXECUTE AS

Understanding Impersonation Scope

...

However, when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error.

Also, there is a lot of good information on the "Extending Database Impersonation by Using EXECUTE AS" MSDN page (linked above) that explains authenticators and the reasoning behind these rules.

For more information, please see:

All of the above makes sense. The part I don't understand is why, when running under impersonation, module signing doesn't work. I could run as the escalated user normally (i.e. not under impersonation) and it would work. What about impersonation makes the module signing context change to "deny only"? Or, perhaps a related question, what sort of attack would be possible if the current behavior was not in place? — Ben Thul, Oct 31 '16 at 20:47
@BenThul Please see the new section I added, just below the example code. Hopefully that clarifies what is going on here. If not, please let me know. — Solomon Rutzky, Nov 01 '16 at 16:27
I understand that I can use module signing by itself. The reality of the situation though is that I have a lot of procedures that already have an execute as clause and there is no way (to my knowledge) to remove only that procedure option without scripting out the procs, removing the clause, then running those alter procedure staements and signing the altered procedures all in one shot. So, everything you're saying is technically correct, but practically a huge PITA to implement. — Ben Thul, Nov 01 '16 at 17:24
@BenThul Understood, but you have two options: 1) set TRUSTWORTHY ON, or 2) remove EXECUTE AS and sign the modules. Either way, as per Microsoft (I just added quotes from documentation to the end of my answer), everything I stated is accurate, and level of effort required to deal with the situation doesn't affect that validity. Also, it shouldn't be a PITA. You can easily SELECT REPLACE([definition], N'EXECUTE AS ...', N'') FROM sys.sql_modules WHERE... in a CURSOR, change the CREATE to ALTER, and EXEC it. Then EXEC results of SELECT 'ADD SIG TO ' + name FROM sys.sql_modules WHERE.... — Solomon Rutzky, Nov 01 '16 at 19:19
@BenThul Another thought regarding making this change: this is a perfect opportunity to script all objects out to individual scripts and import into source control. Then it is really easy to do a find/replace over the files to remove the EXECUTE AS lines and add in the ADD SIGNATURE lines, then save again into source control so you have the history. then it should be trivial to create a deployment script by concatenating the scripts into 1 file and changing the CREATE into ALTER. — Solomon Rutzky, Nov 01 '16 at 21:07
Hi @BenThul . I was wondering if there were any lingering questions here, or any reason(s) to not accept this answer. From what I can tell, I believe I have answered it completely. Please let me know. — Solomon Rutzky, Dec 26 '16 at 18:17
What makes it unsatisfactory to me (and this is in no way a failing on your part) is that I still don't know why the presence of the EXECUTE AS clause ruins it. One of the use cases for module signing is to grant permissions that the executing principal might not have. I would assume that also extends a) to impersonation contexts and b) to cross-database contexts. — Ben Thul, Dec 27 '16 at 00:40
@BenThul I thought I had addressed that explicitly, starting with the Impersonation vs. Module Signing section. Yes, module signing is to grant additional permissions. BUT, your assumptions are a little off, at least in terms of the logical operator: it should be XOR instead of AND ;-). The issue is that Impersonation is simply not allowed across databases, at least not without setting TRUSTWORTHY ON. With TRUSTWORTHY OFF Impersonation causes the context to become DENY. This has nothing to do with module signing, and nothing outside of TRUSTWORTHY ON can change that. — Solomon Rutzky, Dec 27 '16 at 22:32
Thanks for that. Just to put it into my own words, even though there is no explicit DENY in place for the impersonated user, because that user is coming from a non-trustworthy database, there's an effective DENY in place? That's... different from how permissions work in the rest of the product (which is to check explicit permissions and union all of them that apply to you). — Ben Thul, Jan 04 '17 at 03:15
@BenThul Not exactly. There is an explicit DENY in place the moment you use database-level (i.e. User) Impersonation. If you run through my test script you will see it clearly in the results of the SELECT * FROM sys.login_token; at the top of each Stored Procedure. When you use db-level Impersonation, the security context is, by default, quarantined to that particular database. It is not allowed to go up to the server level, not to get server-level permissions of the associated Login, nor to go back down to another database. That DENY can only be circumvented by TRUSTWORTHY ON :-( . — Solomon Rutzky, Jan 04 '17 at 16:01
@BenThul I updated my answer to include that last clarification (i.e. my comment directly above). Also, in the test script, I remove the Certificate creation from the master DB and the Certificate-based Login as they aren't needed in this scenario (there are no server-level permissions / functionality being used). — Solomon Rutzky, Jan 04 '17 at 22:15

Stored procedure security with execute as, cross database queries, and module signing

1 Answers1

Linked