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

SQL Server Encryption - The Service Master Key

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.

The Service Master Key (SMK) in SQL Server is the root of all encryption operations. It's the key that's used to encrypt all other keys below it, or at least encrypt those keys that are used to encrypt something else.

Surprisingly, there's no CREATE SERVICE MASTER KEY command. There is an ALTER SERVICE MASTER KEY command, which is used to work with this key.

Instead, the Service Master key is automatically created when you enable certain operations. For example, if you create a Database Master Key (DMK), if there is no SMK, it is automatically generated. It is then used to encrypt the DMK on the instance. If you enable Transparent Data Encryption (TDE), then an SMK is created if it does not exist. If you create a linked server, an SMK is needed to encrypt the password.

There are a few other operations as well that create the SMK, but the important thing for you to understand is that this key is at the root of all encryption, and needs to be protected. That means backup of this key, and secure storage. There is a Backup Service Master Key command, and you need to make sure you use this and store a copy of this somewhere that you can get to it in a DR situation. Putting it on the root of the instance host might not be a secure place, and honestly I think you need some secure place that you centralize all of your keys so that they are available along with, but separate from, the backups.

Inside the instance, or on the host, the SMK is protected by the Windows DPAPI mechanism, which should be secure. If it's not, then likely nothing is secure in the computer.

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 Ronald Cartmale on 12 April 2011

Hi Steve,

I am somewhat confused about keys. No about their principle in operation but their effect on existing system operations.

I have created and SQL Job scheduler for SQL Express based on an article ‘Scheduling Jobs in SQL Server Express’ written by Mladen Prajdic and published on SQL Team.com.  My version is working extremely well and I have published it to a couple of production servers.

The only thing lacking is a notification service and I am currently experimenting with another service broker system based on the excellent article ‘Building a Distributed Service Broker Application’ written by Santhi Indukuri and published on this site and although I have got my comms system working fine I am still unsure about the creation of a master key in the master database. Page 3 of the article. Create Master Key Encryption By Password….

None of our production servers uses encryption although we do have a linked server into a legacy DBIII database.

Will creating or alter the master key in the master database have an effect on this system as I understand that SQL server automatically creates a master key when you create a linked server?

Could you advise please.

Posted by Steve Jones on 12 April 2011

Really this is a question for the forums.

From my understanding of the DMK, it doesn't affect operation of the db in any way other than it secures other items inside the database. If you create a DMK, it doesn't affect the database objects since any objects dependent on an DML would already have it. If you alter the DMK, other objects should be re-encrypted. You can use an option with alter master key to force the change in encryption.

You also need to be sure you back up the DMK as that is critical for DR.

Leave a Comment

Please register or log in to leave a comment.