I’ve recently had to revisit this topic and spent a lot of time recalling the details. So I’m writing this blog post mainly as a reminder for myself.
The most helpful part will be the diagram detailing all the components and their relation and a comprehensive example anyone can follow.
I’m not going to cover Module Signing in general (I’ll leave that to Solomon Rutzky).
Nor will I cover other ways to achieve Cross DB access (like Cross DB Ownership chaining) because this is superior from the security standpoint.
A picture is worth a thousand words.
But commentary also helps. We have two databases, SourceDB and TargetDB.
SourceDB contains a module (e.g. Stored Procedure) that wants to access some table from TargetDB.
To do that, we’re going to need:
- Two databases
- A table in the TargetDB
- A stored procedure in the SourceDB
- Certificate in each database
- The Certificate in SourceDB must have a Private key
- The Certificate in the TargetDB must have the same thumbprint as the one in SourceDB
- If it’s only-one way communication, it doesn’t need the Private key
- A user created from Certificate in the TargetDB
- Only because we cannot grant permissions directly to a certificate
- Add the signature to the stored procedure in SourceDB
Demo time 🔗︎
Because we’ll be switching the context of the two databases fairly often, I’ll start each code block with the
USE DbName of the specific context.
It might be easier to follow along if you have two side by side sessions - one for each database.
First, we create the environment
USE [master] CREATE DATABASE SourceDB CREATE DATABASE TargetDB
Then let’s create a table in each DB and fill it with some data. I’ve chosen the table names to have the same initial as their respective DB.
USE TargetDB CREATE TABLE dbo.Tony ( Id int PRIMARY KEY , Letter char(1) ) INSERT INTO dbo.Tony (Id, Letter) VALUES (1, 'T') , (2, 'O') , (3, 'N') , (4, 'Y') /* change context */ USE SourceDB CREATE TABLE dbo.Stark ( Id int PRIMARY KEY , Letter char(1) ) INSERT INTO dbo.Stark (Id, Letter) VALUES (5, 'S') , (6, 'T') , (7, 'A') , (8, 'R') , (9, 'K')
Back in the SourceDB we’ll create the self-signed certificate. The password has to conform to the Password complexity rules.
- The password is at least eight characters long
- Contains at least 3 out of these 4 categories
- Uppercase letter
- Lowercase letter
- Special character
USE SourceDB CREATE CERTIFICATE CrossDb_Cert ENCRYPTION BY PASSWORD = 'Cert_Private_Key' WITH SUBJECT = 'Used for Cross DB access' , EXPIRY_DATE = '99991231'
We can check the existence with this snippet
USE SourceDB SELECT c.name , c.pvt_key_encryption_type_desc , c.thumbprint , c.sid FROM sys.certificates AS c
This is my result
Now we have to export the certificate from the SourceDB and import it into TargetDB. Because I like to source control my script, I prefer TSQL code instead of backing up the certificate to file.
For one way only, the public portion of the certificate will be enough.
Let’s get the binary value
USE SourceDB SELECT CERTENCODED(CERT_ID('CrossDb_Cert')) AS PublicPortionBinary
And copy-paste it into this snippet on the TargetDB.
We’ll go ahead and create the User from the Certificate and grant them
SELECT permissions in one fell swoop.
USE TargetDB CREATE CERTIFICATE CrossDb_Cert FROM BINARY = /* <-- Paste the public binary value here (e.g. 0x308202D0…) */ CREATE USER CrossDb_CertUser FROM CERTIFICATE CrossDb_Cert GRANT SELECT ON dbo.Tony TO CrossDb_CertUser
We can check the creation of the cert and user with this snippet:
USE TargetDB SELECT c.name AS certName , c.pvt_key_encryption_type_desc , c.sid AS certSid , c.thumbprint , dp.name AS UserFromCert FROM sys.certificates AS c JOIN sys.database_principals AS dp ON c.sid = dp.sid
We can also check that the certificate has the same thumbprint
SELECT c.name, c.thumbprint FROM SourceDB.sys.certificates AS c INTERSECT SELECT c.name, c.thumbprint FROM TargetDB.sys.certificates AS c
The only thing remaining is the stored procedure.
USE SourceDB GO CREATE OR ALTER PROCEDURE dbo.ReadFromTargetDB AS BEGIN SELECT * FROM TargetDB.dbo.Tony END
So we will do this the old fashioned way - create Login, User from Login and grant it permissions to execute the stored procedure.
CREATE LOGIN Timmy WITH PASSWORD = 'Password1' GO USE SourceDB CREATE USER Timmy FROM LOGIN Timmy GRANT EXECUTE ON dbo.ReadFromTargetDB TO Timmy
Now we have to open a new session and use SQL Server Authentication to log in as a Timmy.
And run the procedure
USE SourceDB EXEC dbo.ReadFromTargetDB
This returns an error because we have not yet signed the procedure
The server principal "Timmy" is not able to access the database "TargetDB" under the current security context.
Let’s sign it then. Switch to the admin session (not the Timmy session) and run
USE SourceDB ADD SIGNATURE TO dbo.ReadFromTargetDB BY CERTIFICATE CrossDb_Cert WITH PASSWORD = 'Cert_Private_Key' -- the Private key from the self-signed certificate
If it passes, you have confirmation that the password was correct.
Otherwise, you would see this error
An error occurred during decryption.
You can also check with this snippet:
USE SourceDB SELECT c.name AS certName , OBJECT_NAME(cp.major_id) AS objectName , cp.crypt_type_desc FROM sys.certificates AS c LEFT JOIN sys.crypt_properties AS cp ON cp.thumbprint = c.thumbprint
Switch back to the Timmy session and rerun the procedure, which now executes without errors.
I once spent a long time debugging an issue when there wasn’t one.
I was just lazy to create the Login along with the test user and tried to cut corners.
To refresh the terminology:
- Login = Server scoped principal
- User = Database scoped principal
In this test, I’ll impersonate the existing Timmy Login and try to run the procedure.
Then I’ll create a new user without Login, impersonate it and attempt it again.
USE SourceDB EXECUTE AS LOGIN = 'Timmy' /* Impersonating a Server principal */ /* After impersonation */ SELECT SUSER_NAME() AS serverLogin , USER_NAME() AS dbUser , ORIGINAL_LOGIN() AS originalLogin EXEC dbo.ReadFromTargetDB REVERT -- end the impersonation
💡 After impersonation, always check that the
If not, run the
REVERTagain or open a new session.
This works. Now for the user without login:
USE SourceDB CREATE USER NewTimmy WITHOUT LOGIN GRANT EXECUTE ON dbo.ReadFromTargetDB TO NewTimmy EXECUTE AS USER = 'NewTimmy' /* Impersonating a Database principal */ /* After impersonation */ SELECT SUSER_NAME() AS serverLogin , USER_NAME() AS dbUser , ORIGINAL_LOGIN() AS originalLogin EXEC dbo.ReadFromTargetDB REVERT -- end the impersonation
The server principal "S-1-9-3-1136980307-1270705754-1660177281-2973733659" is not able to access the database "TargetDB" under the current security context.
Two-way access 🔗︎
Increase speed, drop down and reverse direction!
So far, we have been executing the procedure from SourceDB and reading from TargetDB. It won’t work the other way around with the current form of the certificate.
USE TargetDB GO CREATE OR ALTER PROCEDURE dbo.ReadFromSourceDB AS BEGIN SELECT * FROM SourceDB.dbo.Stark END GO ADD SIGNATURE TO dbo.ReadFromSourceDB BY CERTIFICATE CrossDb_Cert WITH PASSWORD = 'Cert_Private_Key'
Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.
That’s because the certificate on the TargetDB doesn’t have a private key
USE TargetDB SELECT c.name , c.pvt_key_encryption_type_desc , c.thumbprint , c.sid FROM sys.certificates AS c
To fix this, we’ll do the following steps:
- Delete the user created from the certificate
- Delete the certificate
- Copy both the public and the private keys from the SourceDB cert
- Create a new certificate with a private key in the TargetDB
- Create a user in the SourceDB to grant permissions on the Stark table
- Sign the ReadFromSourceDB procedure with the certificate
Steps 1 and 2 are easy
USE TargetDB DROP USER CrossDb_CertUser DROP CERTIFICATE CrossDb_Cert
To script out the certificate completely, we have to know the original password (Cert_Private_Key).
USE SourceDB SELECT CERTENCODED(CERT_ID('CrossDb_Cert')) AS PublicPortionBinary SELECT CERTPRIVATEKEY ( CERT_ID('CrossDb_Cert') , 'CustomEncryptPwd1' /* Encryption password */ , 'Cert_Private_Key' /* Private key */ ) AS PrivateKeyBinary
The encryption password can be anything conforming to the complexity rules. It will be only used once in the next code snippet.
If you didn’t use the correct password, the function would return
NULL instead of the binary string.
Copy those two values and paste them here to create the cert in TargetDB (now with private key)
USE TargetDB CREATE CERTIFICATE CrossDb_Cert FROM BINARY = /* <-- Paste the public binary value here (e.g. 0x308202D0…) */ WITH PRIVATE KEY ( BINARY = /* <-- Paste the private binary value here (e.g. 0x1EF1B5B0…) */ , DECRYPTION BY PASSWORD = 'CustomEncryptPwd1' /* Decrypt using the same password from the previous step */ , ENCRYPTION BY PASSWORD = 'NewPrivateKeyPwd1' /* You can set the same or new private key password */ )
To showcase something, I’ve changed the original private key from Cert_Private_Key to NewPrivateKeyPwd1.
This is useful if you deploy to different environments and want a known private key for the local environment but a secret private key for the production.
Let’s speedrun the rest of the steps.
Don’t forget to use the new password when signing the procedure in the TargetDB.
USE SourceDB /* Create user from certificate and grant select permission on the table */ CREATE USER CrossDb_CertUser FROM CERTIFICATE CrossDb_Cert GRANT SELECT ON dbo.Stark TO CrossDb_CertUser USE TargetDB /* Sign the procedure */ ADD SIGNATURE TO dbo.ReadFromSourceDB BY CERTIFICATE CrossDb_Cert WITH PASSWORD = 'NewPrivateKeyPwd1' /* new private key in the TargetDB */ /* Create another testing login and user, grant permission on the procedure */ CREATE LOGIN AnotherTimmy WITH PASSWORD = 'Password1' CREATE USER AnotherTimmy FROM LOGIN AnotherTimmy GRANT EXECUTE ON dbo.ReadFromSourceDB TO AnotherTimmy /* Impersonate the new login and exec the procedure */ EXECUTE AS LOGIN = 'AnotherTimmy' EXECUTE dbo.ReadFromSourceDB
That’s it, folks! I hope my future self will thank myself for all the unnecessary details.