May 30, 2013 at 11:16 am
I've inherited some SQL Servers (2008, 2008R2) how can I tell if there is a Service Master Key or any Database keys?
May 30, 2013 at 12:31 pm
Master Keys:
select * from sys.symmetric_keys;
The name of '##MS_DatabaseMasterKey##' or '##MS_ServiceMasterKey##' will tell you if it's a database master or service master key.
Symmetric Keys are stored in the same table and will have their own name.
May 30, 2013 at 12:31 pm
There will always be a service master key by virtue of install. You can back it up or restore it that's about it.
As far as database master keys, if you are using encryption with service broker, or you have any symmetric or asymmetric keys in the database (select * from sys.symmetric_keys etc.) you will have a database master key. You can CREATE, OPEN ALTER, BACKUP, RESTORE and DROP a database master key.
Funny, I never thought about how to look to find it, I am sure it is well hidden in data structures.
The probability of survival is inversely proportional to the angle of arrival.
May 30, 2013 at 12:35 pm
Ed Wagner (5/30/2013)
Master Keys:The name of '##MS_DatabaseMasterKey##' or '##MS_ServiceMasterKey##' will tell you if it's a database master or service master key.
Symmetric Keys are stored in the same table and will have their own name.
Doh okay, that seems obvious now... look in master.sys.symmetric_keys and there is where the service master key is. Thanks.
The probability of survival is inversely proportional to the angle of arrival.
May 30, 2013 at 12:37 pm
No problem. Glad it helped.
May 30, 2013 at 12:40 pm
Thanks, that is helpful. Another question. What I thought I read about the master service key is that it's not created until a database master key is created. It sounds like that's not true that it's created when the sql server is installed. So then should I always backup the service key even if encryption is not in use?
Thanks again,
Terrie
May 30, 2013 at 12:45 pm
I highly recommend backing up your service master key and securing it in a safe place for all your critical servers. If you ever need to restore some production databases to other servers (or move to a new production server) it will make things much easier.
Of course, if you are not using any database encryption whatsoever then it doesn't matter.
The probability of survival is inversely proportional to the angle of arrival.
May 30, 2013 at 12:56 pm
I agree with sturner. Backing up your service master key is always a good idea. Just because encryption isn't being used now doesn't mean it won't be used later.
You are correct in that the Service Master Key is created during the installation of SQL Server.
The Database Master Key is created by using CREATE MASTER KEY and is good for the database.
Using CREATE SYMMETRIC KEY will create symmetric keys.
Personally, when I do this, I not only back up the keys, but I also keep the SQL statements I ran to create them in the first place. This way, I know I can recreate the database master key and symmetric keys with the original password. I do the same for any certificates I create. I had a problem restoring a key once and vowed to myself that it wouldn't happen again. So, I save the original SQL.
From what I understand, you can't get from the encrypted keys back to the key passwords. Someone may have figured out a way to do this, but I have not. Therefore, I just want to be sure I have everything backed up. We don't want to lose access to our own data.
May 30, 2013 at 1:13 pm
Excellent advice regarding the service master key... back it up even if you don;t think you'll need it... it may save you later on.
Saving scripts to recreate all of the other keys is an excellent idea also if you are using any native database encryption & key-management. In our case, we are using external key management mechanism we built for our environment. None of our keys are stored in the databases and we use CLR encryption methods using AES256 encryption. This allows to secure our keys centrally and in a more secure manner as well as encrypt and decrypt external to the database server.
The probability of survival is inversely proportional to the angle of arrival.
May 30, 2013 at 2:23 pm
Thanks for all the advice. Saving the scripts is a really good idea.
Terrie
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply