Unable to ADD SIGNATURE TO usp WITH SIGNATURE blob

Question

PROBLEM
I am unable to add a signature to a proc using the [WITH SIGNATURE = signed_blob] argument. Very grateful for any pointers.

OVERVIEW
I want to create cross database access with a signed proc. However, because I do not want to share the private key, I want to sign the proc using a signed BLOB. The concept is outlined in example B here: http://msdn.microsoft.com/en-us/library/ms181700.aspx which , I cannot get  to work.

TEST SCENARIO
The script below is an adaptation of John Kehavias' excellent script and uses the concepts presented in Laureniu Cristofor's blog. The script creates two DB's TargetDb and ReaderDb. TargetDb contains a populated table, a proc that reads the table, a certificate that signs the proc, and a user generated from the user in the master DB, see below. A backup of the public key is made. The master DB contains a copy of the certificate and two users, one from the certificate with required permissions and another, [MasterTestuserLoginX], with no permissions. ReaderDb contains a user for the master [MasterTestuserLoginX], a copy of the certificate, and a proc signed [unsuccessfully] using the signed_blob.

The first half of the script has to be run, upto the comment "-->>> STOP HERE", the BLOB must be copied into the readerDBx script, and then the second and final part of the script run.

-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
/********************************
Create two databases
*********************************/
CREATE DATABASE targetDbx
CREATE DATABASE readerDbx
GO

/********************************
Setup targetDbx
*********************************/
USE targetDbx
GO
CREATE TABLE testtbl (a int NOT NULL,
                      b int NOT NULL,
                      c int NOT NULL)
GO
-- Insert some test data.
INSERT testtbl (a, b, c) VALUES (12, 23, 34)
GO

-- Create a CERTIFICATE to sign the procedure.
CREATE CERTIFICATE cert_signature
    ENCRYPTION BY PASSWORD = 'SimplePwd01'
    WITH SUBJECT = 'ADD SIGNATURE demo';

GO
CREATE PROCEDURE signed_sp AS
    SELECT 'Hello from: ' + DB_NAME();
    SELECT a, b, c FROM targetDbx..testtbl
GO
-- Sign the procedure.
ADD SIGNATURE TO [signed_sp]
    BY CERTIFICATE [cert_signature]
    WITH PASSWORD = 'SimplePwd01' ;
GO


backup CERTIFICATE [cert_signature] to file = 'C:\cert_signature.cer'
GO
-- retrieve the signature from the catalogs and store the result for use below
select crypt_property [BLOB for cert signing in targetDbx]
from sys.crypt_properties where major_id = object_id('signed_sp')

-- Output from my system
-- 0x938531481351A7102D2A5DE654B00FA61A409ECA788A5FBED6B189C9CB9272958F44F213F082FA03CDCB7701F51F4FAC0BDF3E897BF48A43473332CF2CC3B140005E11E287CF7B61A7353B073683CCCBA9C54F045FD54595A8E62C27CEE658D9753C6001A78D78E8F58FFA2326819D56777F36B84D94C6CA6FA1E2184D4D6C4D


-- ###########################################################################################
-- Add Certificate to Master Database
-- ###########################################################################################
GO
USE master
GO

CREATE CERTIFICATE [SigningCertificateX]
FROM FILE = 'D:\Marko\cert_signature.cer';
GO
-- Create a login from the certificate
CREATE LOGIN [MasterSigningLoginX] FROM CERTIFICATE [SigningCertificateX];
GO
-- The Login must have Authenticate Sever access
-- per http://msdn.microsoft.com/en-us/library/ms190785.aspx
GRANT AUTHENTICATE SERVER TO [MasterSigningLoginX]

GO
-- Create a test login to test that the certificate signed procedure
-- can properly execute the target procedure without the login having
-- access to the target database or target procedure directly
CREATE LOGIN [MasterTestuserLoginX] WITH PASSWORD=N't3stp@$$', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO



-- ###########################################################################################
-- Create a user in targetDbx
-- ###########################################################################################
USE targetDbx
GO
CREATE USER [signingLoginX] FOR LOGIN [MasterTestuserLoginX]
GO
-- Grant EXECUTE on the target stored procedure to the
-- certificate based login
GRANT EXECUTE ON [dbo].[signed_sp] TO [signingLoginX]
GO

/***********************************************************************************
Exec script to here, note the BLOB detail above, amend the BLOB details below, and then
run the second half of the script.
***********************************************************************************/
-->>> STOP HERE

/********************************
Setup readerDbx
*********************************/
USE readerDbx
GO
CREATE USER [testuser] FOR LOGIN [MasterTestuserLoginX]
GO
CREATE CERTIFICATE cert_sign_target from file = 'C:\cert_signature.cer'
GO
CREATE PROCEDURE proc_Crossdb
WITH EXECUTE AS OWNER
AS
    select 'Hello from: ' + db_name();
    exec targetDbx..signed_sp
GO
add signature to proc_Crossdb by CERTIFICATE cert_sign_target
with signature = 0x938531481351A7102D2A5DE654B00FA61A409ECA788A5FBED6B189C9CB9272958F44F213F082FA03CDCB7701F51F4FAC0BDF3E897BF48A43473332CF2CC3B140005E11E287CF7B61A7353B073683CCCBA9C54F045FD54595A8E62C27CEE658D9753C6001A78D78E8F58FFA2326819D56777F36B84D94C6CA6FA1E2184D4D6C4D ;
/* Error message here.
    Msg 15299, Level 16, State 1, Line 2
    The signature of the public key is invalid.
*/
GO
GRANT EXECUTE ON proc_Crossdb TO [testuser];
GO

EXECUTE AS LOGIN = 'MasterTestuserLoginX'
EXECUTE proc_Crossdb;


GO
USE master
GO

/*
USE master
DROP DATABASE targetDbx
DROP DATABASE readerDbx
DROP LOGIN MasterSigningLoginX
DROP LOGIN [MasterTestuserLoginX]
DROP CERTIFICATE [SigningCertificateX]
*/

Answer 1

No, that will not work out, and there is a different and simpler solution for what you want to achieve: backup the certificate with the private key and restore it the other database. Sign it, and then drop the private key from the certificate with ALTER CERTIFICATE REMOVE PRIVATE KEY.

I have a long article on my web site that discusses procedure signing at great length, including case of cross-db access:
http://www.sommarskog.se/grantperm.html
Beware that the example on cross-db access does not drop the private key; you will need to add that.

I don't cover SIGNED_BLOB in my article, and I have not played with it a lot. I nevertheless believe that I understand when you would use it and how. Say that you develop an application that you ship to a customer site. Some of the procedures requires signing for whatever reason. When you send an install package, you don't want to include the private key of the certificate. Therefore, in a database at home you sign the procedure and extract the signature blob, and in the deploy script you use the SIGNED_BLOB option. Now here is the key part which explains that it not work in your case: the blob you have is only valid for exactly that procedure text you signed originally. In your example, you sign one procedure, and then try to apply that signature on a different procedure. Or more precisely on a different piece of text. If you would have the same procedure text in both databases, it would work out. Going back to the example where the procedure was deployed to a customer site, this means that the customer cannot change the procedure, and re-sign the procedure, because the old signature is no longer valid, and he does not have the private key.

Also, in the script you posted, you create the certificate login, as well as a login from that certificate which you grant Authenticate Server. There should not be any need for this if all you want is cross-database access. The only time you need to create a certificate and login in master is when you want to bundle server-level permissions in a stored procedure.

Finally, certificate signing is a very powerful way to package permissions with stored procedures in a controlled way.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Erland, many thanks for your reply. My apologies for failing to give credit to you for your excellent article Giving Permissions through Stored Procedures. In fact, I came to this site via the link you provide in that article.

Regarding your reply:

1) I agree with your assessment that my use of the [WITH SIGNATURE = signed_blob] argument above appears to be inappropriate. For interest I retried this technique but using precisely the same proc syntax in both DB's. Again it failed. If I ever discover the correct syntax, I'll post it here.

2) I amended your Cross Database script adding the [ALTER CERTIFICATE crossdbcert REMOVE PRIVATE KEY;] in db2. As you stated, this prevents the certificate being reused. I also restricted access to proc in db1, which is pretty much the solution I was looking for. Great.

3) It is a pity that in SQL 2008 there is no way to grant Cross DB access using certificates without passing the private key around? I note the solution in 2012, also documented on your site.

Your answer above is therefore the solution to my question.

TY

Answer 3

1) I agree with your assessment that my use of the [WITH SIGNATURE = signed_blob] argument above appears to be inappropriate. For interest I retried this technique but using precisely the same proc syntax in both DB's. Again it failed. If I ever discover the correct syntax, I'll post it here.

Probably there was some minor difference between the procedures. A space, an extra line-break - everything counts.

Here is a script that is not particularly useful, but it achieves the feat.

CREATE DATABASE db1
CREATE DATABASE db2
go
USE db1
go
CREATE PROCEDURE my_sleazy_proc AS
PRINT 'Some random garbage'
SELECT 'More contraband stuff' AS [This is enough]
go
CREATE CERTIFICATE cer
ENCRYPTION BY PASSWORD = 'Taxman'
WITH SUBJECT = 'Testing'
go
ADD SIGNATURE TO my_sleazy_proc BY CERTIFICATE cer WITH PASSWORD = 'Taxman'
go
BACKUP CERTIFICATE cer TO FILE = 'C:\temp\cer.bak'
go
USE db2
go
DECLARE @code nvarchar(MAX)
SELECT @code = sm.definition
FROM   db1.sys.objects o
JOIN   db1.sys.sql_modules sm ON o.object_id = sm.object_id
WHERE  o.name = 'my_sleazy_proc'
EXEC(@code)
go
CREATE CERTIFICATE cer FROM FILE = 'C:\temp\cer.bak'
go
DECLARE @blob varbinary(MAX)
SELECT @blob = cp.crypt_property
FROM   db1.sys.crypt_properties cp
JOIN   db1.sys.objects o ON cp.major_id = o.object_id
WHERE  o.name = 'my_sleazy_proc'
DECLARE @sql nvarchar(MAX) = 'ADD SIGNATURE TO my_sleazy_proc BY CERTIFICATE cer ' +
                             'WITH SIGNATURE=' + convert(nvarchar(MAX), @blob, 1)
PRINT @sql
EXEC(@sql)
go
USE tempdb
DROP DATABASE db1
DROP DATABASE db2
EXEC xp_cmdshell 'DEL C:\temp\cer.bak'

---

Erland Sommarskog, SQL Server MVP, [e-mail]