Creating and Migrating an encrypted database.

This is a simple tutorial on how to…

  • Create a database
  • Setting up encryption
  • Add encrypted data to a table
  • Migrate the encrypted database to another server

The tutorial is supplied without warranty and David Bridge and David Bridge Technology Limited accept no responsibility for its use. If you wish to follow the code here then you must do so on a test machine in a controlled and safe environment.

Creation and population

To start with we will create a new database called “EncryptionTest” and add an employee table. This employee table is going to hold sensitive information about each employee’s income. We do not want unauthorised people to read or alter this information. Without encryption the sensitive data would be stored in an integer or string column but for encryption we need to store the data in a binary column.

The size of the binary column will depend on the size of the source data but it’s not a 1 to 1 relationship: You need to experiment to determine the size of the varbinary column (max 8,000 bytes). For the following example varbinary(300) will be way more than enough as it’s just going to store an integer.

CREATE DATABASE [EncryptionTest]
GO

CREATE TABLE [dbo].[Employee](
[id] [int] NULL,
[Name] [varchar](50) NULL,
[Salary] [varbinary](300) NULL
) ON [PRIMARY]
GO

Note that the table does not know that the data within it is encrypted; it’s just basic binary data. We are going to use a function to encrypt the data before we put it in to the binary column. If we simply write data to the column then the data will be stored as binary and can also be read as binary

To put data into the varbinary column we need to create a MASTER encryption key and to do this we need a master password. The following is not a good password but it’s good for an example.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyMasterPassword'

We also need to provide a subject name for the certificate that will be used to encrypt the data. Again, not a good name for a production system.

CREATE CERTIFICATE cert_sk_admin WITH SUBJECT = 'Admin key';

The Master password and the subject for the certificate are very important. If you lose then there will be no way to retrieve data from the database so you need to keep them very safe both from loss and prying eyes. Write them down on a piece of paper and lock them away in a sealed envelope in a secure fireproof safe and treat them like the crown jewels!

We now need to create another symmetric key that will be used to encrypt the data. This key will be encrypted using the certificate that we just created so there is no visible security on this key as the certificate was encrypted and stored in the database. This means that the code we will create later to read, write and update the table will not need to contain any sensitive data, just a reference to the stored certificate: Clever eh?

CREATE SYMMETRIC KEY sk_admin
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE cert_sk_admin;

As I mentioned earlier the table simply contains a binary column that knows nothing about the encryption. To store encrypted data in the column we open the database symmetric key then call a function that will encrypt the data. Then we simply write the encrypted data in to the binary column.

The encryption function encryptbykey takes the GUID of a locally stored symmetric key as its first parameter followed by the data to be encrypted. The guid can be obtained by calling the key_guid() function, passing in the name of the key.

Let’s populate the employee table with encrypted Salary details

open symmetric key sk_admin
decryption by certificate cert_sk_admin;

insert into employee (id, name, salary)
values (1, 'A' , encryptbykey(key_guid('sk_admin'), '50000', 1, '1'))
insert into employee (id, name, salary)
values (2, 'B', encryptbykey(key_guid('sk_admin'), '50000', 1, '2'))
insert into employee (id, name, salary)
values (3, 'C', encryptbykey(key_guid('sk_admin'), '50000', 1, '3'))

And now let’s read the table using a simple select statement that will display the binary data

SELECT *
FROM [EncryptionTest].[dbo].[Employee]

Id Name Salary
1 A 0x0013CFB6E4F55745BFAE11A22DEF9969010000004E20F5770518E0AD4…
2 B 0x0013CFB6E4F55745BFAE11A22DEF996901000000EA49FC95777D6A0A1…
3 C 0x0013CFB6E4F55745BFAE11A22DEF9969010000009E62C1816144EF6CEF…

Although all of the Salaries are the same value (50k), the encrypted data is different for each row stored. This is because each row uses a row specific constant (row authenticator) in the encryption process. In the above case this row constant is the value of id but it could be anything that you either store with the row or that could be stored elsewhere but is specific to the row (i.e. employee NI, payroll number etc.); as long as we can pass the data to the encryptbykey function. The function is using the same symmetric key that we created above to perform the encryption but is performing the encryption with a modifier that is specific to the data being stored. You do not have to use a row authenticator: That magic number 1 in the example is the flag that states we are using one here.

Note that the function call to key_guid is being called for each row. This is not efficient. If updating in a loop or updating several rows like above, it is better to call the function once and store its value.

DECLARE @key uniqueidentifier = key_guid('sk_admin')
insert into employee (
id
, name
, salary
)
values (
1
, 'A'
, encryptbykey(@key, '50000', 1, '1')
)

Reading the encrypted data

To decrypt we simply use a decryption function with the same symmetric key. Note that the decryptbykey function does not require us to specify the key. You have to open the key before you call the function.

OPEN SYMMETRIC KEY sk_admin
DECRYPTION BY CERTIFICATE cert_sk_admin;
SELECT
Id
, Name
, CONVERT(VARCHAR(10),
DECRYPTBYKEY(Salary, 1, CONVERT(VARCHAR(5), id))
) as Salary
FROM Employee

Id            Name    Salary

1              A             50000

2              B             50000

3              C             50000

Now we have encrypted data in a table which we can read, update and add to as long as we have access to the symmetric key and authenticator that was used to encrypt the data, which in turn required that we can access the certificate that is stored in the same database which itself is encrypted by the master key, also stored in the same database.

Migration

Any backup of the database will contain the Keys and the certificate but it is a good idea to back up the keys and certificate manually and store them in a secure location.

Note that you DO NOT have to restore any keys or certificates to the target server when migrating a database which contains encrypted data. The database master key and certificate are contained in the backup file but they are encrypted so they are also useless to another server.

The correct way to migrate a database with encrypted data is to use a temporary migration symmetric key. The following code is going to show you how to do this

NOTE: Very important

Contrary to what you might read on the internet you do not have to drop the service master key on the source database before backing it up. DO NOT DO THAT!

Also you do not have to MIGRATE the service master key to the migration server. In fact this would be a silly thing to do. DO NOT DO THAT EITHER!

Check this before starting…

You may not be able to do the following using a local SQL Server management console connected to a remote source server due to windows permissions regarding file access rights on the source server and therefore might have to RDP to the machine and do it locally.

Overview of migration

SQL can encrypt data as many times as you ask it to using a different method each time and possibly multiple passwords. When SQL tries to read encrypted data it will decrypt the database master key using the service master key by default. If this fails it will try to decrypt it using the SQL Server Service account.

When you open a migrated encrypted database the system will attempt to decrypt the database master key using the service master key on the migration server and this key is likely different to the source server’s key so the decryption will fail. When this happens the migration server will try to open the database master key using the SQL service account. If it is different to the source server (common) then this will also fail. As sql failed to open the database master key then you will get the following message.

Msg 15581, Level 16, State 3, Line 1
Please create a master key in the database or open the master key in the session before performing this operation.

Ignore the “create a master key” bit as this is not the issue (read the “or” bit of the message): The database contains a master key but SQL cannot decrypt it using its service master key (if one exists, which it nearly always does).

Note: You do not need to change any settings for certificates or symmetric keys as these are already in the backup and they are fine. You do need to update the database master key though. Do NOT drop any keys! You just need to open the database master key using the migration password that you used to create it and then drop the encryption by the remote server’s service master key and then re-encrypt by the local server’s service master key.

NOTE: Do not misread that last bit: I did NOT say to drop any key – I said drop the “encryption” of the database master key by the service master key!

Following this simply close all keys (including the master) and then re-open the symmetric key used for querying the data and it will all work fine. This is because SQL is now able to decrypt the certificate and use it.

Note that when SQL decrypts the new database master key successfully with the newly applied service master key then it will also update its SQL Service encryption method too.

Migration process – step by step

If the remote server’s database master key does not have password encryption or you do not know the password but have access to the source server then you will need to add a migration password to the source database and then back it up.

 

Method in English

  1. On the source server
  2. Add password encryption first if not already exists.
  3. Back up the database
  4. Remove the migration password encryption if you created it in step 1a
  5. On the target machine
  6. Restore the database to the target server and select it
  7. Open the database master key using the migration password (1a)
  8. Drop the service master key from the encryption methods
  9. Add the service master key to the encryption methods (this will use local)

Method in Code

At the source server – backup

Add migration password and backup the database

alter master key add encryption by password = 'Migrat10n';
GO

Backup the database

BACKUP DATABASE [DBName]
TO DISK = N'folder\DBfilename.bak' WITH NOFORMAT, NOINIT
,  NAME = N'DBName -Full Database Backup', SKIP, NOREWIND, NOUNLOAD
,  STATS = 10
GO

Migration server restore.

RESTORE DATABASE [DBName]

FROM  DISK = N'folder\filename.bak' WITH  FILE = 1
,  MOVE N'DBName' TO N'folder\filename.mdf'
,  MOVE N'DBName_log' TO N'folder\filename.ldf'
,  NOUNLOAD
,  STATS = 10
GO

Now if you try to read the data you will see it complaining about the master key being missing or closed

open symmetric key sk_admin decryption by certificate cert_sk_admin;

select
Id
, Name
, convert(varchar(10), decryptbykey(Salary, 1, convert(varchar(5), id))) as Salary
from Employee

Msg 15581, Level 16, State 3, Line 1
Please create a master key in the database or open the master key in the session before performing this operation.

This is is because, by default, SQL Server will try to decrypt the key using the local service master key and that key is not capable of doing that. We need to update it.

Remember that when you backed up the data you created a new password encryption of the master key using the password ‘Migrat10n’? You can open the master key with that password.

open master key DECRYPTION BY PASSWORD = 'Migrat10n';

Test this works by running the select statement again after opening the key this way. It should work (but once you close it SQL will revert to trying to open it with the service key again).

You must now drop the encryption by service master key (legacy from the old server) and add it back in again for this server’s master key like this…

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
GO

ALTER MASTER KEY ADD  ENCRYPTION BY SERVICE MASTER KEY
GO

Now test that it works by closing all keys (this also closes the database master key)

close all symmetric keys

then run the select again, first opening the key with the certificate (i.e. without any password)

open symmetric key sk_admin decryption by certificate cert_sk_admin;

select
Id
, Name
, convert(varchar(10), decryptbykey(Salary, 1, convert(varchar(5), id))) as Salary
from Employee

Success!

Id            Name    Salary

1              A             50000

2              B             50000

3              C             50000

Additional recommended backup of certificate (not required)

backup master key to file = N'folder\filename.mkey' encryption by password = 'Migrat10n';
GO


backup certificate cert_sk_Admin
to file = 'folder\filename.cert'
with private key (
file = 'folder\filename.skey'
,encryption by password = ' Migrat10n');
GO

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s