I have created a few Certificates (via CREATE CERTIFICATE ) and Asymmetric Keys (via CREATE ASYMMETRIC KEY ) and used them to sign and counter-sign various SQL modules: - Stored Procedures - User-Defined Functions (UDFs) - Triggers - Assemblies Signing was done via ADD SIGNATURE . But now I need to find which Certificates and/or Asymmetric Keys have been used to sign which particular modules.

I want to sign plaintext using a SQL Server certificate and then verify that signature using the certificate's public key on an external system. Here's what I've tried.

-- Create a self-signed certificate
CREATE CERTIFICATE MyCert WITH SUBJECT = 'Integrations';

-- Output the public certificate
SELECT [der_hex] = CERTENCODED(CERT_ID('MyCert'));

Using CyberChef, I can convert this binary output into a PEM format and also verify that it parses as a X.509 certificate with an RSA public key: CyberChef example . After writing this PEM-format certificate to a file, then using openssl I can extract this RSA Public Key in PEM format:

% openssl x509 -inform pem -in cert.pem -pubkey -noout
-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAvHYJf/jNWetCyySQBWVO
S9M8maFmMQXOZDG2eXHsw92rCjhNbtFsWBi077rQrN/zAXuiiWC1oFAwHMOrMleI
jZCaghqdBr9HI5IbCe1eZvucg3K+AfLx3iZ9zr//HSh+0bKVScWW9POW19F2F8z/
uIWnHDOXEDO0BrZ7W9thl0WRaY+dG8jPtQSxL8fVhcho1g6fF3vmD1zpiIHmAiZI
h0np0rdYQ/pj8aK7jRLgPlsz4JqQ5JZBhtLmxK5Vz6OZ6+ocqfltmuL0f5wGbd8M
e2qmXcBB6DFJm4xs+Ey7FqSjAMFiMBNYWt2NbEeAKHxBjXOjM0vbtGaVyT8uaSJW
JQIDAQAB
-----END PUBLIC KEY-----

Now, I can have SQL Server sign some data:

SELECT [signature] = SIGNBYCERT(CERT_ID('MyCert'), 'hello');

And now, again using CyberChef, I want to verify the signature against the same plaintext using the public key: CyberChef example . Unfortunately, this signature does not verify and I'm unable to figure out where I'm going wrong. [The documentation for SIGNBYCERT()](https://learn.microsoft.com/en-us/sql/t-sql/functions/signbycert-transact-sql?view=sql-server-2016) is not exactly tremendous, in that it is not specific about any particular standards or formats that are used to generate the signature. I am assuming that it is an RSA signature by virtue of the metadata in the certificate used to create the signature. But I recognize that this could be an issue of text encoding; I'm sure the text I'm verifying in CyberChef is UTF-8, and nowhere in the SQL code have I declared what text encoding to use when generating the signature -- and indeed, if I pass in N'hello' instead of 'hello', the signature output changes. However, this seems to be a moot point since when attempting to verify a signature in CyberChef, the error I receive is Error: Encryption block is invalid. This would indicate an issue with the signature format, not with the data simply not validating -- valid or invalid data instead would receive a Verification failure or Verified OK message. [This GitHub issue](https://github.com/digitalbazaar/forge/issues/316) indicates it could be a difference in padding algorithm. I think I am also concerned about the internal structure of SQL Server's signature output not being "formatted" the right way, but I am not really knowledgeable enough to know how far-fetched that is. Assuming that nothing super proprietary is going on here, what do I have to do to verify a SQL Server signature with external systems?

Assume that there is a certificate created in database create certificate certName with subject = 'subj'; GO And a user mapped to this certificate create user userName from certificate certName; GO Trying to impersonate this user directly execute as user = 'userName'; GO or specifying user in the execute as clause of the module create procedure procName with execute as 'userName' as set nocount on; GO returns error > Msg 15517, Level 16, State 1 ...
Cannot execute as the database > principal because the principal "userName" does not exist, this type > of principal cannot be impersonated, or you do not have permission. However, I was not able to find this restriction mentioned in documentation (here and here ), where the only relevant statement seems is > user_name must exist in the current database and must be a singleton > account. user_name cannot be a group, role, certificate, key, or > built-in account, such as NT AUTHORITY\LocalService, NT > AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem. Is it possible to impersonate user (or login) mapped to certificate or not?

I'm trying to add "System.Messaging.dll" using the guidance provided by Solomon Rutzky in his post Assembly deployment with permission UNSAFE or EXTERNAL_ACCESS using asymmetric key but I'm failing at the first hurdle. The first part of the script is to create a certificate from the assembly; CREATE CERTIFICATE [MS.NETcer] FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'; GO However, when I execute this I get the error; Msg 15208, Level 16, State 1, Line 1 The certificate, asymmetric key, or private key file does not exist or has invalid format. The account I'm using to execute the command has the 'sysadmin' server role. This is on a SQL Server 2008 instance. Please does anyone have any ideas regarding why this is failing? **---------- Update 1 -----------** I've taken Solomon's advice and amended my script so it looks like this; CREATE ASYMMETRIC KEY [Key.System.Messaging] FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'; GO CREATE LOGIN [CLR.Login.System.Messaging] FROM ASYMMETRIC KEY [Key.System.Messaging]; GO GRANT UNSAFE ASSEMBLY TO [CLR.Login.System.Messaging]; GO All good so far. I now run the CREATE ASSEMBLY command to add the assembly to the SQLCLR; CREATE ASSEMBLY [System.Messaging] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll' WITH PERMISSION_SET = UNSAFE GO but this fails with the error; *CREATE ASSEMBLY for assembly 'System.Messaging' failed because assembly 'System.Windows.Forms' is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.* I've tried creating an asymetric key for 'System.Windows.Forms' CREATE ASYMMETRIC KEY [Key.System.Windows.Forms] FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll'; GO but this fails with; *Msg 15468, Level 16, State 1, Line 1 An error occurred during the generation of the asymmetric key.* so I tried; CREATE CERTIFICATE [Cer.System.Windows.Forms] FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll'; GO and this fails with; *Msg 15208, Level 16, State 1, Line 1 The certificate, asymmetric key, or private key file does not exist or has invalid format.* So now I'm stuck again and wondering if its possible to use System.Messaging.dll in the SQL CLR without having to enable the TRUSTWORTHY database property *(and also why on earth does System.Messaging.dll need to have a dependency on System.Windows.Forms.dll!)* Any thoughts gratefully received

I have a certificate I would like to remove from the database. If I issue the command DROP CERTIFICATE I get the error The certificate cannot be dropped because one or more entities are either signed or encrypted using it According to Jason Strate, I should be able to find out what's signed by the certificate. The following query returns 0 rows: SELECT OBJECT_SCHEMA_NAME(co.major_id) + '.' + OBJECT_NAME(co.major_id) FROM sys.certificates c INNER JOIN sys.crypt_properties co ON c.thumbprint = co.thumbprint WHERE co.crypt_type_desc = 'SIGNATURE BY CERTIFICATE' AND c.name = 'FooCert' I've also tried decoupling the entities per this SO question. https://stackoverflow.com/questions/52460/how-do-i-find-and-decouple-entities-from-a-certificate-when-upgrading-ms-sqlserv How can I remove the dependencies on this cert so I can remove it?

We have the 2 databases. - Reporting - HR There are some users who want to extract data from HR database. But we don't want to give direct access to that HR database. So, we created the special views in Reporting database and the users have **dbowner** right for that Reporting database. But, when they try to run the query, the error message is showing... The server principal "test" is not able to access the database "HR" under the current security context. When I grant them as the **db_datareader** reader for HR database, it's all fine and they can run the query. But it breaks the security and we don't want them to get access to the HR database directly. That's why we made the special views in Reporting database. How can I enable those users to run the view/query without giving direct access to the HR database?

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

So, given MS SQL 2008R2 or better: I want to not only store data in a table but be able for my application to automatically verify it was not modified. Solution seems to be to use SignByCert to make a digital signature of every cell and insert each result into a matching table full of signatures so I can call VerifySignedByCert later when I query it to see if someone changed the plaintext data. https://learn.microsoft.com/en-us/sql/t-sql/functions/signbycert-transact-sql https://learn.microsoft.com/en-us/sql/t-sql/functions/verifysignedbycert-transact-sql My question is, signbycert wants an optional password to decrypt the private key it is signing with. I don't want to store private key passpharses in my stored procedures that call SignByCert. It doesn't seem to make sense to just leave the passphrases in the clear. Alternatively, is there a secure (good) way to reference the passphrase or is there a safe way to store or NOT encrypt the private key in MS SQL so MS SQL hides the passphrase and knows it internally so I don't have to put it in the SQL statement?

**Question:** I am trying to create a single (self signed) certificate and deploy across many databases and instances. I create and backup the cert with: USE MASTER CREATE CERTIFICATE DavesCert ENCRYPTION BY PASSWORD ='S3creT!' WITH SUBJECT = 'The master cert' BACKUP CERTIFICATE DavesCert TO FILE = 'd:\DavesCert.cer' WITH PRIVATE KEY ( DECRYPTION BY PASSWORD = 'S3creT!' , FILE = 'd:\DavesCert.pvk' , ENCRYPTION BY PASSWORD = 'S3creT!' ); I restore with USE FOO GO CREATE CERTIFICATE ERecruitStatsGatheringCert FROM FILE = 'd:\DavesCert.cer' WITH PRIVATE KEY (FILE = 'd:\DavesCert.pvk', DECRYPTION BY PASSWORD = 'S3creT!') and get the following error: "Please create a master key in the database or open the master key in the session before performing this operation." I don't want to create a database master key. I'm happy to decrypt the cert by password as needed. **Background:** SaaS application. Many to Many relationship between DB's and instances. I need each DB to be able to query it's own stats by executing a stored procedure that wraps up some calls to DMV's to return the stats. App runs under a low-privileged account. DMV's require VIEW SERVER STATE permission, therefore, I'm implementing signing of the stored procedures using certs. Basic way of setting this up is to: 1. Create a cert in the user DB. 2. Backup/restore that cert to master. 3. Create login in master, assigned that permissions, etc. 4. Add Certificate to stored procedure. I have test code for the above and it works well, however the model does not scale very well for deployment across multiple instances/dbs. Therefore, I _think_ I want to use the same cert across all DB's/instances. Happy for other suggestions/approaches. --Originally posted to Stack Exchange then moved on suggestion from another use

I have a procedure TheNotificationProcedure that makes a cross-database call to msdb.dbo.sp_send_dbmail. It gets invoked (indirectly) from a service broker queue: CREATE QUEUE [Blah].[TheQueue] WITH ACTIVATION (STATUS = ON, PROCEDURE_NAME = [Blah].[TheQueueProcedure], MAX_QUEUE_READERS = 1, EXECUTE AS N'TheUser'); TheQueueProcedure eventually calls TheNotificationProcedure If I connect in SSMS as TheUser and execute TheNotificationProcedure, everything works and emails go out. However, when TheNotificationProcedure is invoked as a result of a message arriving on the queue, it fails as unable to access the msdb procedure. I've tried everything I can think of, including creating my own procedure in msdb that wraps sp_send_dbmail and signing both my dbmail wrapper and TheNotificationProcedure with the same certiface, and ensure the certificate user in msdb is a member of "DatabaseMailUserRole". Finally, after doing many more detailed traces, I eventually noticed the following: That is even though the service broker is executing under the *login* of TheUser, for some reason, it is executing under the database *user* of guest, which I suspect at least partially explains my permissions issues. The *login* TheUser is also mapped to a *user* in msdb called TheUser - it is certainly not mapped to guest. So why is it being executed as guest in msdb when going through service broker? I need to avoid marking the database as Trustworthy. I was hoping that by signing the procedures (e.g. http://www.sommarskog.se/grantperm.html) I could get permissions to transfer across the database - does execute as negate any permissions that would typically be associated via the certificate user? Here is a script to duplicate the above permissions issue *without* any of the module signing (which gives the same "guest" trace) when going through service broker: Setup: --REPLACE EMAIL, and db_mail profile --@profile_name = 'Test db mail profile', --@recipients = 'test@test.test', use master; GO IF EXISTS(select * FROM sys.databases where name='http://dba.stackexchange.com/questions/166033 ') BEGIN ALTER DATABASE [http://dba.stackexchange.com/questions/166033] SET OFFLINE WITH ROLLBACK IMMEDIATE; ALTER DATABASE [http://dba.stackexchange.com/questions/166033] SET ONLINE WITH ROLLBACK IMMEDIATE; DROP DATABASE [http://dba.stackexchange.com/questions/166033] ; END CREATE DATABASE [http://dba.stackexchange.com/questions/166033] ; GO IF EXISTS(select * FROM sys.server_principals WHERE name = 'TheUser' AND type_desc='SQL_LOGIN') DROP LOGIN TheUser; CREATE LOGIN [TheUser] WITH PASSWORD=N'jL839lIFKttcm3cNuk1WUazfk5lS76RKMscZ01UdFkI=' , DEFAULT_DATABASE=[http://dba.stackexchange.com/questions/166033] , DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; use [msdb]; GO IF (NOT EXISTS(select * FROM sys.database_principals WHERE name = 'TheUser')) CREATE USER [TheUser] FOR LOGIN [TheUser] WITH DEFAULT_SCHEMA=[dbo]; exec sp_addrolemember 'DatabaseMailUserRole', 'TheUser'; GO use [http://dba.stackexchange.com/questions/166033] ; GO CREATE USER [TheUser] FOR LOGIN [TheUser] WITH DEFAULT_SCHEMA=[dbo] GO CREATE SCHEMA [Blah] AUTHORIZATION dbo; GO CREATE QUEUE [Blah].[SourceQueue]; GO CREATE SERVICE [//FromService] AUTHORIZATION [dbo] ON QUEUE [Blah].[SourceQueue]; GO CREATE MESSAGE TYPE [//TestMessage] AUTHORIZATION [dbo] VALIDATION = NONE; GO CREATE CONTRACT [//ServiceContract] AUTHORIZATION [dbo] ([//TestMessage] SENT BY INITIATOR); GO CREATE PROCEDURE [Blah].[SendMessage] AS DECLARE @message varchar(50), @conversationHandle UNIQUEIDENTIFIER SET @message = 'Test Message Content'; -- Begin the dialog. BEGIN DIALOG CONVERSATION @conversationHandle FROM SERVICE [//FromService] TO SERVICE '//ToService' ON CONTRACT [//ServiceContract] WITH ENCRYPTION = OFF; -- Send the message on the dialog. SEND ON CONVERSATION @conversationHandle MESSAGE TYPE [//TestMessage] (@message) ; END CONVERSATION @conversationHandle ; GO CREATE PROCEDURE [dbo].[TheNotificationProcedure] AS PRINT 'DEBUG - Entering [dbo].[TheNotificationProcedure]' -- Send notification PRINT 'DEBUG - [dbo].[TheNotificationProcedure] - PRIOR TO msdb.dbo.sp_send_dbmail' declare @log nvarchar(max) = ''; select @log = @log + 'name: ' + name + ' ' + 'type: ' + type + ' usage: ' + usage + ' || ' FROM sys.login_token print @log declare @mailitem_id int; --exec [msdb].[dbo].[WRAP__sp_send_dbmail] exec [msdb].[dbo].[sp_send_dbmail] @profile_name = 'Test db mail profile', @recipients = 'test@test.test', --@Recipient, @subject = 'Testing sp_send_dbmail', --@NotificationSubject, @body = 'Testing sp_sdend_dbmail from service broker', --@NotificationBody, @exclude_query_output = 1, @mailitem_id = @mailitem_id OUTPUT PRINT 'DEBUG - [dbo].[TheNotificationProcedure] - AFTER msdb.dbo.sp_send_dbmail' GO CREATE PROCEDURE [Blah].[TestMessageHandler] AS --has other logic that eventully calls notification EXECUTE [dbo].[TheNotificationProcedure] GO CREATE PROCEDURE [Blah].[TheQueueProcedure] AS --Service Broker variables DECLARE @conversation_handle UNIQUEIDENTIFIER, @conversation_group_id UNIQUEIDENTIFIER, @message_body varchar(255), @message_type_name NVARCHAR(256), @dialog UNIQUEIDENTIFIER, @RowsReceived int PRINT 'Start' WHILE (1 = 1) BEGIN -- Get next conversation group. WAITFOR( GET CONVERSATION GROUP @conversation_group_id FROM [Blah].[TheQueue]), TIMEOUT 500 ; -- If there are no more conversation groups, roll back the -- transaction and break out of the outermost WHILE loop. IF @conversation_group_id IS NULL BEGIN BREAK ; END ; WHILE (1 = 1) BEGIN BEGIN TRANSACTION PRINT 'Get Message' ; RECEIVE TOP (1) @dialog = conversation_handle, @message_type_name=message_type_name, @message_body=message_body FROM [Blah].[TheQueue] WHERE conversation_group_id = @conversation_group_id ; SET @RowsReceived = @@ROWCOUNT PRINT 'Queue Read: ' + ISNULL(@message_body, '') PRINT '@RowsReceived: ' + CAST(@RowsReceived as varchar(200)) IF (@RowsReceived = 0) BEGIN BREAK ; END ; PRINT 'Deal with Message' IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog ') BEGIN PRINT 'End Dialog received for dialog # ' + cast(@dialog as nvarchar(40)) ; END CONVERSATION @dialog ; END ; IF (@message_type_name = '//TestMessage') BEGIN print 'Have //TestMessage: ' + @message_body exec [Blah].[TestMessageHandler]; END COMMIT TRANSACTION; END END RETURN GO CREATE QUEUE [Blah].[TheQueue] WITH ACTIVATION (STATUS = ON, PROCEDURE_NAME = [Blah].[TheQueueProcedure], MAX_QUEUE_READERS = 1, EXECUTE AS N'TheUser'); GO CREATE SERVICE [//ToService] AUTHORIZATION [dbo] ON QUEUE [Blah].[TheQueue] ([//ServiceContract]); GO GRANT EXECUTE ON [Blah].[TheQueueProcedure] TO [TheUser]; GO Then to kick everything off: --kick everything off EXEC [Blah].[SendMessage]; GO --read results from error log --(might need to execute once or twice to get results - because service broker is asynchronous) declare @sqlErrorLog table (LogDate datetime, ProcessInfo nvarchar(max), Text nvarchar(max)); INSERT INTO @sqlErrorLog EXEC xp_ReadErrorLog SELECT * FROM @sqlErrorLog WHERE LogDate >= DATEADD(SECOND, -15, GETDATE()) AND Text NOT LIKE 'CHECKDB%' AND Text NOT LIKE 'Starting up database ''upgrade%' AND Text NOT LIKE '%upgrade%information%' AND TEXT 'Error: 9001, Severity: 21, State: 1.' ORDER BY LogDate

In the documentation for ADD SIGNATURE , they have the following caveat: > Module signing should only be used to grant permissions, never to deny or revoke permissions. My question is: why? Conceptually, I think of signature possibly adding to the user token. If the user linked to the certificate or key that signed the module has a DENY permission associated with it, I'd think that it would come along for the ride. My testing shows that it doesn't, but I'd like to understand why it doesn't.

I use cross database certificates (as explained by Erland Sommarskog ) to control access to a certain database in my environment ( SQL Server 2008 R2 ). I have stored procedures in database A that update tables in database B. This has always worked for a variety of stored procedures in db A and tables in db B until now. I am trying to update a table in db B, but the table has a trigger on it. This trigger is inserting additional data in another table in db B. I am getting the error: > Msg 916, Level 14, State 1, Procedure table_trigger, Line 11 The server > principal "sql\login" is not able to access the database > "B" under the current security context. I tried granting insert permissions for database B user that is tied to the certificate to insert into that other table, but it did not resolve the error. Do I have any options other than changing the trigger so it uses WITH EXECUTE AS OWNER? Here is DDL to replicate the issue: CREATE LOGIN [GuggTest] WITH PASSWORD=N'abcd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF CREATE DATABASE A; CREATE DATABASE B; USE A; CREATE TABLE dbo.SPtoUpdate ( ID INT , ILoveFishing VARCHAR(255) ); INSERT INTO dbo.SPtoUpdate ( ID , ILoveFishing ) VALUES ( 1,'Musky'),( 2,'Pike'),( 3,'Yellow Perch'); CREATE TABLE dbo.TriggerToInsert ( ID INT , ILoveFishing VARCHAR(255) , ChangeDate DATETIME2 ); GO CREATE TRIGGER dbo.SPtoUpdateTrigger ON dbo.SPtoUpdate FOR UPDATE AS DECLARE @datetime DATETIME2; SELECT @datetime = GETDATE() INSERT INTO dbo.TriggerToInsert ( ID , ILoveFishing , ChangeDate ) VALUES ( 1 , 'Yes' , @datetime ); GO CREATE CERTIFICATE BExecutor ENCRYPTION BY PASSWORD = 'Obfuscated' WITH SUBJECT = 'Execute sp from B to A', START_DATE = '20140101', EXPIRY_DATE = '20300101' GO BACKUP CERTIFICATE BExecutor TO FILE = 'C:\temp\crossdbcert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' , ENCRYPTION BY PASSWORD = 'Obfuscated', DECRYPTION BY PASSWORD = 'Obfuscated') GO CREATE USER BExecutor FROM CERTIFICATE BExecutor GRANT UPDATE ON dbo.SPtoUpdate TO BExecutor GRANT SELECT ON dbo.SPtoUpdate TO BExecutor --Also give insert on dbo.TriggerToInsert GRANT INSERT ON dbo.TriggerToInsert TO BExecutor USE B GO CREATE USER [GuggTest] FOR LOGIN [GuggTest]; EXEC sp_addrolemember N'db_owner', N'GuggTest' GO CREATE PROCEDURE dbo.UpdateTableInA AS BEGIN UPDATE A.dbo.SPtoUpdate SET ILoveFishing = 'Walleye' WHERE ID = 2; END GO CREATE CERTIFICATE BExecutor FROM FILE = 'C:\temp\crossdbcert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' , ENCRYPTION BY PASSWORD = 'Obfuscated', DECRYPTION BY PASSWORD = 'Obfuscated') GO EXEC master..xp_cmdshell 'DEL C:\temp\crossdbcert.*', 'no_output' GO ADD SIGNATURE TO dbo.UpdateTableInA BY CERTIFICATE BExecutor WITH PASSWORD = 'Obfuscated' GO --Log In or Change execution context to GuggTest, then EXEC dbo.UpdateTableInA

Running a SQL Server 2012 CLR stored procedure with this code: const string executeLog = "EXEC master..xp_logevent @errorNumber, @message, informational"; using (SqlCommand cmd = new SqlCommand(executeLog, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@errorNumber", 60001); cmd.Parameters.AddWithValue("@message", url); cmd.ExecuteScalar(); } I get this error: > System.Data.SqlClient.SqlException: The EXECUTE permission was denied > on the object 'xp_logevent', database 'mssqlsystemresource', schema > 'sys'. I've tried to do this as sa user: grant execute on xp_logevent to sa But I get another error: > You can not find user 'sa' that does not exist or the user does not > have permission. How can I grant execute permissions to sa user?

I have a two users, user1 and user2. User1 has sysadmin rights and I have created a database level trigger through sa. Now I am trying to create a table through user2 who has db-ddladmin rights and db_datareader. I'm getting the error: 'Msg 297, Level 16, State 1, Procedure TR_CaptureDBChanges, Line 35 The user does not have permission to perform this action. Code: CREATE TRIGGER [TR_CaptureDBChanges] ON DATABASE FOR CREATE_PROCEDURE, ALTER_PROCEDURE,DROP_PROCEDURE,CREATE_TABLE, ALTER_TABLE,DROP_TABLE,CREATE_TRIGGER,ALTER_TRIGGER,DROP_TRIGGER,CREATE_VIEW,ALTER_VIEW,DROP_VIEW,CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION BEGIN DECLARE @ed XML SET @ed = EVENTDATA() DECLARE @ip VARCHAR(32) = (SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID); INSERT INTO DBChangesLogs ( EventDate,DBName)VALUES(GetDate(),@ed.value('(/EVENT_INSTANCE/DatabaseName)', 'varchar(256)')) END

I have a Microsoft SQL Server 2014. The mail system is configured via DBMail and there is one stored proc in main web app (having its own database) which executes msdb's *sysmail_update_account_sp* by selecting the appropriate profile using *msdb.dbo.sysmail_account*. The problem I am facing is the web application has a SQL login and I want to switch context and execute *sysmail_update_account_sp*. EXEC AS USER = @UserName SET @AccountNr = ( SELECT TOP 1 account_id FROM msdb.dbo.sysmail_account WHERE name = @DatabaseName ORDER BY account_id ) IF @AccountNr > 0 EXECUTE msdb.dbo.sysmail_update_account_sp @account_id = @AccountNr , @email_address = @ParamSMTPFrom , @account_name = @DatabaseName , @display_name = @DatabaseName , @replyto_address = @ParamSMTPFrom , @description = @EmailAccountDescription , @mailserver_name = @ParamWebAppSmtpServer --[ , [ @mailserver_type = ] 'server_type' ] 'optional only SMTP supported' , @port = @ParamWebAppSMTPPort , @username = @ParamWebAppSMTPUser , @password = @ParamWebAppSMTPPwd , @use_default_credentials = 0 -- we will always have nor domain credientials @DefaultCredentials --use account name and password --not databse credentials , @enable_ssl = @SSL -- bit 1 for use ssl REVERT Error occurring is: > Cannot execute as the database principal because the principal > "SERVERNAME\dbMailUser" does not exist, this type of principal cannot > be impersonated, or you do not have permission Things I checked: 1. SERVERNAME\dbMailUser has DatabaseMailUserRole permission in msdb currently. 2. If I add the executing stored proc user (from web app) as sysadmin,I don't have to switch or anything. But I want to limit the permission to it's minimum. How can I execute as a different user (SQL or Windows Login) by switching context properly? The current SQL login is a database owner. The requirement is the login should be able to configure DBMail from a stored procedure and able to send mails (the DatabaseMailUserRole), without it being in the sysadmin role. Am I in the right direction, regarding my understanding of how DBMail configuration works?

I simply would like a very limited user, in its own database, to call an update procedure on our database to tell us that it has updated a table, which it has access to. I have allowed the user login ("Doc") the "Impersonate any login" permission. Doc also has impersonate rights on the "App" login, which *exists* despite what the error messages seem to say. And I inserted the following statement near the beginning of the proc EXECUTE AS LOGIN = 'App'; "App" is in the db_owner of the database that I want to update. I was running into an issue with the WITH EXECUTE AS 'App' permission on the proc I was using, because our canned audit procedure calls procedures in another database, and I had read that such a permission was confined to the current database. But for some reason when I try the EXECUTE AS LOGIN statement it tells me that this very much used login cannot be used. Cannot execute as the server principal because the principal "App" does not exist, this type of principal cannot be impersonated, or you do not have permission. So, if this is telling me right, 1. logins aren't the type of principal that can be impersonated, or 2. despite the check boxes on the UI, "Doc" does not have the right to impersonate the user "App", or 3. The login that my application uses 700,00 times a day, "does not exist". Or this is just ***broken***. I'd *prefer* to give "sudo" type rights to limited users, but if I have to, I'll start traversing through the tree giving every single right the "limited" user needs for 24-hour access to table modifications which are *kind of* like the updates I want him to make. Despite that all I want him to do is call a proc that I created, update a particular column in a table in the Application database and log the result if it goes sideways. --- **UPDATE:** Base on what srutzky says below, I tried turning Trustworthy on in a restricted database with a new user and a new login and the rights to impersonate, and it worked. I guess I have to weigh the risk between marking a database as trustworthy and creating users in every database that login will touch.

I am using SSDT 14.0.50730.0 in Visual Studio Professional 2015. I am comparing databases between two instances and it is finding that there are stored procedures that do not match with regard to their signatures. In both instances, the Certificate was created in each instance from the same script and the signatures were also added by CERTIFICATE with a PASSWORD using the same script in each instance. However, Schema Comparison is showing the procedure in both the source and target as having a CERTIFICATE with a SIGNATURE with Signature values that do not match.The generated publish script drops the SIGNATURE BY CERTIFICATE, modifies the procedure, and then adds a SIGNATURE BY CERTIFICATE WITH SIGNATURE. The signature value matches what was shown in the source instance. However, the script fails with the message "The signature of the public key is invalid." What do I have to do to get these procedures in sync?

I have a certificate I am using for inter-DB access to a stored procedure in SQL Server, and am able to provide access to the stored procedure by creating a new user in the database using: CREATE CERTIFICATE testCertificate FROM FILE = 'test_certificate6.cer'; CREATE USER testCertificateUser FROM CERTIFICATE testCertificate; GRANT AUTHENTICATE TO testCertificateUser; GRANT EXECUTE ON storedProc1 TO testCertificateUser; but need to be able to add the inter-DB access to an existing database role (for compatibility with existing systems). How can I go about doing this?