0

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 would like 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 tried to run below T-SQL:

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

It throws error:

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 ?

1 Answer 1

2

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.

2
  • thank you @DanGuzman, the answer helped me many times. One thing to note is that I had to change @ sql from nvarchar(max) to varchar(max) recently, as resulting length of the statement was 4259 symbols; while nvarchar(max) seems to allow 4000 max, so statement was cut Oct 12, 2023 at 11:50
  • 1
    @AlekseyVitsko, nvarchar(MAX) should allow roughly 1 billion characters. I wasn't able to reproduce your issue with this script but try casting the concatenated literals as nvarchar(MAX) to avoid implicit conversion from nvarchar(MAX) to a truncated nvarchar(n) value. See this answer.
    – Dan Guzman
    Oct 12, 2023 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.