Translate

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

4 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. Thanks. this has been so helpful to resolve issues of my own

    ReplyDelete