Foreword
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.
Components
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.
Simplified version:
- The password is at least eight characters long
- Contains at least 3 out of these 4 categories
- Uppercase letter
- Lowercase letter
- Number
- 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
Testing
Typically, I would create a User without login and impersonate it, but impersonating just the User wouldn’t work - you would have to impersonate the Login.
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.
Impersonation
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
SUSER_NAME()
equalsORIGINAL_LOGIN()
If not, run the
REVERT
again 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!
— Lrrr
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.