locked
decryption error on linked server after master restore RRS feed

  • 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?

     

    Thursday, November 13, 2008 9:30 PM

Answers

  •   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

     

    Monday, November 17, 2008 7:55 PM

All replies

  •   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

     

    Monday, November 17, 2008 7:55 PM
  • Hi Raul,

    I recently ran into this issue when moving a publisher by backing up the master, msdb, distribution, and publication databases and restoring them on a new server:

    "An error occurred during decryption."

    You mentioned in your previous post that the only way to recover the SMK is via restoring a SMK backup on the new SQL Server.  What about regenerating the SMK using [FORCE], changing the passwords of the necessary logins, and changing the necessary properties (ie. distributor properties) to reflect the new passwords?

    What are your thoughts on this?

    Friday, April 2, 2010 6:42 PM