An error occurred during decryption

We might get Error: 15466, Severity: 16, State: 2  An error occurred during decryption while installing Projects servers (or) Sending mails using database mail (or) Linked server connections might fail with Msg 15593, Level 16, State 1, Line 1

Linked server connection fails with below error

{

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
An error occurred during decryption. (Microsoft SQL Server, Error: 15466)

Msg 15593, Level 16, State 1, Line 1

An error occurred while decrypting the password for linked login ‘distributor_admin’ that was encrypted by the old master key. The error was ignored because the FORCE option was specified.

}

Database mail might fail with below error

{

Set mail server login password failed for MailServer ‘Domain’.  (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

An error occurred during decryption. (Microsoft SQL Server, Error: 15466)

}

Or

You notice below errors in SQL Server errorlogs

spid10s Error: 15581, Severity: 16, State: 3.
Please create a master key in the database or open the master key in the session before performing this operation.

Cause

SQL Server service account was changed from services control manager (or) service master key was not backed up and restored when migrating SQL Server to another computer domain.

{

http://msdn.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.

}

Resolution

Regenerate the service master key using ALTER SERVICE MASTER KEY REGENERATE

If you receive the following error message when running ALTER SERVICE MASTER KEY REGENERATE.

{

The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.

}

We are left with only option to force regenerating service master key using “ALTER SERVICE MASTER KEY FORCE REGENERATE “.

Note:The service master key is the root of the SQL Server encryption hierarchy. The service master key directly or indirectly protects all other keys and secrets in the tree. If a dependent key cannot be decrypted during a forced regeneration, the data the key secures will be lost.

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

We’re running a SQL Server instance on AWS RDS. I believe this is a 2017 instance. We also have a couple of Linux EC2 instances running SQL Server (not sure the version, but I assume 2017)

Our RDS instance has linked servers set up to both of the Linux instances. What I’ve found is that after creating the linked servers, I can query them and they work fine.

This is a QA environment and, to save money, we take all the servers down at night. Then they are started on demand. The problem I’m seeing is that once the servers come back up, I get the following error when trying to query the linked servers:

System.Data.SqlClient.SqlException (0x80131904): An error occurred during decryption.

My best guess is that when the RDS instance comes back up, it appears to be different hardware and I’m guessing there is a decryption key based on that hardware?

I’ve tested a couple things to eliminate possibilities:

1. I tested leaving the Linux instances up and just stop/start (not restarted) the RDS instance. This seemed to reproduce the problem. I haven’t yet tried the reverse (leave RDS running and stop/start a Linux instance).

2. I tried dropping and recreating the linked server when I get this error. This seems to fix the problem.

Theoretically, I guess I could use this sp_procoption procedure I’ve been reading about to run a query at server startup that would drop and recreate all the linked servers. But that seems like the wrong way to fix this.

We could leave our servers running 24×7, though our budget is pretty lean so we’d rather not. And besides, if we had some other reason to stop/start our RDS instance, we still have this problem.

Is there any way to make this work other than just running a drop/create script on the linked servers at server startup?

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’,
‘DomainMary’, ‘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 ‘DomainMary’ 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_backupskeysservice_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

Hello Everyone:

I hope someone can assist with my question:

============================================================================================

Environment:

—————-

Production Environment running:

SQL Server 2005 — 9.00.2153.00 (Intel X86) Standard Edition

Windows NT 5.2 (Build 3790: Service Pack 1)

Using a domain service account.

The problem that was encountered:

——————————————-

I have a two node cluster; I will call the nodes — node1 and node2.

— SQL Server has been running on node1 since I started DBA support for the SQL Cluster.

— Recently an incident occurred which caused SQL to failover onto node2.

— When SQL started up the following errors were displayed in the error log.

2009-02-21 11:06:59.90 spid5s Error: 15466, Severity: 16, State: 1.

2009-02-21 11:06:59.90 spid5s An error occurred during decryption.

2009-02-21 11:07:00.13 Server Error: 17190, Severity: 16, State: 1.

2009-02-21 11:07:00.13 Server FallBack certificate initialization failed with error code: 4.

2009-02-21 11:07:00.13 Server Warning:Encryption is not available, could not find a valid certificate to load.

— In addition to this I noticed that the existing full text indexes could not be used.

After a lot of investigation I ended up failing it back onto node1 again …… and the errors / warning disappeared and fti works. When SQL server starts up now, I receive the following message in the logs:

2009-02-23 19:06:12.87 Server A self-generated certificate was successfully loaded for encryption.

My concern:

—————

Is that if it ever fails over again …… the same problem will occur again.

Backup of SMK

———————

Now that it is running on node 1 I have backed up the SMK using the command:

BACKUP SERVICE MASTER KEY TO FILE = ‘H:smk_20090224.smk’ ENCRYPTION BY PASSWORD = ‘aspecificpassword’

What I would like to do:

——————————

1. I would like to schedule some down time of the system and fail it over onto node2 (where the decryption error occurs) and try to restore the SMK using the backup taken from node 1 ie :

RESTORE SERVICE MASTER KEY FROM FILE = ‘H:smk_20090224.smk’ DECRYPTION BY PASSWORD = ‘aspecificpassword’;

Does this sound plausible; is this the right thing to do?

2. Does any one know how can I recreate this error, so I can test it out?

============================================================================================

Cheers

Tim