Creating a Login with a SID

For those of you who don’t already know, there is a new PASS Virtual Chapter called DBA Fundamentals that has meetings on the first Tuesday of every month. The chapter specializes in providing training and support for new DBAs and also for seasoned DBAs who would just like to brush-up on their skills. I was fortunate enough to be asked to present at their second meeting on 9/4, and I gave my session about SQL Server Service Broker. One of the questions that I was asked during the session was: How do you create a login with a SID, and more specifically, how do you create and find the SID for that login?

In my session on Service Broker and also in my session on AlwaysOn Availability Groups, I mention that if you’re using AlwaysOn Availability Groups or Database Mirroring that you should create your logins with the same SID on each instance/replica so that when a failover occurs, you don’t have to re-map your database users to their appropriate logins. However, I don’t really go into the detail of how to do that in my sessions, so I wanted to take some time to do that here.

use master;

-- Get a new GUID value to use as the SID
declare @SID uniqueidentifier = newid();

-- In order to use the SID in a create login statement, it must be in binary format
declare @SID_Binary varbinary(max) = (select cast(@SID as varbinary(max)));

-- View the SID in GUID and Binary format:
select @SID, @SID_Binary;
-- E72669E3-9FAA-4BCB-8F8F-570EBF114674, 0xE36926E7AA9FCB4B8F8F570EBF114674

-- Here is the statement we really want to run:
--create login SQLDiablo with password='Passw0rd!', sid=0xE36926E7AA9FCB4B8F8F570EBF114674;

-- But that requires us to paste in the SID. There has to be a better way:
declare @UserName nvarchar(128) = 'SQLDiablo', @Password nvarchar(max) = 'Passw0rd!';
declare @Query nvarchar(max) = 'create login ' + @UserName + ' with password=''' + @Password + ''', sid=0x' + cast('' as xml).value('xs:hexBinary(sql:variable("@SID_Binary") )', 'varchar(max)') + ';';

select @Query;
execute sp_executesql @Query;

-- Since varbinary can be a little tricky to work with in dynamic SQL, XPath is our friend.
-- Above we converted the value of @SID_Binary to Hex using XPath's value method (don't forget to add 0x to the beginning of it).

-- Get the SID for the login we just created, as a GUID
select, cast(sp.sid as uniqueidentifier) SID_AS_GUID from sys.server_principals sp where = 'SQLDiablo';

-- SQLDiablo, E72669E3-9FAA-4BCB-8F8F-570EBF114674

set @Query = 'drop login ' + @UserName + ';';
execute sp_executesql @Query;

Service Broker Overview

No Junk Mail

No poison messages either, please.

Service Broker has been a core feature of all editions of SQL Server since SQL 2005. It allows for the asynchronous delivery and processing of messages sent within a database or between databases (either on the same instance or in separate instances). In some ways, you can think of Service Broker like the USPS (in fact, SQL Server even uses Service Broker as part of Database Mail to send email messages). This blog post will use the analogy of sending a letter to a friend (yes, a snail-mail letter…yes, I know that nobody does that anymore… :-P) to give a brief overview of the various components of Service Broker and how they work together, allowing you to send a message from one instance to another instance, where the message will be processed.

Encryption & Security

By default, Service Broker will encrypt messages sent between databases (on the same instance, or on separate instances), and the best way to allow this is to use certificates. However, setting up certificate-based encryption can be a major pain in the butt, and order of operations is extremely important. The first thing you want to do is create and backup the database Master Key on each database/instance. Then, you should create a user in each database/instance with or without a login (Ex: create UserA on InstanceA and UserB on InstanceB). Once you create the users, then create a certificate for each user and back it up. Next, create a user on each database/instance that has the same name as the user on the other instance/database (Ex: create UserB on InstanceA and UserA on InstanceB), and then restore the certificate for each of the users you just made (Ex: Restore UserACert on InstanceB and UserBCert on InstanceA). Once you have the users and certificates setup correctly, then you can proceed with setting up the Service Broker infrastructure.

It is recommended to use database users without logins to restrict the users’ access to just the database that is receiving messages, but if you use a Stored Procedure to automatically process Messages received on a Queue, and if that stored procedure needs to interact with any other database(s) than the database that holds the Queue, then you will need to create a user with a login, and map that login to the other database(s) that the Stored Procedure needs to interact with.

It helps to have a central location where you can store the certificate files so that you can get to them later if you need to add more instances to your Service Broker topology.

-- Example script to setup security on AdamServer
USE Demo;

-- Create the database master key (you should back it up too, but that's not required for this to work)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyReally,Really,Really,ReallyStrongPassword...Really!';

-- You can optionally create a login to use. If you don't need the login, just create the user "WITH NO LOGIN" (instead of "FOR LOGIN AdamUser").
CREATE LOGIN AdamUser WITH PASSWORD = 'SomeOtherReally,Really,ReallyStrongPassword...Really!';

EXEC sp_addrolemember @rolename='db_owner', @membername='AdamUser'; -- Add security as you see fit. You probably shouldn't give away the keys to the house like I did here.

-- Create a certificate and bind it to the database user, then back it up (you must do the backup here because you will use it on the remote server to recreate this user and certificate there).
WITH SUBJECT = 'Certificate for database user AdamUser',
EXPIRY_DATE = '12/31/2099';

TO FILE = 'c:\AdamUser_cert.cer' -- It's probably a good idea to use a secure network share here
PRIVATE KEY (FILE = 'c:\AdamUser_cert.pvk', -- It's probably a good idea to use a secure network share here
ENCRYPTION BY PASSWORD = 'SomeOther,OtherReally,Really,ReallyStrongPassword...Really!');

-- Recreate the remote server's user and certificate on this server (I'm assuming you've already done the steps above on the remote server. The user doesn't need a login.)

EXEC sp_addrolemember @rolename='db_owner', @membername='FriendUser';

FROM FILE = 'c:\FriendUser_cert.cer' -- It's probably a good idea to use a secure network share here
PRIVATE KEY (FILE = 'c:\FriendUser_Cert.pvk', -- It's probably a good idea to use a secure network share here
DECRYPTION BY PASSWORD = 'SomeOther,OtherReally,Really,ReallyStrongPassword...Really!');

Message Types & Contracts

In order to hold a conversation using Service Broker, we first have to specify one or more message types. A Message Type is simply a logical way for you to specify what kind of content will be contained in a message. In this analogy, our message type will be called FriendlyLetter (All messages sent with this type will contain letters sent between friends. We could also create another message type called BusinessLetter that would be letters sent between businesses.) and specifies that all messages sent with this Message Type will be validated as WELL_FORMED_XML. Once we have our Message Type(s) created, then we can create a Contract. In this case, our Contract will be called LetterContract, and it specifies that the parties involved in the conversation are allowed to communicate using the FriendlyLetter Message Type only.

Authorization dbo
Validation = WELL_FORMED_XML;

Authorization dbo
(FriendlyLetter SENT BY ANY);

Queues & Services

In order to send and receive our messages, we need a Queue (or mailbox) to send and receive the messages with. When we create our LetterQueue, we can optionally specify an automated process (stored procedure) that will handle the messages received (in this case, dbo.FriendlyMessageHandler). If you specify an automated process to receive the messages, you should also specify how many concurrent copies of that process can be running (in this case, just 1). If you don’t specify an automated process to receive the messages, then you will need to receive them manually (see the Conversations & Messages section).

Just like in real life, our messages won’t get delivered unless there is a Service to deliver them for us (In real life, this would be the USPS. Unlike in real life, the service is only for a single Queue (whereas the USPS delivers to all mailboxes in the US). In this case, we will call our Service the LetterService, and specify that it will be for our LetterQueue, and that it will be owned by the local user we created in the Encryption & Security section.

STATUS = ON, -- Is the queue enabled to receive messages?
RETENTION = OFF, -- Does the queue keep messages that have been received off the queue, or delete them?
ACTIVATION ( -- The Activation clause is optional and only needs to be specified when automatic message processing is being used
PROCEDURE_NAME = [dbo].[FriendlyMessageHandler], -- The stored procedure that is run when a message is received
MAX_QUEUE_READERS = 1, -- How many times can we run the proc above concurrently?
EXECUTE AS 'AdamUser', -- What user will the proc be executed as?
STATUS = ON -- Is automatic activation turned on or off for this queue?

CREATE SERVICE [//AdamServer/LetterService] -- I recommend naming your services: ///, so that it's easier to tell what server and service you're conversing with
AUTHORIZATION [AdamUser] -- The user that owns this service
ON QUEUE LetterQueue -- What queue is this service for?
([LetterContract]); -- What contract governs conversations over this service?

GRANT SEND ON SERVICE::[//AdamServer/LetterService] to FriendUser; -- Allow the other server's user to send this server messages

Endpoints, Routes, & Remote Service Bindings

Now that we have our Queue and Service created, we need to create a Route and Endpoint so that the Service knows where and how to send messages. We only need one Endpoint per instance, and in this case, our Endpoint will be called SBEndpoint. In order for our Service to know where to send the messages to, we need to specify a Route. In this case, our Route will be called SendLetterRoute, will specify that it is for the receiving server’s LetterService, and will also specify where that service can be found. We also need to create a route in MSDB for receiving messages to our local service, which I’ve called ReceiveLetterRoute.

The last piece of infrastructure we need to create is the Remote Service Binding. This tells SQL Server which user to use when sending messages to the receiving service. Just like with Routes, we need to create one Remote Service Binding for each receiving service we want to send to.



CREATE ROUTE ReceiveLetterRoute
SERVICE_NAME = N'//AdamServer/LetterService',

USE Demo;

CREATE ROUTE SendLetterRoute
SERVICE_NAME = N'//FriendServer/LetterService',
ADDRESS = N'TCP://FriendServer:4022';

TO SERVICE N'//FriendServer/LetterService'
WITH USER = [AdamUser]; -- The user to send the message as

Conversations & Messages

Once all of the infrastructure pieces are created on both the sending and receiving sides, we can begin to hold a conversation/Dialog. When we begin our Dialog, we specify which local service is sending the message and to which remote service it is sending the message. We must also specify the Contract that governs how the two sides may communicate on that Dialog. In this case, our Dialog will be sent from //AdamServer/LetterService to //FriendServer/LetterService on Contract LetterContract. Once the Dialog has been started, we can send messages (of any type allowed by the contract), and the messages will be received in the same order in which they are sent. Since it is possible to send more than one message over a single Dialog, it is recommended not to begin new Dialogs unless absolutely necessary (beginning a new Dialog creates overhead as the two servers authenticate and encrypt a new channel of communication, whereas using an existing Dialog does not incur that overhead).

When the receiving side gets a message, the message is placed in the Queue specified by the receiving Service. If there is an automated process specified to process messages on that queue, then that Stored Procedure will be executed and will process the message. In order to process a message (either automatically or manually), you must Receive the message off of the Queue. In most cases, you will receive one message at a time to make sure that the messages are received and processed in the correct order.

-- Execute on sender:
DECLARE @ConversationHandle uniqueidentifier;
DECLARE @XMLMessage XML = 'Hi!';

BEGIN DIALOG @ConversationHandle
FROM SERVICE [//AdamServer/LetterService]
TO SERVICE N'//FriendServer/LetterService'
ON CONTRACT [LetterContract]

SEND ON CONVERSATION @ConversationHandle
MESSAGE TYPE [FriendlyLetter]
-- Execute on receiver:
declare @ConversationHandle uniqueidentifier, @Service varchar(128), @Contract varchar(128), @MessageType varchar(128), @XMLMessage xml;

@ConversationHandle = conversation_handle,
@Service = service_name,
@Contract = service_contract_name,
@MessageType = message_type_name,
@XMLMessage = CAST(message_body AS XML)
FROM LetterQueue
), TIMEOUT 5000; -- Wait 5 seconds

Bringing It All Together

Service Broker Diagram

Click the image to see it full size in a new window