none
Error Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin - An error occurred during decryption

    Question

  • Greetings all,

    I'm having a Problem with adding logins to a linked server via sp_addlinkedsrvlogin stored procedure between SQL 2005 & 2000.

    Environment: SQL Server 2005 SP2 build 9.00.3073.00, Enterprise Edition running on Windows 2003 R2 SP2.  Created a linked server successfully to SQL Server 2000 SP4 build 8.00.2279, Enterprise Edition. 

     

    Issue: When trying to add a login using:

    EXEC sp_addlinkedsrvlogin 'ServerName', 'false', 'Domain\Mary', 'user', 'userPW'

    And the user and userPW are SQL logins on the server being linked to.

    --I Receive error:

    /*Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91

    An error occurred during decryption.

    Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98

    There is no remote user 'itdevlinkuser' mapped to local user 'Domain\Mary' from the remote server 'Alaric'.

    */

     

    --Tried to execute to test connection.

    exec sp_tables_ex Alaric

     

    /* received error:

    OLE DB provider "SQLNCLI" for linked server "Alaric" returned message "Communication link failure".

    Msg 10054, Level 16, State 1, Line 0

    TCP Provider: An existing connection was forcibly closed by the remote host.

    Msg 18452, Level 14, State 1, Line 0

    Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

    */

    --The linked server does exist, by checking:

    sp_linkedservers

    Select * from sys.servers where is_linked = 1

     

    Tried dropping and recreating it:

    --Drop the existing:

    sp_dropserver 'servername', 'droplogins'

     

    --Create a new one:

    USE master

    GO

    EXEC sp_addlinkedserver

        'servername',

        N'SQL Server'

    GO

    But creating logins still fails.  Also tried recreating is using @provider='SQLNCLI', to no avail.

     

    I Googled on “An error occurred during decryption.” and found a couple posted solutions to perform the following:

    ALTER SERVICE MASTER KEY REGENERATE

     

    What exactly does this command do and what could it potentially break??  The BOL doesn’t give me enough info to determine if I’m going to break anything by running this command.  Can someone help me understand the internals to this security and verify nothing will break? 

    I researched the security hierarchy a bit and it appears that this service master data key was generated when the linked server was created, but how do I determine that? 

    I also read that the service master key controls all encryption in the DB.  We don’t have encryption turned on via any configuration – at least that I can find.  (I just inherited this server.) 

    I investigated and found:

    SELECT * FROM sys.SERVER_PERMISSIONS --60 rows, all are server class_desc except for 5.

    SELECT * FROM sys.SYMMETRIC_KEYs --1 created 10/21/08 & modified 6/12/09; the linked server was just created last week.

    SELECT * FROM sys.aSYMMETRIC_KEYs --none

    SELECT * FROM sys.certificates --5 certificates

    SELECT * FROM sys.credentials --none

    SELECT * FROM sys.master_key_passwords --none

     

    This is a dev server but, (you know there had to be one!), there is one developed appl that was turned on in production here that we can’t ‘break’ the system.  (after this issue is resolved my first order of business is to get this prod system off to a prod server, of course)

     Is it safe to simply backup the service master key via BOL example:

    BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp_backups\keys\service_master_key' ENCRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4';

    --Will any strong password value do here since we don't use encryption?

    And then run the command?

    ALTER SERVICE MASTER KEY REGENERATE

     

    I also see this is a resource intensive command.  Why?  What exactly is it touching?

     

    Thanks in advance for your help, it’s greatly appreciated!

    Lori

     

     

    Thursday, April 22, 2010 2:25 PM

Answers

  • Hi,


    SQL Server uses encryption keys to help secure data, credentials, and connection. The Service Master Key is the root of the SQL Server encryption hierarchy that is used to encrypt another key. However, in this case, the error is often caused by the Service Master Key missing. To verify it, you could check the Entropy key under  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\Security by using Registry Editor. If you cannot find it, execute the following command to recreate the missing Key:
    Use master;
    Go
    ALTER SERVICE MASTER KEY FORCE REGENERATE;
    Go

    If there are any more problems, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Monday, April 26, 2010 5:15 AM
    Moderator
  • Hi,


    I think SQL Server Books Online is the best learning material. For SQL Server encryption keys, you could refer to:
    SQL Server and Database Encryption Keys (Database Engine)
    http://msdn.microsoft.com/en-us/library/bb964742(v=SQL.100).aspx

    If there are any more problems, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Tuesday, April 27, 2010 2:34 AM
    Moderator

All replies

  • Hi,


    SQL Server uses encryption keys to help secure data, credentials, and connection. The Service Master Key is the root of the SQL Server encryption hierarchy that is used to encrypt another key. However, in this case, the error is often caused by the Service Master Key missing. To verify it, you could check the Entropy key under  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\Security by using Registry Editor. If you cannot find it, execute the following command to recreate the missing Key:
    Use master;
    Go
    ALTER SERVICE MASTER KEY FORCE REGENERATE;
    Go

    If there are any more problems, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Monday, April 26, 2010 5:15 AM
    Moderator
  • Thank you Xiao,

    I verified the key is missing in the registry and we will try to execute this command soon.  I will let you know the outcome.

    Also, is there more detailed information available that you can provide as to what this does internally to SQL Server?  I would appreciate learning more.

    Thank you,

    Lori

    Monday, April 26, 2010 1:33 PM
  • Hi,


    I think SQL Server Books Online is the best learning material. For SQL Server encryption keys, you could refer to:
    SQL Server and Database Encryption Keys (Database Engine)
    http://msdn.microsoft.com/en-us/library/bb964742(v=SQL.100).aspx

    If there are any more problems, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Tuesday, April 27, 2010 2:34 AM
    Moderator
  • Thanks for your help Xiao,

    I just wanted to let you know that your provided command:

    ALTER SERVICE MASTER KEY FORCE REGENERATE;
    Go

    did the trick.

    Take care,

    Lori

    Tuesday, May 04, 2010 4:07 PM