Cross DB Access With Module Signing

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

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

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() equals ORIGINAL_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

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'

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:

1. Delete the user created from the certificate
2. Delete the certificate
3. Copy both the public and the private keys from the SourceDB cert
4. Create a new certificate with a private key in the TargetDB
5. Create a user in the SourceDB to grant permissions on the Stark table
6. 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.