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
Best Answer
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. RemovingEXECUTE AS
andmyAppUserEscalated
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 keepingTRUSTWORTHY
set toOFF
(and even keepingDB_CHAINING
set toOFF
).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: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 toON
, then the stored procedure,getSecrets
(no Impersonation or Dynamic SQL), works as desired between Databases. But thegetSecretsWithDynamicSql
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 toON
(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 wantTRUSTWORTHY
to beON
as it is a security risk. This test is just to show how things used to be, prior to module signing (i.e. thatTRUSTWORTHY
is required when using Impersonation, which in turn is required when using Dynamic SQL).Test 4 shows that when both
DB_CHAINING
andTRUSTWORTHY
are set toON
, 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 wantTRUSTWORTHY
to beON
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
andTRUSTWORTHY
back toOFF
, 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:
Impersonation vs. Module Signing
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 toOFF
-- tests 1 and 2) then the server-level "usage" isDENY 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
sinceDENY
permissions always take precedence overGRANT
permissions. ThatDENY
can only be circumvented byTRUSTWORTHY ON
.The only thing that can remove the server-level
DENY
permission when using Impersonation is settingTRUSTWORTHY
toON
for the source database. Tests 3 and 4 show that onceTRUSTWORTHY
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 andTRUSTWORTHY ON
. However, module signing is required if you don't want to enableTRUSTWORTHY
, in which case it replaces the need for the Impersonation.The following chart shows the various scenarios and what they require:
Hopefully it is clear that module signing can completely replace the need for
DB_CHAINING ON
,Impersonation
, andTRUSTWORTHY 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
andTRUSTWORTHY 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 setDB_CHAINING
toOFF
.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
andTRUSTWORTHY
toOFF
!! 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
Extending Database Impersonation by Using EXECUTE AS
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: