November 14, 2011 at 3:29 pm
I have two instances on my laptop, Default instance and Instance1.
In the default instance, I have enabled TDE.
Im now trying to restore a backup of a TDE Database from default instance to Instance1.
First, I backed up the ServiceMasterKey:
BACKUP SERVICE MASTER KEY TO FILE = 'C:\SQLServerFiles\TDE\serviceMasterKey'
ENCRYPTION BY PASSWORD = 'Password1'
I then opened a session on instance2, in the master database, and ran the following code:
--restore TDE Encrypted database to new instance
RESTORE SERVICE MASTER KEY FROM FILE = 'C:\SQLServerFiles\TDE\serviceMasterKey'
DECRYPTION BY PASSWORD = 'Password1'
GO
When i run this i get the following error:
Msg 15317, Level 16, State 2, Line 1
The master key file does not exist or has invalid format.
I know the key is not corrupt as if i try to restore it onto the default instance i get the message:
The old and new master keys are identical. No data re-encryption is required
Am i missing a step to restore the Service Master Key to the Instance2?
November 14, 2011 at 3:49 pm
UPDATE: I got the Service Master Key restored to the Instance2. I had to change the sql service account on Instance2, to the same as was on the default instance.
Can anyone explain why this is?
November 14, 2011 at 5:58 pm
the code you posted points to two different folders; 'C:\SQLServerFiles\serviceMasterKey'
and 'C:\SQLServerFiles\TDE\serviceMasterKey'
could that have been the issue? the extra folder?
Lowell
November 15, 2011 at 12:40 am
Apologiefor the path mistake. That was not the issue though. That was just a copy/paste blunder on my part. (spelling now corrected)
When I changed the srvc acc of the instance2 srvc to the account running the default instance the restore code worked. I can't figure out why.
November 15, 2011 at 12:53 am
The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password.
Regenerating or restoring the Service Master Key involves decrypting and re-encrypting the complete encryption hierarchy. Unless the key has been compromised, this resource-intensive operation should be scheduled during a period of low demand.
November 15, 2011 at 2:27 am
Dev (11/15/2011)
The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password.
So, if i have this correct, i couldnt restore the service master key to my Instance2, as the serviceAccount for instance2 is not a principal with access to the serviceAccount that created the ServiceMasterKey.
How do i assign access to the Account that created the Serivce master key? I want to know as in future i may have to restore encrypted backups to servers running under completely different service accounts, with no link to the server on which the database, and original ServicemasterKey was created.
November 15, 2011 at 3:19 am
winston Smith (11/15/2011)
Dev (11/15/2011)
The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password.So, if i have this correct, i couldnt restore the service master key to my Instance2, as the serviceAccount for instance2 is not a principal with access to the serviceAccount that created the ServiceMasterKey.
How do i assign access to the Account that created the Serivce master key? I want to know as in future i may have to restore encrypted backups to servers running under completely different service accounts, with no link to the server on which the database, and original ServicemasterKey was created.
If you use domain accounts for SQL Server Services, you void this question at the first place. Domain Account could be used with side-by-side installations (your current situation) and with any server in network (your doubt).
November 15, 2011 at 3:27 am
I understand, but imagine a situation where I take a backup of an encrypted DB from ServerA Running under Account AccA.
ServerA is running under AccA as AccA has access to shares/other servers to allow it to function in its distributed application.
I want to restore the encrypted backup to serverB, running under AccB. AccB has access to other shares, databases etc. There is no link between the ServerA and ServerB. They are completely different application.
I cannot change the serviceAccount of either servers as they requrie the accuont they have to access shares/other databases.
How do i restore that database backup onto serverB?
November 15, 2011 at 3:48 am
I checked the article RESTORE SERVICE MASTER KEY (Transact-SQL) nothing much their except FORCE option which can lead to data loss.
http://msdn.microsoft.com/en-us/library/ms187972.aspx
I went through the document Windows Data Protection (quick read) to understand how it actually wroks. Not much help there as well.
http://msdn.microsoft.com/en-us/library/ms995355.aspx
I am afraid I can’t help more.
November 15, 2011 at 3:53 am
no worries. think a bit of testing is required. Il post any results. thanks for the help.
November 15, 2011 at 4:16 am
I investigated further and here is something that might help you.
The service master key is automatically generated the first time it is needed to encrypt a linked server password, credential, or database master key. The service master key is encrypted using the local machine key or the Windows Data Protection API. This API uses a key that is derived from the Windows credentials of the SQL Server service account.
The service master key can only be decrypted by the service account under which it was created or by a principal that has access to the Windows credentials of that service account. Therefore, if you change the Windows account under which the SQL Server service runs, you must also enable decryption of the service master key by the new account.
Changing the SQL Server Service Account
To change the SQL Server service account, use SQL Server Configuration Manager. To manage a change of the service account, SQL Server stores a redundant copy of the service master key protected by the machine account that has the necessary permissions granted to the SQL Server service group. If the computer is rebuilt, the same domain user that was previously used by the service account can recover the service master key. This does not work with local accounts or the Local System, Local Service, or Network Service accounts. When you are moving SQL Server to another computer, migrate the service master key by using backup and restore.
The REGENERATE phrase regenerates the service master key. When the service master key is regenerated, SQL Server decrypts all the keys that have been encrypted with it, and then encrypts them with the new service master key. This is a resource-intensive operation. You should schedule this operation during a period of low demand, unless the key has been compromised. If any one of the decryptions fail, the whole statement fails.
The FORCE option causes the key regeneration process to continue even if the process cannot retrieve the current master key, or cannot decrypt all the private keys that are encrypted with it. Use FORCE only if regeneration fails and you cannot restore the service master key by using the RESTORE SERVICE MASTER KEY statement.
The only thing that is not very clear to me is:
When you are moving SQL Server to another computer, migrate the service master key by using backup and restore.
It makes sense if and only if another computer is in same network. (Domain User option)
November 15, 2011 at 4:32 am
The only thing that is not very clear to me is:
When you are moving SQL Server to another computer, migrate the service master key by using backup and restore.
It makes sense if and only if another computer is in same network. (Domain User option)
And if the new computer has its SQL Service running under the same Service Account that the SMK was originally created under. Thats a big limitation IMO.
Thanks for the help Dev, rally appreciate it!
November 15, 2011 at 8:04 am
Thanks for the help Dev, rally appreciate it!
In fact Thanks to you. You gave me a good subject to study upon. Please count me in 😉 for R&Ds, if you are planning any sometime soon.
November 15, 2011 at 9:47 pm
winston Smith (11/15/2011)
How do i restore that database backup onto serverB?
You don't need the service master key, A backup of the certificate and its private key are all you need to recover the database to another instance!
The service master key is created the first time the service starts.
When creating a database master key you encrypt it using a password and a copy is encrypted using the service master key (derived from the service account and machine name) to allow automatic use of the service master key.
winston Smith (11/15/2011)
Thats a big limitation IMO.
Follow the correct procedure and it's not 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 6, 2011 at 12:51 pm
try this on your instance 2 :
ALTER SERVICE MASTER KEY WITH OLD_ACCOUNT = 'old_service_account', OLD_PASSWORD = 'old_service_account_password'
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply