SQL Server 2005: A look at the master keys

re: SQL Server 2005: A look at the master keys

kt — Mon, 19 Jun 2006 04:47:26 GMT

Thanks for the info.

I have a question: what should I do if I need to restore a database , that has encrypted data, to a different server? The database can have millions of rows of encrypted data.

re: SQL Server 2005: A look at the master keys

lcris — Tue, 20 Jun 2006 20:40:22 GMT

See the answer in the second part of this article: http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx

Basically, you only need to restore the SMK encryption of the DbMK on the new server, if such encryption existed on the original server. To do this, on the new server you need to issue the following statements:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '...'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

re: SQL Server 2005: A look at the master keys

jimbobmcgee — Mon, 01 Sep 2008 16:07:11 GMT

Apologies for posting this so late after the article date, but I'm having a problem with my SMK and you seem like the best person to ask.

When I try to issue a CREATE CREDENTIAL call, I am told 'An error occurred during decryption'

I have tried to issue an ALTER SERVICE MASTER KEY FORCE REGENERATE, as per your suggestions elsewhere, but am told 'An error occurred during encryption'.

As per another suggestion, I have checked the permissions on the Protect folder in the App Data of SQL Server's service account (a domain account, that I used SQL Server Configuration Manager' to configure) and it seems fine.

Are you able to help?

Thanks

re: SQL Server 2005: A look at the master keys

lcris — Tue, 02 Sep 2008 21:43:11 GMT

Have a look at this more recent article and see if it helps you: http://blogs.msdn.com/lcris/archive/2007/11/14/sql-server-2005-how-to-recover-when-the-service-master-key-smk-is-not-accessible.aspx.

If not, then please post this issue in the Security forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1) and we'll continue the discussion there.