Service Master Key

Question

Service Master Key

VastSQL

SSC-Dedicated

Points: 36097
More actions
August 21, 2018 at 10:40 am

#378376

Hi Experts,
As per my limited knowledge the SMK is created when SQL Service is first started and all SQL Server instance will have SMK .
Why we need to backup SMK?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

VastSQL - Tuesday, August 21, 2018 10:40 AM
Hi Experts,
As per my limited knowledge the SMK is created when SQL Service is first started and all SQL Server instance will have SMK .
Why we need to backup SMK?

Because it's used in securing all of the other keys.

Sue

Thom A SSC Guru Points: 99130 More actions · Answer 2

It's worth noting that if you've upgraded your instance to 2017 (from 2016 or prior), Microsoft also recommend you regenerate your Service Master Key. To quote the documentation:

SQL Server 2017 uses the AES encryption algorithm to protect the service master key (SMK) and the database master key (DMK). AES is a newer encryption algorithm than 3DES used in earlier versions. After upgrading an instance of the Database Engine to SQL Server 2017 the SMK and DMK should be regenerated in order to upgrade the master keys to AES.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 3

Sue_H - Tuesday, August 21, 2018 10:52 AM
VastSQL - Tuesday, August 21, 2018 10:40 AM
Hi Experts,
As per my limited knowledge the SMK is created when SQL Service is first started and all SQL Server instance will have SMK .
Why we need to backup SMK?
Because it's used in securing all of the other keys.
Sue

Thanks Sue, when we will be using the backup of SMK ,each instance will have its own SMK right, then whats the use of backup?

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 4

Thom A - Tuesday, August 21, 2018 1:20 PM
It's worth noting that if you've upgraded your instance to 2017 (from 2016 or prior), Microsoft also recommend you regenerate your Service Master Key. To quote the documentation:
SQL Server 2017 uses the AES encryption algorithm to protect the service master key (SMK) and the database master key (DMK). AES is a newer encryption algorithm than 3DES used in earlier versions. After upgrading an instance of the Database Engine to SQL Server 2017 the SMK and DMK should be regenerated in order to upgrade the master keys to AES.

Thanks Thom

Thom A SSC Guru Points: 99130 More actions · Answer 5

VastSQL - Wednesday, August 22, 2018 3:55 AM
Sue_H - Tuesday, August 21, 2018 10:52 AM
VastSQL - Tuesday, August 21, 2018 10:40 AM
Hi Experts,
As per my limited knowledge the SMK is created when SQL Service is first started and all SQL Server instance will have SMK .
Why we need to backup SMK?
Because it's used in securing all of the other keys.
Sue
Thanks Sue, when we will be using the backup of SMK ,each instance will have its own SMK right, then whats the use of backup?

Disaster recovery?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Vegard Hagen Hall of Fame Points: 3078 More actions · Answer 6

Well, as has already been stated, the SMK is used to secure all other keys on the server.
Which means that if you have ANY encryption ANYWHERE in your system, you WILL need your SMK.
And each instance having it's own SMK means they CANNOT read each other's encrypted stuff unless they've been specifically set up to be able to do so.

We're not the NSA here, so having good database backups and being able to restore them won't help you one bit if stuff is encrypted with keys you do not have.

That's why you need a backup of your SMK.

Kind regards,

Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

It is better to light a candle than to curse the darkness. (Chinese proverb)

Sue_H SSC Guru Points: 90860 More actions · Answer 7

VastSQL - Wednesday, August 22, 2018 3:55 AM
Sue_H - Tuesday, August 21, 2018 10:52 AM
VastSQL - Tuesday, August 21, 2018 10:40 AM
Hi Experts,
As per my limited knowledge the SMK is created when SQL Service is first started and all SQL Server instance will have SMK .
Why we need to backup SMK?
Because it's used in securing all of the other keys.
Sue
Thanks Sue, when we will be using the backup of SMK ,each instance will have its own SMK right, then whats the use of backup?

The other instances having their own SMK doesn't matter. You need the instance SMK in case you need to rebuild the server, DR as Thom mentioned. You need it for all of the keys for that instance, not for other instances.

Sue

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 8

Sue_H - Wednesday, August 22, 2018 7:20 AM
VastSQL - Wednesday, August 22, 2018 3:55 AM
Sue_H - Tuesday, August 21, 2018 10:52 AM
VastSQL - Tuesday, August 21, 2018 10:40 AM
Hi Experts,
As per my limited knowledge the SMK is created when SQL Service is first started and all SQL Server instance will have SMK .
Why we need to backup SMK?
Because it's used in securing all of the other keys.
Sue
Thanks Sue, when we will be using the backup of SMK ,each instance will have its own SMK right, then whats the use of backup?
The other instances having their own SMK doesn't matter. You need the instance SMK in case you need to rebuild the server, DR as Thom mentioned. You need it for all of the keys for that instance, not for other instances.
Sue

Thanks Sue ,so what i understood from your advise is that when we restore an SMK on a new instance its existing SMK is replaced with SMK of other instance so that we can proceed with restoration of DBs. By the way i tried restoring encrypted backup to another instance and all in need was the certificate from other instance.

Sue_H SSC Guru Points: 90860 More actions · Answer 9

VastSQL - Wednesday, August 22, 2018 11:06 AM
Thanks Sue ,so what i understood from your advise is that when we restore an SMK on a new instance its existing SMK is replaced with SMK of other instance so that we can proceed with restoration of DBs. By the way i tried restoring encrypted backup to another instance and all in need was the certificate from other instance.

If an SMK already exists, yes it would overwrite the existing one, it will decrypt and re-encrypt the existing keys using the new SMK you just restored.
And yes you just need the certificate to restore an encrypted backup from another instance. That's how it's suppose to work.
The encryption hierarchy is explained in this article:
SMKs, DMKs, Certificates for TDE and Encrypted Backups

Sue