SQL Server – How to Add Signature to Stored Procedure by Certificate

certificatepermissionssql serversql-server-2017stored-procedures

I have a stored procedure in Database 1 that updates couple of tables in Database 1 and Database 2

To avoid granting calling login/user permissions to do table updates, I try to use code signing

Created certificate in the master database, and created login from this certificate
Then I've created users for this certificate-mapped login, in Database 1 and Database 2, and granted them permissions to do table updates

When I try to run below t-sql:

use [Database 1]
add signature to MyProcedure
by certificate MyCertificate

It throws:

Msg 15151, Level 16, State 1, Line 168
Cannot find the certificate 'MyCertificate', because it does not exist or you do not have permission.

Then I try

use [master]
add signature to [Database1].[dbo].MyProcedure
by certificate MyCertificate

I am in a sysadmin server role, but it throws:

Msg 15151, Level 16, State 1, Line 168
Cannot alter the object 'Database1.dbo.MyProcedure', because it does not exist or you do not have permission.

Question:

How can I properly add signature to my stored procedure in Database 1, by certificate that is in the master database ?

Best Answer

You need to copy the certificate from master to your user database. For example:

-- Copy master certificate to user database
USE master;
DECLARE @cert_id int = cert_id('your_certificate')
DECLARE @public_key  varbinary(MAX) = certencoded(@cert_id),
        @private_key varbinary(MAX) =
           certprivatekey(@cert_id,
              'your encryption password',
              'your decryption password')

--these values should not be NULL
SELECT @cert_id, @public_key, @private_key;

DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE your_certificate
       FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
       WITH PRIVATE KEY (BINARY = ' +
          convert(varchar(MAX), @private_key, 1) + ',
          DECRYPTION BY PASSWORD = ''your encryption password'',
          ENCRYPTION BY PASSWORD = ''your decryption password'')'

EXEC YourUserDatabase.sys.sp_executesql @sql;

EDIT

If the certificate is encrypted with the database master key instead of a password, omit the decryption password from CERTPRIVATEKEY (to decrypt with the source database DBMK) and omit the ENCRYPTION BY PASSWORD clause of CREATE CERTIFICATE (to encrypt with the target database DBMK). The password in this case is used only to encrypt the private key in transit.

-- Copy cert to user database
USE master;
DECLARE @cert_id int = cert_id('your_certificate')
DECLARE @public_key  varbinary(MAX) = CERTENCODED(@cert_id),
        @private_key varbinary(MAX) =
           CERTPRIVATEKEY(@cert_id,
              'your encryption password');

--these values should not be NULL
SELECT @cert_id, @public_key, @private_key;

--create certificate in target database encrypted with DBMK
DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE your_certificate
       FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
       WITH PRIVATE KEY (BINARY = ' +
          convert(varchar(MAX), @private_key, 1)
        + ', DECRYPTION BY PASSWORD = ''your encryption password'');'
PRINT @sql

EXEC YourUserDatabase.sys.sp_executesql @sql;
GO

I'll add that for only cross-database permissions, one doesn't need to create a login from the certificate. Create a certificate in a user database, copy the cert to other databases as needed, create a user from the certificate in each database and grant permissions to the cert user, and finally sign the proc with the certificate.

A server level principal is needed only when server-level permissions must be granted.

Related Question