我有一个场景，我正在将数据库从一台服务器恢复到另一台服务器。在源服务器上，数据库主密钥 (DMK) 使用密码和服务主密钥 (SMK) 进行加密。当我去将它恢复到新服务器时，里面的行sys.key_encryptions仍然说它是由 SMK 加密的。但事实并非如此，因为两台服务器之间的 SMK 不匹配。是否有任何编程方式来验证 DMK 确实使用此服务器的SMK 加密？
我有一种情况，虽然我能够解决它（正如重现将显示的那样），但我不明白。这里是高点
- 两个数据库，ChainingSource 和 ChainDestination，它们都将跨数据库链接设置为 true
- ChainingSource 中的存储过程通过
EXEC(@sql)访问 ChainingDestination 中的表 - 存储过程是用一个
execute as子句定义的 - 如果我尝试按原样执行该过程，则表示执行上下文的服务器主体无法访问 ChainingDestination
- 因此，我将证书和代码签名添加到组合中。也就是说，我将证书映射登录添加到服务器，将用户映射到每个数据库，并相应地授予证书映射用户权限
- 如果我保留该
execute as子句，我会得到同样的错误。 - 如果我删除该
execute as子句，一切都很好。
这是我感到困惑的倒数第二点。或者，具体来说，为什么那个不起作用而最后一个起作用。
/******************************
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
