June 13, 2017 at 2:46 pm
Hi All,
I'm rusty with admin and definitely new to master keys. I'm working on a database that has a service master key, called ##MS_ServiceMasterKey##, which I am assuming was created when linked server credentials were added, and probably has default everything. I do see there are also credentials we use for running SQL Server Agent jobs, but we don't have any column-level encryption or other data encryption.
The problem is that now we want to start encrypting databases, and I'm reading about encrypting and figure I need to keep track of the SMK encryption methods. Is there any way I can force a new password on this key? Or should I drop and recreate it? If I drop and recreate, what should I be aware that I need to rebuild?
Thanks for your help,
The developer who now gets to do everything.
June 13, 2017 at 4:03 pm
gdpollock - Tuesday, June 13, 2017 2:46 PMHi All,I'm rusty with admin and definitely new to master keys. I'm working on a database that has a service master key, called ##MS_ServiceMasterKey##, which I am assuming was created when linked server credentials were added, and probably has default everything. I do see there are also credentials we use for running SQL Server Agent jobs, but we don't have any column-level encryption or other data encryption.
The problem is that now we want to start encrypting databases, and I'm reading about encrypting and figure I need to keep track of the SMK encryption methods. Is there any way I can force a new password on this key? Or should I drop and recreate it? If I drop and recreate, what should I be aware that I need to rebuild?Thanks for your help,
The developer who now gets to do everything.
The master database is where you would find ##MS_ServiceMasterKey##.
You would want to back up the key (you use a password for the backup) and then store the backup offsite in a secure location and anywhere else you need for DR and as long as it hasn't been compromised, leave it alone. The SMK is the base or starting point for all other encryption. Other than backing it up and the password for the file, there really isn't anything you would need to track with it.
Information on the Service Master Key:
Service Master Key
Information on the Encryption Hierarchy:
Encryption Hierarchy
Sue
June 14, 2017 at 5:58 am
gdpollock - Tuesday, June 13, 2017 2:46 PMHi All,I'm rusty with admin and definitely new to master keys. I'm working on a database that has a service master key, called ##MS_ServiceMasterKey##, which I am assuming was created when linked server credentials were added, and probably has default everything. I do see there are also credentials we use for running SQL Server Agent jobs, but we don't have any column-level encryption or other data encryption.
The problem is that now we want to start encrypting databases, and I'm reading about encrypting and figure I need to keep track of the SMK encryption methods. Is there any way I can force a new password on this key? Or should I drop and recreate it? If I drop and recreate, what should I be aware that I need to rebuild?Thanks for your help,
The developer who now gets to do everything.
do not touch the SMK, you do not need too. The SQL Sedrver instance handles this.
Do you plan to use TDE?
Please check my encryption articles at this link and this link
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 14, 2017 at 6:05 am
Thank you for your feedback. Yes, I am planning on implementing TDE.
What I'm hearing is that even though I do not know the original password for the SMK, as long as I backup and am ready to restore, I'll never need that password again? Would there ever be a situation where I would need to know the SMK password?
I'm guessing this SMK was created automatically when a linked server was added, so I don't know if a default was used.
June 14, 2017 at 6:50 am
gdpollock - Wednesday, June 14, 2017 6:05 AMThank you for your feedback. Yes, I am planning on implementing TDE.
What I'm hearing is that even though I do not know the original password for the SMK, as long as I backup and am ready to restore, I'll never need that password again? Would there ever be a situation where I would need to know the SMK password?
I'm guessing this SMK was created automatically when a linked server was added, so I don't know if a default was used.
Have you read my articles i linked above??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 14, 2017 at 7:05 am
gdpollock - Wednesday, June 14, 2017 6:05 AMThank you for your feedback. Yes, I am planning on implementing TDE.
What I'm hearing is that even though I do not know the original password for the SMK, as long as I backup and am ready to restore, I'll never need that password again? Would there ever be a situation where I would need to know the SMK password?
I'm guessing this SMK was created automatically when a linked server was added, so I don't know if a default was used.
You wouldn't and won't have any password for it. That's the point. There is no password stored with it that you need - it's not user generated and it doesn't really work where a "default" is used. You'd only have a password for the backup file of the key.
Sue
June 14, 2017 at 7:18 am
Thank you both, Sue and Perry. I appreciate your patience with me. I'm working through the articles provided, as well as the Microsoft article on TDE and keys/certificates.
One final question, if you'll indulge me? On my dev server, I was following the Microsoft guide to create a certificate, but I wasn't able to because the SMK wasn't open. I couldn't figure out how to get the default key to open, since all the commands I see on google/guides say I need to use a password.
I made a shot in the dark and created a new master key using the below. Looking in the database I don't see an additional key, and I clearly saw one before. Did I override the original SMK, or just create a secondary DMK in master?
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = <pass>
GO
June 14, 2017 at 7:29 am
gdpollock - Wednesday, June 14, 2017 7:18 AMThank you both, Sue and Perry. I appreciate your patience with me. I'm working through the articles provided, as well as the Microsoft article on TDE and keys/certificates.
One final question, if you'll indulge me? On my dev server, I was following the Microsoft guide to create a certificate, but I wasn't able to because the SMK wasn't open. I couldn't figure out how to get the default key to open, since all the commands I see on google/guides say I need to use a password.
I made a shot in the dark and created a new master key using the below. Looking in the database I don't see an additional key, and I clearly saw one before. Did I override the original SMK, or just create a secondary DMK in master?
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = <pass>
GO
Neither - you created a database master key. If I follow the same steps you use, I then have two keys in the master database. The service master key and a database master key. Both are seen when I query sys.symmetric_keys
Sue
June 14, 2017 at 7:32 am
What was the exact error you received
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 14, 2017 at 7:45 am
Perry Whittle - Wednesday, June 14, 2017 7:32 AMWhat was the exact error you received
At first, I ran the below command to create the certificate, and I just realized I caught something.CREATE CERTIFICATE myTDECert WITH SUBJECT = 'Server TDE Certificate'
Msg 15581, Level 16, State 1, Line 1
Please create a master key in the database or open the master key in the session before performing this operation.
At first I thought it meant I needed to open the SMK, but re-reading this, and with Sue pointing out the sys table, I see that I needed a DMK in the master database before creating any certificates. I thought I used the SMK to create a certificate, so that was the confusion.
From this, and your guide, I'm seeing that the SMK is automatically created by installing SQL Server, and is unique to the installation; you typically don't use a password at the SMK level, and the SMK is what everything else is built on. The only way I could move the SMK from one server to another is to use a backup of the SMK, which Sue described. Am I warm?
If that's the case, then all the encryption relies on DMKs, which is where I encrypt with passwords and certificates?
June 14, 2017 at 7:58 am
gdpollock - Wednesday, June 14, 2017 7:45 AMAt first, I ran the below command to create the certificate, and I just realized I caught something.CREATE CERTIFICATE myTDECert WITH SUBJECT = 'Server TDE Certificate'
That command will try to create a certificate that will use the DMK to encrypt its private key.
If the DMK does not exist (i.e. you haven't created one) or you created one and then altered the DMk to drop encryption by the SMK then you will receive the error
Msg 15581, Level 16, State 1, Line 1
Please create a master key in the database or open the master key in the session before performing this operation.
gdpollock - Wednesday, June 14, 2017 7:45 AM
The only way I could move the SMK from one server to another is to use a backup of the SMK, which Sue described. Am I warm?
No, icelandic, subzero :Whistling:
As my arcticle states do not move the SMK :Whistling:
gdpollock - Wednesday, June 14, 2017 7:45 AMIf that's the case, then all the encryption relies on DMKs, which is where I encrypt with passwords and certificates?
The DMK and the SMK are instance specific, they are only used to protect the private key of any asymmetric keys that reside on the instance. Do not move them across instances
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 14, 2017 at 8:07 am
gdpollock - Wednesday, June 14, 2017 7:45 AMThe only way I could move the SMK from one server to another is to use a backup of the SMK, which Sue described. Am I warm?If that's the case, then all the encryption relies on DMKs, which is where I encrypt with passwords and certificates?
You don't. You back it up for disaster recovery, emergency purposes and not to move it around. You back it up and then just leave it alone. I think both Perry and I have mentioned that at least once so you probably want to find a way to get your head away from that notion of moving or replacing the SMK.
Encryption works on a hierarchy. Walk through Perry's article here to get a better understanding - he includes the hierarchy image from BOL that you will need to understand and walks through how they are used while explaining what is in that image:
SMKs, DMKs, Certificates for TDE and Encrypted Backups
Sue
June 14, 2017 at 8:14 am
Thanks for your help. Before I burn you both out, let me do some more research and wrap my head around the keys and how they're related to TDE. Though you did answer my original question--don't drop the SMK.
June 14, 2017 at 8:22 am
gdpollock - Wednesday, June 14, 2017 8:14 AMdon't drop the SMK.
You can't drop it!
You can back it up, restore it and alter it
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 14, 2017 at 8:29 am
Perry Whittle - Wednesday, June 14, 2017 8:22 AMgdpollock - Wednesday, June 14, 2017 8:14 AMdon't drop the SMK.You can't drop it!
You can back it up, restore it and alter it
🙁 would you believe me if I actually originally typed "don't drop it because you can't?".
By the way, I just finished your article about SMKs, DMKs, and Certificates. Thank you for it--it helped with some of the questions I originally had.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply