Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Backing Up Asymmetric Keys

I've been tech editing a book about SQL Server encryption and this series of blog posts looks at some interesting things I've found when going through the book.

We're DBAs, right? We're concerned with data, with stability, and perhaps most importantly, backing up and restoring our data. We expect to be able to do this, and at a fairly granular level. I can back up a table or a view (through bcp), a stored procedure (scripting), a file group, or a database.

As I was checking facts in the encryption book I stumbled upon this caveat. You can't back up an asymmetric key!

There's not even a backup asymmetric key command in the T-SQL reference!

I was stunned and looked all around to try and figure this out. After all, I can create an asymmetric key from a file. You use some other program to create the key files, like sn.exe from the Visual Studio resource kit, but you apparently can't get this key back out of SQL Server once it's in there.

It is in a database backup and restore, so that's good, but if I wanted to use this key in another database, or perhaps more importantly, I wanted to put the public key in one database, say an order entry system where I take credit cards in and encrypt them with this key. I don't have the private key, but I move that key to a second system, which the order entry people don't have access to. I then transfer the encrypted data between systems and can decrypt it with the private key on the second system.

If I've created my asymmetric keys in SQL Server, I can't easily do this. Can't do it for DR if I need to get data decrypted, just have to be extra sure I have a good database backup and I can use it to decrypt the data.

There's a backup certificate command, a backup master key command, and even a backup service master key command (though strangely no create service master key command), but no backup asymmetric key command.

I think this is an oversight in SQL Server encryption (as is the lack of CREATE SERVICE MASTER KEY) and ought to be corrected. Hopefully it will in the future. In the meantime, I would not recommend you creating asymmetric keys in SQL Server. Instead, create them in Visual Studio and then protect the files.

I've been tech editing a book about SQL Server encryption and this series of blog posts looks at some interesting things I've found when going through the book.

Other posts:

Comments

Posted by BuckWoody on 4 May 2009

Steve - you might want to check here:

www.eggheadcafe.com/conversation.aspx

The database contains what you need. So you back that up.

Posted by Steve Jones on 4 May 2009

That's correct, but what if I want to move one of the keys to another database? I can't. Or not easily.

It's fine for DR, or testing, but there are definitely places where this would be useful. I think the backup asymm key command is missing. We can back up other keys.

Posted by K. Brian Kelley on 4 May 2009

You can't backup symmetric keys, either. The lack of CREATE SERVICE MASTER KEY isn't an oversight. It's generated the first time it is needed. And you have the option of executing an ALTER in order to regenerate it, if need be.

BTW, this is why, if you're using an asymmetric key (general term, not SQL Server specific term) to encrypt the symmetric key, the recommendation is to use a certificate because you can back that up. :)

Posted by Steve Jones on 5 May 2009

It just seems like an oversight that you can't back these up. I know they're in the database, but I'm sure there will be cases where you'd want to have copies of them.

The CREATE SERVICE MASTER seems like an oversight as well. Regardless of whether it's created the first time, why not just put the command in?

Posted by vikas.sahu on 18 June 2012

Hi Steve Jones,

We have to first create database master key(DMK) & this DMK is encrypted by password.

Syntax:CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

My question:

1. Later on where this password is asked for decryption?

2. What is the use of this password?

Because while backup this DMK if we supply different password it is ok.

Posted by Jaat on 10 September 2013

The password is going to be requested when you are trying to restore the master key from the file generated at the backup moment

RESTORE MASTER KEY FROM FILE = 'Path'

DECRYPTION BY PASSWORD = 'password backup'

ENCRYPTION BY PASSWORD = 'password used at the moment of creation'

etc

Leave a Comment

Please register or log in to leave a comment.