Steve Jones (Blog|Twitter) asked a question on the #sqlhelp hash tag tonight that I found interesting so I thought I’d play around with Powershell for a few minutes and see if I could beat Aaron Nelson (Blog|Twitter) to the punch with a Powershell answer for once.  Steve’s question was:

@wayoutwest: “Anyone have a PSH script that can add a login, run sp_helprevlogin, pipe the output as a new batch to a 2nd server? #sqlhelp #powershell”

Initially I thought, well that should be easy enough, but then I realized, if you are creating a new login, you don’t really need sp_helprevlogin to create the login on the second server, you just need the new login SID from the first server.  We already have the new login name and password, the only thing sp_helprevlogin gives us that we don’t already have in this case is the SID for the new login.  Ostensibly, the intent behind Steve’s request is to have the login created on both servers with the same SID so that a restore of the database from the first server to the second server doesn’t result in orphaned database users.  A good place for this would be database mirroring where the logins are created on the principle server and the mirror needs an identical login with the same SID for the application to work correctly after a failover occurs.

We can leverage SMO to do this with Powershell by loading the Microsoft.SqlServer.SMO assembly into our environment. 

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

Then we create and set a number of strings for the principle and mirror server names, and the new login’s name and password. 

[string]$PrincipleServer = ".\2008R2Lab1";
[string]$MirrorServer = ".\2008R2Lab2";
[string]$LoginName = "TestLogin";
[string]$Password = "pass@word1";

Then we create a Server object to both of the SQL instances:

# Create SMO Connections to both SQL Instances
$PriSvr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $PrincipleServer
$MirSvr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $MirrorServer

For development and testing purposes we can check the principle server to determine if the login already exists and if so drop it before we create it:

# Check if the login exists on the Principle server and Drop if it does
if ($PriSvr.Logins.Contains($LoginName))
{ $PriSvr.Logins[$LoginName].Drop(); }

To create the new login, we create a new Login object passing the Server object for the principle server and the new logins name into the object constructor.  Then we set the LoginType for the new login object to be a SQL Login and finally we call Create() on the object and pass in the password for the new login to be created.

# Create a new login for the Principle Server
$NewLogin = New-Object ('Microsoft.SqlServer.Management.Smo.Login') $PriSvr, $LoginName
# Specify that this is a SQL Login
$NewLogin.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin;
# Create the login on the Principle Server
$NewLogin.Create($Password);

After creating our login, we will need to refresh the Logins Collection on the principle server to get the new login back with its SID, and then we can grab the login into a local variable for easier access later.

# Refresh the login collection to get the login back with SID
$PriSvr.Logins.Refresh();
# Get a Login object for the Principle Server Login we just created
$PriLogin = $PriSvr.Logins[$LoginName]

Same as before, on the Mirror Server we can check if the new login exists and drop it if it does for development and testing purposes:

# Check if the login exists on the Mirror server and Drop if it does
if ($MirSvr.Logins.Contains($LoginName))
{    $MirSvr.Logins[$LoginName].Drop(); }

To create the new login on the mirror server, we follow all the same steps as we did on the principle, except we add one additional step to call set_SID() and provide the SID from the login on the principle server, in this case using the $PriLogin variable we stored the login in for easier access.  Once we set the SID we can call Create() for the login and pass in the password to create the login with.

# Create a new login for the Mirror Server
$NewLogin = New-Object ('Microsoft.SqlServer.Management.Smo.Login') $MirSvr, $LoginName
# Specify that this is a SQL Login
$NewLogin.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin;
# Assign the SID to this login from the Principle Server Login
$NewLogin.set_Sid($PriLogin.get_Sid());
# Create the Login on the Mirror Server
$NewLogin.Create($Password);

To validate that this all worked as we expected it to, we can open up SSMS and create a new query window that uses SQLCMD mode, allowing us to connect to both instances and query the sys.server_principals DMV for the TestLogin login to validate we have the same SID.

:CONNECT .\Lab1
SELECT *
FROM sys.server_principals
WHERE name = 'TestLogin';
GO
:CONNECT .\Lab2
SELECT *
FROM sys.server_principals
WHERE name = 'TestLogin';
GO

As you can see in the above image, the SID’s are identical which means that the database users will properly map to their server login in the event of a restore from one instance to another, or a database failover when using database mirroring.

The complete script for this is attached to this blog post.

Create Mirrored Login.ps1 (2.13 kb)

Yesterday I received an email asking me a question related to my Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail on SQL Server Central that intrigued me enough that I had to put a couple of minutes into actually figuring out what the problem the being encountered was.  In the article, I show how to create a stored procedure in a user database that can be used to call sp_send_dbmail without the calling user being in the DatabaseMailUserRole role in msdb, and there are actually a number of uses for this implementation beyond the example that is provided in the article.  I use this same method to allow Service Broker activation procedures to send me emails through Database Mail all the time, and there are numerous other cases where you might need to create a stored procedure in one database that executes code in a separate database, where the calling user should not have access to the separate database. 

I don’t know the exact use case for the problem that was posed in the email question, but the basic tenet of the question was that there was a stored procedure in one database that needed to be called from multiple databases and the intent was to use the same certificate to sign stored procedures in multiple databases to call this centralized stored procedure without giving the users access to the central database.  Is that confusing to you?  I confused me initially when I read the email, and it took a second pass for me to actually understand the problem being encountered and then build out a repro of the issue to begin looking at what the problem might be.  To keep things simple and non-confusing if that is possible, lets first create two databases, two procedures and build out the basic framework from the article for two user stored procedures.

-- Create the calling database

CREATE DATABASE a;

GO

-- Create the target database

CREATE DATABASE b;

GO

-- Switch to the target database

USE b

GO

-- Create a table and insert 10 rows into the table

CREATE TABLE SomeTable (RowID int identity primary key)

GO

INSERT INTO SomeTable DEFAULT VALUES

GO 10

-- Create the target stored procedure that selects from the table

-- in the target database.

CREATE PROCEDURE Select_SomeTable

AS

SELECT * FROM SomeTable

GO

-- Switch to the calling database

USE a

GO

-- Create a stored procedure that calls the stored procedure

-- in the target database

CREATE PROCEDURE ExecuteDatabaseB_Select_SomeTable

WITH EXECUTE AS OWNER

AS

EXECUTE b.dbo.Select_SomeTable

GO

-- Create a certificate to sign the calling stored procedures with

CREATE CERTIFICATE [SigningCertificate]

ENCRYPTION BY PASSWORD = '$tr0ngp@$$w0rd'

WITH SUBJECT = 'Certificate for signing a Stored Procedure';

GO

-- Backup certificate so it can be create in master database

BACKUP CERTIFICATE [SigningCertificate]

TO FILE = 'c:\SQLskills\SigningCertificate.cer';

GO

-- Add Certificate to Master Database

USE [master]

GO

CREATE CERTIFICATE [SigningCertificate]

FROM FILE = 'c:\SQLskills\SigningCertificate.cer';

GO

-- Create a login from the certificate

CREATE LOGIN [SigningLogin]

FROM CERTIFICATE [SigningCertificate];

GO

-- The Login must have Authenticate Sever access

-- per http://msdn.microsoft.com/en-us/library/ms190785.aspx

GRANT AUTHENTICATE SERVER TO [SigningLogin]

GO

-- Create a user in database b for the Login

USE b

GO

CREATE USER [SigningLogin] FROM LOGIN [SigningLogin]

GO

-- Grant EXECUTE on the target stored procedure to the 

-- certificate based login

GRANT EXECUTE ON [dbo].[Select_SomeTable] TO [SigningLogin]

GO

-- Switch to the calling database

USE a

GO

-- Sign the procedure with the certificate's private key

ADD SIGNATURE TO OBJECT::[ExecuteDatabaseB_Select_SomeTable]

BY CERTIFICATE [SigningCertificate] 

WITH PASSWORD = '$tr0ngp@$$w0rd';

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

USE master;

GO

CREATE LOGIN [testuser] WITH PASSWORD=N't3stp@$$', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

-- Create the test user for the test login in the calling database and

-- grant it EXECUTE privileges on the calling stored procedure

USE [a]

GO

CREATE USER [testuser] FOR LOGIN [testuser]

GO

GRANT EXECUTE ON [dbo].[ExecuteDatabaseB_Select_SomeTable] TO [testuser]

GO

-- Switch to the test login context

EXECUTE AS LOGIN = 'testuser'

EXECUTE a.dbo.ExecuteDatabaseB_Select_SomeTable

GO

-- Revert the context back to our sysadmin login

REVERT

GO

In the above code, database “a” has a stored procedure that calls a stored procedure in database “b” and the “testuser” login only has access to database “a” and EXECUTE privileges on the stored procedure in database “a”.  The certificate from database “a” is backed up and created in the “master” database so that a certificate login can be created from the certificate which has the EXECUTE privilege on the stored procedure in database “b”.  The “testuser” login does not have access to database “b” or any of the objects inside of the database but because the stored procedure in database “a” is signed by the certificate and a login has been created in master from the certificate that has access to EXECUTE the stored procedure in database “b”, the “testuser” login can execute the stored procedure in database “a” and get access to the information in database “b” without having to have explicit access to database “b” or the stored procedure contained in that database.

At this point everything is essentially in line with the information contained in the SQL Server Central article I wrote, and it works exactly as shown in the article, even though it is all being applied to user databases and objects.  Now let’s try to extend this functionality to a third database as a caller of the stored procedure in database “b” by creating a new database, the same stored procedure that was created in database “a”, and then creating the certificate in the new database from the previous backup from database “a” and signing the stored procedure using the same certificate.

-- Create a third database to create another calling database

-- and stored procedure to the target database and stored procedure

CREATE DATABASE c;

GO

-- Create the calling stored procedure in the third database

USE c

GO

CREATE PROCEDURE ExecuteDatabaseB_Select_SomeTable

WITH EXECUTE AS OWNER

AS

EXECUTE b.dbo.Select_SomeTable

GO

-- Create the certificate from in the third database from the 

-- previous certificate backup to allow signing of the procedure

CREATE CERTIFICATE [SigningCertificate]

FROM FILE = 'c:\SQLskills\SigningCertificate.cer';

GO

-- Sign the procedure with the certificate's private key

ADD SIGNATURE TO OBJECT::[ExecuteDatabaseB_Select_SomeTable]

BY CERTIFICATE [SigningCertificate] 

WITH PASSWORD = '$tr0ngp@$$w0rd';

GO

This all works great, right up to the point that we try to sign the stored procedure using the certificate we created from our backup from database “a”.  When we try to sign the stored procedure in database “c” we get the following error back from SQL Server:

Msg 15556, Level 16, State 1, Line 2
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.

So what exactly happened here, the process worked the first time, but now we can’t duplicate it for additional databases that need to call this stored procedure?  The problem is that when we backed up the certificate from database “a” we only backed up the certificate, we didn’t backup the certificate’s private key to a separate file, so we can’t use this same certificate in additional databases to sign stored procedures unless we go back and backup the private key from the certificate as well.

To fix the problem, we first have to drop the stored procedure in database “c” and then drop the certificate without its private key from the database as well.  Then we will need to take a new backup of the certificate from database “a” and specify the WITH PRIVATE KEY clause to backup the private key for the certificate to a separate private key file so that we can recreate the certificate in database “c” with the correct private key to sign the stored procedure.  Once we have done this, we can recreate our stored procedure and sign it with the certificate, then create the database user for the “testuser” login and grant the database user the EXECUTE privilege on the stored procedure in database “c”, and test the configuration by executing the stored procedure in database “c” to ensure that we get the results from database “b”.

-- Fix the problem

USE [c]

GO

-- Drop the calling procedure with the invalid signature

-- Create the calling stored procedure in the third database

DROP PROCEDURE ExecuteDatabaseB_Select_SomeTable

GO

-- Drop the certificate without the private key

DROP CERTIFICATE [SigningCertificate]

GO

-- Backup the certificate with its private key so it can be used in

-- additional databases to sign stored procedures calling the target

-- stored procedure.

USE a

GO

BACKUP CERTIFICATE [SigningCertificate]

TO FILE = 'c:\SQLskills\SigningCertificate_WithKey.cer'

WITH PRIVATE KEY (

FILE = 'c:\SQLskills\SigningCertificate_WithKey.pvk',

ENCRYPTION BY PASSWORD = '$tr0ngp@$$w0rd',

DECRYPTION BY PASSWORD = '$tr0ngp@$$w0rd');

GO

-- Create the certificate in our new database with the private key

-- from the new certificate backup with the private key.

USE c

GO

CREATE CERTIFICATE [SigningCertificate]

FROM FILE = 'c:\SQLskills\SigningCertificate_WithKey.cer'

WITH PRIVATE KEY (

FILE = 'c:\SQLskills\SigningCertificate_WithKey.pvk',

ENCRYPTION BY PASSWORD = '$tr0ngp@$$w0rd',

DECRYPTION BY PASSWORD = '$tr0ngp@$$w0rd');

GO

-- Recreate the calling stored procedure in the third database

CREATE PROCEDURE ExecuteDatabaseB_Select_SomeTable

WITH EXECUTE AS OWNER

AS

EXECUTE b.dbo.Select_SomeTable

GO

-- Sign the procedure with the certificate's private key

ADD SIGNATURE TO OBJECT::[ExecuteDatabaseB_Select_SomeTable]

BY CERTIFICATE [SigningCertificate] 

WITH PASSWORD = '$tr0ngp@$$w0rd';

GO

-- Create the database user for the testuser login and grant it

-- the EXECUTE privilege on the calling stored procedure

CREATE USER [testuser] FOR LOGIN [testuser]

GO

GRANT EXECUTE ON [dbo].[ExecuteDatabaseB_Select_SomeTable] TO [testuser]

GO

-- Switch to the test login context

EXECUTE AS LOGIN = 'testuser'

EXECUTE c.dbo.ExecuteDatabaseB_Select_SomeTable

GO

-- Revert the context back to our sysadmin login

REVERT

GO

This all works as expected and we’ve resolved our problem.  This was a really interesting problem because I had never actually anticipated that someone would use the same certificate in multiple databases to sign multiple stored procedures that perform cross database operations without granting the calling users access to the target database.  This repro shows that this is technically possible if you backup the private key for the certificate and restore the private key in the additional databases with the certificate.

See you on the playground!