The Last Pickle

I decided to take a look at setting up a SQL Server 2005 Database Mirroring session and once again got carried away with security and ownership. In the past other than maintaining ownership chains I’ve purposely ignored these areas, but in SQL 2005 it seems a lot easier to do the right thing.

The mirroring session I wanted to setup would not be operating in a Windows Domain; so certificates would have to be used for one machine to prove it’s identity to another rather than domain accounts. The first thing I noticed when looking at documentation such as Using Certificates for Database Mirroring was that I was completely confused. What did they mean by Outbound and Inbound connections on an Endpoint? I thought endpoints were only inbound ports into SQL Server, such as the TCP-TSQL endpoint clients connect on. Also the way they were talking about using certificates just seemed all screwy.

However when I took at look at the “Certificates” section of this page on Database Mirroring Transport Security things got a little clearer. Certificates were been used the way I thought they were meant to be…

“For example, consider two server instances, Server_A and Server_B. Server_A uses its private key to encrypt the connection header before sending a connection request to Server_B. Server_B uses the public key of Server_A’s certificate to decrypt the connection header. If the decrypted header is correct, Server_B knows that the header was encrypted by Server_A, and the connection is authenticated. If the decrypted header is incorrect, Server_B knows that the connection request is inauthentic and refuses the connection.”

The certificate attached to the “Outbound” endpoint provides the execution context the connection will use when connecting to an (inbound) endpoint on another server, which authenticates the connection using the public key of the certificate. The public part of the certificate must be pre-shared with the other side of the connection.

I think the language used and the design of this point is little confusing, so I made a nice big drawing to try and understand it. I’ve included the object ownership as well as the execution context. The execution context may not be technically correct but I believe it illustrates the process:

1. When mirroring makes an outbound connection is uses the full certificate to construct a token to identify itself.
2. When the request reaches the other side the token is matched with a certificate public key which maps to a server login which is then used as the execution context for the request. This is the authentication of the request.
3. The permissions of the authenticated identity are then checked to ensure it is allowed to connect to the end point. This is the authorisation of the request.

In the model above:

1. Certificates are owned by a dedicated user. These are database scoped objects that are not in a schema.
2. The Mirroring endpoints are owned by a dedicated certificate mapped login. These are server scoped objects and so cannot belong to a schema.
3. The execution context for the mirroring endpoints is a certificate mapped login which is different to the owner.
4. A pre shared public certificate is used to establish a login on the far side of the mirroring connection that will be the identity of the requesting server.

So now on with the code. I was setting up what used to be called High Available mode, but is now called something like High -Safety Mode with a witness or Synchronous Database Mirroring with a witness. Node 1 will contain the initial primary DB, Node 2 will contain the initial secondary and and Node w will act as the mirroring. The code below is for the things to do on Node 1, with comments about what to do for Node 2 and optionally Node w.

Mirroring is configured at the server level using endpoints, each server instance can have only a single Mirroring Endpoint. However a mirror session runs at the database level; the secondary and then the primary DB are altered to specify the partner in the mirroring session. The secondary DB must be a fully restored but not recovered copy of the primary DB, check the documentation for more information on what’s required.

    -- Mirroring endpoints are server scoped, and the certificates
-- are db scoped (non schema scoped) objects. Certificates used to create
-- logins must be in the master db. To keep things simple I'll use the master
-- to store all objects.
use master;
go

-- This is the scrips run on Node 1 that will contain the initial
-- primary DB, Node 2 will contain the initial secondary DB
-- and Node w is the witness server

-- may need a database master key
select * from sys.symmetric_keys where name = '##MS_DatabaseMasterKey##'

-- if there is not a record create a new db key
create master key encryption by password = 'pword';

-- create a user to own the db scoped certificates
-- the user does not require a login
create user
    cert_owner
without login

-- create a certificate and login to own the mirroring endpoints
-- they are server scoped objects owned by a server login.
-- The certificate is contained in the master DB and so is owned
-- by the cert_owner user.
create certificate
    cert_node_1_mirror_owner
authorization
    cert_owner
with
    subject ='Owner of mirroring objects on node 1';

create login
    node_1_mirror_owner
from
    certificate cert_node_1_mirror_owner;

-- Create the certificate used as the identity of node 1 when mirroring.
-- The identity is used for authorising access to the endpoint and encryption (i think)
-- The certificate will be owned by cert_owner as its a db scoped object
CREATE CERTIFICATE
    cert_node_1_mirror_identity
authorization
    cert_owner
with
    subject = 'Node 1 mirroring identity';

-- create a mirroring partner endpoint on node 1,
-- this will let node 1 act as the primary or secondary db in the mirror session.
-- The first authorisation clause specifies the owner of the endpoint
-- object, in this case the node_1_mirror_owner.
-- The authorisation clause in the data mirroring section
-- specifies a certificate that is used for outbound
-- mirror connections.
-- I've turned off encryption as this is just for dev.

CREATE ENDPOINT
    Mirroring
AUTHORIZATION
    node_1_mirror_owner
STATE=STARTED
AS TCP
    (
        LISTENER_PORT = 5022,
        LISTENER_IP = ALL
    )
FOR
    DATA_MIRRORING
    (
        ROLE = PARTNER,
        AUTHENTICATION = CERTIFICATE cert_node_1_mirror_identity,
        ENCRYPTION = DISABLED
    )

-- backup the public part of the identity certificate as we need to
-- install this on node 2 (and node w) to identify node 1.
backup certificate
    cert_node_1_mirror_identity
to
    file = 'c:\cert_node_1_mirror_identity.cert';

-- now runt the same scripts on node 2
-- just change node_1 for node_2

-- install the public certificate for node 2's mirroring identity into node 1

-- install the public part of the certificate that identifies node 2
create certificate
    cert_node_2_mirror_identity
from
    file = 'c:\cert_node_2_mirror_identity.cert'

-- we need a login mapped to the certificate so we can
-- grant permission for this identity to access the endpoint in node 1
create login
    node_2_mirror_identity
from
    certificate cert_node_2_mirror_identity

-- and give the node 2 identity permission to connect to the node 1
-- mirroring endpoint
grant
    connect
on
    endpoint::Mirroring
to
    node_2_mirror_identity

-- would install the certificate for the witness and grant permission
-- at this time as well

-- look at the endpoints
select
    *
from
    sys.tcp_endpoints

-- NOTE execute the following on node 2, the secondary db, first
-- then on node 1 as primary.
ALTER DATABASE
    mirrored_db
SET
    PARTNER = 'TCP://node2.youdomain.com:5022';

-- to add the witness exec the following
/*
ALTER DATABASE
    mirrored_db
SET
    WITNESS = 'TCP://witness.youdomain.com:5023';
*/

-- to turn off mirroring exec the following
ALTER DATABASE
    mirrored_db
SET
    PARTNER off