Wednesday, January 6, 2010

An Error Occurred during decryption when creating a linked server.

When linked server is created, following error is occurring.

An error occurred during decryption.

PN: Also, when configuring distribution for replication following error occurred.

An error occurred during decryption.

There is no remote user 'distributor_admin' mapped to local user '(null)' from the remote server 'repl_distributor'.

Changed database context to 'master'. (Microsoft SQL Server, Error: 15466)

This also due to unable to create linked server named repl_distributor.

The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password.

Solution is to regenerate the service master key with following command.

ALTER SERVICE MASTER KEY REGENERATE

In case above statement generates an error, you have to use with FORCE command. http://support.microsoft.com/kb/914261

ALTER SERVICE MASTER KEY FORCE REGENERATE

7 comments:

  1. The correct syntax is:
    ALTER SERVICE MASTER KEY FORCE REGENERATE

    ReplyDelete
  2. Just had to do this. And Josh is correct the syntax is off.
    ALTER SERVICE MASTER KEY FORCE REGENERATE

    ReplyDelete
  3. brillant piece of information, I had come to know about your web-page from my friend hardkik, chennai,i have read atleast 9 posts of yours by now, and let me tell you, your webpage gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanx a million once again, Regards, obiee training in hyderebad

    ReplyDelete
  4. Thanks. this has been so helpful to resolve issues of my own

    ReplyDelete
  5. Is there a risk to run this command ??
    ALTER SERVICE MASTER KEY FORCE REGENERATE

    ReplyDelete
    Replies
    1. please comment on the question asked....we are also facing the same issue......pls comment what will happen when we run this command. will it change any current databases properties/data.....any other issues?? please let usknow.

      Delete
  6. Also would like to know about risks

    ReplyDelete