An error occurred during decryption ошибка

  • Remove From My Forums

  • Question

  • Hi

    I get an error on my SQL Server 2005 database when I try to decrypt a password using AES_256 algorithm.

    Here is what I did to get the error:

    1.  Created a stored Procedure on server A (SQL 2005, windows server 2003, joined on domain) that create a symetric key using AES_256 algorithm and decrypt password from a table.

    2. I restored the DB that contains this SP to another server B (SQL 2005, windows server 2003, joined on domain) and try to run that same SP to decrypt. I get below Message:

    Msg 15466, Level 16, State 1, Procedure ReadDecryptedPwd, Line 44
    An error occurred during decryption.

    I suspect this problem has to do with master service keys but need some advice. I tried taking a backup of the SMK of server A and restoring on server B, then deleting symetric key that use AES_256 and recreating it, but still have the same message.

    What can I do to solve my issue?

    Thanking you in advance for your help.

    Regards,

    Barabello

Answers

  • Dear Erlang,

    Erland. :-)

    Note that the procedure for decryption has a piece of code that is questionable:

    IF NOT EXISTS  (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)

    BEGIN
        CREATE MASTER KEY ENCRYPTION BY
            PASSWORD = ‘23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj’

        CREATE CERTIFICATE ExchangePassword
           WITH SUBJECT = ‘Employee Password for Exchange and Windows’;

        CREATE SYMMETRIC KEY SSN_Key_01
            WITH ALGORITHM = AES_256
            ENCRYPTION BY CERTIFICATE ExchangePassword;
    END

    The IF statement checks for the database master key. But if all keys for whatever reason have been lost at this point, it will not help to create new keys at this point. The data will be lost.

    A database master key can be protected by a password of by the service master key. As long as you are on the source server, you don’t need to open the database master key, since the service master key is there for you.

    But if you move the database to a different server, that server has a different master key. Therefore, to use the database master key on the new server, you will need do open it first, by using the password. 

    OPEN MASTER KEY DECRYPTION BY PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'
EXEC ReadDecryptedPwd 'Someuser'

    You can use ALTER MASTER KEY to change the master key so that it is encrypted by the service master key of the new server:

    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

    However, you will need to open the master key first (to prove that you are authorised to use the master key).

    If you are new to encryption and want to get a head start, this book by MVP colleagues Michael Coles and Rodney Landrum may be useful to you:
    http://www.amazon.com/Expert-SQL-Server-2008-Encryption/dp/1430224649/ref=sr_1_1?ie=UTF8&qid=1342183362&sr=8-1&keywords=michael+coles+encryption

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by

      Tuesday, July 17, 2012 8:22 AM

Источник

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?

Источник

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

Источник
  • Remove From My Forums

  • Question

  • i cann’t create the credential.

    when i type the user and password. i get error 15466 An error occurred during decryption.

    the first time i use this code

    Use master

    crate credential outs_cred with identity=’DomainName\Administrator’,secret=‘xxxxkmn’

    i will get error 15466 An error occurred during decryption.

    but i use this code  (no secret)

    Use master

    crate credential outs_cred with identity=‘DomainName\Administrator’

    i will create the Credential success.

    Why??

    Best regards,

    Mr.Problem

Answers

  • I already posted a longer reply and it got lost, so this is my second attempt to reply:

    Thank you for this information. Executing and failing the REGENERATE statement will have no impact on your server state — it just confirms Raul’s observation that the SMK is probably undecryptable.

    The error you get in a lot of those ring buffer entries is indeed a cryptographic error:

    #define NTE_BAD_KEY_STATE                _HRESULT_TYPEDEF_(0x8009000BL)

    I wrote a post on my blog that includes the recovery steps I had previously included in the reply that got lost: http://blogs.msdn.com/lcris/archive/2007/11/14/sql-server-2005-how-to-recover-when-the-service-master-key-smk-is-not-accessible.aspx.

    Look through these steps — you basically would need a service master key backup to be able to recover. Otherwise you would need to recreate all linked server passwords and credentials and to re-encrypt the database master keys.

    Thanks

    Laurentiu

Источник

Понравилась статья? Поделить с друзьями:
  • Amrestoreerrordomain ошибка 9
  • Amppal sys ошибка
  • Ample guitar ошибка
  • Amp fault ошибка усилителя
  • Amd ошибка 43 patcher