decryption error on linked server after master restore

Question

I've googled the error and found many references to regenerating the service master key, but there is little to no exlplanation of why.

We recently rebuild a SQL Server -- reinstalled and then restored the system databases and user databases.  We ran into an issue with the fact that the local user groups that are built by the installation were then out of synch with the local user groups in SQL Server after the restore of master.  The resolution was to remove the groups and re-add them. as if to synch up the SIDs for a user.

After all of this, however, I still recieve the error: An error occurred during decryption.

when trying to run an alter on a stored procedure which utilize the linked server.   I'm also unable to create a new linked server or execute the stored procedures which utilize the linked server.

I do understand that the Service Master key is used to encrypt the linked server passwords, and this encryption is tied to the account used to run the SQL Server service.

So, the question I have is that since when the server was rebuilt, the account used for the service was recreated and then master was restored over the top, I'll need to do the regenerate on the Service master key.

Correct?

 

Answer 1

  Given your description, most likely the problem is that the service master key (SMK) is protected by DPAPI. If the service account for SQL Server is a domain user, the SMK should be able to recover itself when moved to a different server, if not, the only way to recover the SMK is via restoring a SMK backup on the new SLQ Server .

 

Laurentiu wrote a few articles that describe these type of error in great detail, and you may also find other good resources by searching this forum. Below I am adding a few links that may be useful (including Laurentiu's articles on this topic):

http://blogs.msdn.com/lcris/archive/2006/04/10/572678.aspx

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

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1016860&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3454765&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1741947&SiteID=1

 

Please let us know if you have any additional questions or feedback.

 

  Thanks,

-Raul Garcia

  SDE/T

  SQL Server Engine