How to: Decrypt SQL 2005/2008 database master keys on other servers

SQL 2005 and 2008 both have what’s referred to as an encryption hierarchy. The details of this are beyond the scope of this post, but in essence: we encrypt our data using a key. We need to protect our key somehow, and we don’t want to litter our stored procs with key passwords, so we use a certificate. We then protect the certificate with a database master key. This is in turn protected by the service master key which is finally protected by DPAPI, an operating system provided store.

The keys and certificates are stored within the database itself, but when we move the database to another server they can’t be accessed. This is because the new server doesn’t know how to decrypt the database master key and in turn can’t decrypt the keys and certificates we need to use.

Usually…

The database master key is always protected by a password. You would have had to provide this when you first created the key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mypassword'

When you create the key like this, SQL generates the key value and encrypts it with the supplied password before storing it. It also makes a second copy which is encrypted using the service master key, and this is the copy that is normally used. When you move your database to another server, the service master key protected copy can’t be loaded but the password protected copy can be.

 OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword' 

With the key now decrypted and loaded into memory, we can ask the new server to make a copy that is protected using the service master key.

 ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY 

Finally, we close the key to take it out of memory.

 CLOSE MASTER KEY 

Voila, you can now access your keys and certificates on the new server with automatic key management (that is, with SQL automatically opening and closing the database master key for you as required).

Read-only databases

The approach described above is dependent upon the database being in a writable state as it makes modifications of the database master key itself. What happens when we want to work with a read-only version of a database such as a snapshot or a mirror?

With automatic key management, SQL Server will first attempt to decrypt the database master key using the service master key. In a read-only database, we are unable to create a copy of the key that is protected in this way.

After attempting that, SQL Server will look in the credential store (sys.credentials) for any credentials related to the master key. It will attempt each credential it finds.

Adding our credential to the store is easy:

USE [master]
GO

EXEC sp_control_dbmasterkey_password
    @db_name   = N'mydatabase',
    @password  = N'mypassword',
    @action    = N'add';

You can see evidence of the new credential in both sys.master_key_passwords and sys.credentials:

SELECT  d.name as database_name,
        c.*,
        mkp.family_guid

FROM    master.sys.credentials c

        INNER JOIN master.sys.master_key_passwords mkp
            ON c.credential_id = mkp.credential_id

        INNER JOIN master.sys.database_recovery_status drs
            ON mkp.family_guid = drs.family_guid

        INNER JOIN master.sys.databases d
            ON drs.database_id = d.database_id

Voila, just like above, you can now access your keys and certificates on the new server with automatic key management (that is, with SQL automatically opening and closing the database master key for you as required).

You might also notice that the sys.master_key_passwords view ties a master key password to a family id as opposed to a database id. A family id is assigned when a database is first created and it stays the same even if the database is detached, moved, reattached, mirrored, etc. As a result of this behavior, you could have multiple databases on the one server that share the same family id. In contrast, a database id is created every time a database is attached and is therefore unique for every database instance on the server. In the context of our master keys, the outcome is that adding a credential against one database will actually add it for all of the databases which have come from the same original instance. Even if you detach and reattach your databases, or drop and restart replication, the credential will still be kept in the store and work with the new database instance. Attaching any new instances which share the same family id will also automatically inherit the credential. (Unless of course you change the database master key password in which case the credential will still be attempted but will fail and cause the next one to be attempted instead.)

Geographic Proximity Searches in SQL 2005

Disclaimer: I don’t actually know anything about SQL performance. The techniques described below have been gleaned from other sources, seem to work for me, and it makes sense why they would. If you know more about SQL, please correct me. 🙂

Talking to Darren around Tech.Ed today, he expressed a need for a way to do proximity searches in SQL. These are queries along the lines of “give me all the records that talk about locations within 50km of X”. Now, in SQL 2008 this is baked into the product, but for SQL 2005 and below we need to do the maths ourselves.

The general query is relatively simple: calculate the distance-from-X for each row, then filter by the distance. Performing 6 trigonometric operations for every row, on every query is a pretty sure way to kill your average database though.

Instead, we add some persisted calculated columns to our table like so:

CREATE TABLE [dbo].[Locations]
(
    [LocationID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_Locations_LocationID] DEFAULT (newid()),
    [Title] [varchar](100) NOT NULL,
    [Latitude] [decimal](9, 6) NOT NULL,
    [Longitude] [decimal](9, 6) NOT NULL,
    [ProximityX]  AS (cos(radians([Latitude]))*cos(radians([Longitude]))) PERSISTED,
    [ProximityY]  AS (cos(radians([Latitude]))*sin(radians([Longitude]))) PERSISTED,
    [ProximityZ]  AS (sin(radians([Latitude]))) PERSISTED,

    CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED
    (
        [LocationID] ASC
    ) WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Basically, we take the reusable parts of our calculation and store them between queries. Storing these precalculated components means that our comparisons can be done using relatively simple maths and only one trig function per row.

Because they are persisted calculated columns, they are only calculated when the row is created or updated. SQL manages this for us.

Finally, here’s a stored procedure to query that table:

CREATE PROCEDURE [dbo].[FindLocationsNearby]
   @CenterLat float,
   @CenterLon float,
   @SearchRadius float
AS
BEGIN
    –Store the radius of the earth (in km so that we can search by km)
    declare @EarthRadius float
    set @EarthRadius = 6378.14

    –Calculate the X, Y and Z axis values for the center point
    declare @CenterX float
    declare @CenterY float
    declare @CenterZ float
    set @CenterX = cos(radians(@CenterLat)) * cos(radians(@CenterLon))
    set @CenterY = cos(radians(@CenterLat)) * sin(radians(@CenterLon))
    set @CenterZ = sin(radians(@CenterLat))

    –Perform the database search
    SELECT    Title,
            Distance = @EarthRadius * acos(ProximityX * @CenterX + ProximityY * @CenterY + ProximityZ * @CenterZ)
    FROM    Locations
    WHERE    @EarthRadius * acos(ProximityX * @CenterX + ProximityY * @CenterY + ProximityZ * @CenterZ) <= @SearchRadius
    ORDER BY Distance ASC
END
GO

This technique was extracted from an Access/SQL 2000 article I found on MSDN.