Tuesday, 18 October 2016

There is no remote user ‘xxxxx’ mapped to local user ‘(null)’ from the remote server ‘xxxxx’

My Client recently complained that his linked server was failing to connect with the following error,

“An error Occurred during Service Key decryption
There is no remote user ‘xxxxx’ mapped to local user ‘(null)’ from the remote server ‘xxxxx’.
(Microsoft SQL Server, Error : 33094)”

On checking the user permissions were right and the remote login in which the Log server was configured had right permissions to access to the Linked server.

When I did some googling I stumbled across, https://technet.microsoft.com/en-us/library/ms187788.aspx

To change the SQL Server service account, use SQL Server Configuration Manager. To manage a change of the service account, SQL Server stores a redundant copy of the service master key protected by the machine account that has the necessary permissions granted to the SQL Server service group. If the computer is rebuilt, the same domain user that was previously used by the service account can recover the service master key. This does not work with local accounts or the Local System, Local Service, or Network Service accounts. When you are moving SQL Server to another computer, migrate the service master key by using backup and restore.

The REGENERATE phrase regenerates the service master key. When the service master key is regenerated, SQL Server decrypts all the keys that have been encrypted with it, and then encrypts them with the new service master key. This is a resource-intensive operation. You should schedule this operation during a period of low demand, unless the key has been compromised. If any one of the decryptions fail, the whole statement fails.

The FORCE option causes the key regeneration process to continue even if the process cannot retrieve the current master key, or cannot decrypt all the private keys that are encrypted with it. Use FORCE only if regeneration fails and you cannot restore the service master key by using the RESTORE SERVICE MASTER KEY statement.”

This is exactly what happened with my client, the SQL was running under local account and the server was migrated.

Once I ran ALTER SERVICE MASTER KEY FORCE REGENERATE the linked servers started working gain.



  2. Cheap Moncler jackets , combining elegant style and cutting-edge technology, a variety of styles of Cheap moncler jackets men jackets , the pointer walks between your exclusive taste style.

  3. This comment has been removed by the author.

  4. This is actually the kind of information I have been trying to find. Thank you for writing this information. http://www.mamafamille.ca/

  5. solution worked as expected. thx

  6. and the thought is to persuade the application to run SQL code that was not expected. In the event that the application is making SQL strings innocently on the fly and, running them, it's direct to make a few genuine astonishments. https://onohosting.com/

  7. More modest ventures might be served by the building range of abilities moved by the DBA. You should go over the DBA's experience and check for preparing and involvement with database plan and execution. Past experience on a database creation venture would be great.https://hostinglelo.in/