SQL Server 2005: what to do when a decryption error occurs while regenerating or reloading a master key

re: SQL Server 2005: what to do when a decryption error occurs while regenerating or reloading a master key

Laurentiu Cristofor [MSFT] — Fri, 15 Jun 2007 21:48:09 GMT

FORCE will work, as discussed, or you could have the machines added to a domain, at least while you're working on this machine replacement - you could then remove them after the replacement is completed.

re: SQL Server 2005: what to do when a decryption error occurs while regenerating or reloading a master key

grundt — Fri, 15 Jun 2007 19:46:08 GMT

Thanks for that clarification.

My IT person tells me we have no DOMAIN ( our servers are stand-alone ).

So it sounds like using FORCE is my only option.

re: SQL Server 2005: what to do when a decryption error occurs while regenerating or reloading a master key

Laurentiu Cristofor [MSFT] — Fri, 15 Jun 2007 05:50:41 GMT

I moderate all comments, so they don't appear immediately. And I check my blog for comments about once a day. I also received this last comment only once.

Coming back to your problem - by same service account, I meant having the same domain user - both the domain and the user part have to be the same. Two local users are not the same, even if they have the same name.

If you see this error when the service account on both servers is the same domain account, then let me know - that would probably indicate some bug - I don't expect an error for this scenario. This is the only scenario that allows you to move the databases without having to force reload the SMK.

re: SQL Server 2005: what to do when a decryption error occurs while regenerating or reloading a master key

grundt — Thu, 14 Jun 2007 19:03:20 GMT

Laurentiu ...

I appreciate your responses and apologize if you've received this already. I posted the message twice yesterday, but haven't seen it appear. Here goes again ...

The services had both been running under the LOCALSYSTEM account.

Just to experiment, I created a user account on both servers, with the same name, and switched both services to use those accounts.

The results were the same ... I still wound up getting the "15329 The current master key cannot be decrypted" error.

re: SQL Server 2005: what to do when a decryption error occurs while regenerating or reloading a master key

Laurentiu Cristofor [MSFT] — Wed, 13 Jun 2007 23:56:56 GMT

I saved the results you posted, but I cut them before publishing your comment, because they were taking too much space. I also noticed that the crypt_property values from your second query are truncated.

What is your question?

re: SQL Server 2005: what to do when a decryption error occurs while regenerating or reloading a master key

grundt — Wed, 13 Jun 2007 19:03:18 GMT

During my experiment (see prior post), I ran these queries on server C:

SELECT * FROM sys.symmetric_keys

SELECT * FROM sys.key_encryptions

re: SQL Server 2005: what to do when a decryption error occurs while regenerating or reloading a master key

Laurentiu Cristofor [MSFT] — Mon, 11 Jun 2007 23:54:46 GMT

Are your service accounts on A and C different? If they are the same, you should not see this error. I expect to see such errors if the service accounts are different.

FORCE loading the SMK should also work - it won't be able to decrypt anything, so it will just load the SMK and re-encrypt it such that you can decrypt it; if you loaded the right SMK, then it will now decrypt whatever used to be encrypted with it. Of course, don't throw away your database backup before this operation.

So, if you can setup the service account to be the same on C as it was on A, you should be able to start the database normally, and then you could change the service account to something else, if you wish - but you must start the database under the same account as on the source server. Otherwise, you'll have to use the FORCE option to reload the SMK.

re: SQL Server 2005: what to do when a decryption error occurs while regenerating or reloading a master key

grundt — Mon, 11 Jun 2007 20:43:59 GMT

Scenario:

New hardware: replacing production db server (A) with new server (C).

Log shipping between server A (primary) and server B (secondary).

A and C are at the same data center.

B is at a different data center, far far away.

Plan:

Install SQL Server on C;

Create tail log (backup log with no recovery) on A;

Failover from A to B;

Configure B as primary log shipping server;

Restore database with standby on A;

Copy db files (system & application) from A to C;

Change startup param on C to point to copied system db files;

Configure C as secondary ( B to C );

Failover from B to C;

The system db's are being copied from A to C because it seems to be the only way to preserve the "STANDBY.." state on the application db. This allows log shipping from B to C without having to restore a backup from B to C.

After copying the system db's from A to C; changing the startup param on C to point to those files; and starting SQL Server ... I get the "15466: An error ocurred during decryption" error.

Backing up the Service Master Key on A, and restoring it on C fails with "15329: The current master key cannot be decrypted."

Q: is there some procedure that would allow me to end up with a good Service Master Key on C, without having to use the force option ?

re: SQL Server 2005: what to do when a decryption error occurs while regenerating or reloading a master key

Laurentiu Cristofor [MSFT] — Sat, 12 Aug 2006 00:33:59 GMT

You should ask this question on the replication forum at: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=90&SiteID=1.

This is most likely an issue related to encryption done in replication. I don't think it has anything to do with the SMK or with the encryption features exposed in SQL Server 2005.

re: SQL Server 2005: what to do when a decryption error occurs while regenerating or reloading a master key

CrispyRice — Fri, 11 Aug 2006 19:09:16 GMT

Hi, I'm getting this problem:

[298] SQLServer Error: 22046, Encryption error using CryptProtectData. [SQLSTATE 42000]

when trying to create a replication publication, i though it might be due to the SMK, i have done everything suggest including FORCE REGENERATION, but still get the message.
Have you any idea?

Its a SQL 2005 Cluster

Thanks,

Chris