An Error Occurred During Decryption Sql Server 2005
Contents |
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: an error occurred during decryption sql server 2008 Msg 15466, ... An error occurred during decryption. SQL Server encryption and decryption in sql server > SQL Server Security Question 0 Sign in to vote Hi I get an error an error occurred during decryption. linked server 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:
An Error Occurred During Service Master Key Decryption
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) error 15466 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 Wednesday, July 11, 2012 7:14 AM Reply | Quote Answers 0 Sign in to vote 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' CR
occurs while regenerating or reloading a master key ★★★★★★★★★★★★★★★ Laurentiu Cristofor [MSFT]April 10, 200612 0 0 0 Decryption errors during the regeneration of a master key are highly an error occurred during service master key decryption linked server unlikely. I've never seen one of these occurring naturally so far (we caused
An Error Occurred During Service Master Key Decryption 33094
these errors manually for testing), so a discussion on their topic has a slim chance of being useful in
Alter Service Master Key Force Regenerate
practice. However, I also know this topic isn't covered anywhere else in the detail I'll cover it here, and while writing an answer to a question about it, I realized I was https://social.msdn.microsoft.com/Forums/sqlserver/en-US/29886005-8604-4385-9af9-975c4828dd26/msg-15466-an-error-occurred-during-decryption?forum=sqlsecurity actually writing an entire topic, not just an answer. So I've decided to write my answer as a new post that can serve as reference in case anyone will ever need this information or is just curious about the details. I'll discuss each master key separately. Service Master Key The entitites encrypted by the SMK are credential secrets, linked server login passwords, and https://blogs.msdn.microsoft.com/lcris/2006/04/10/sql-server-2005-what-to-do-when-a-decryption-error-occurs-while-regenerating-or-reloading-a-master-key/ DbMKs. DbMKs always have an additional encryption by a password, so, unless that password is forgotten, the key cannot be lost - even if the SMK encryption is corrupted. For the other two entities, if the SMK is changed and errors occur while attempting to decrypt them with the current key, then if FORCE is specified, the errors will be ignored and a new key will be regenerated anyway; this new key will naturally not be able to access the entities for which errors were encountered earlier, so they are likely lost - this is the reason why the error messages mention the possibility of data loss. The FORCE option is an option for unblocking the regeneration or reload of the SMK and for ignoring any decryption errors that occur during the process. Without FORCE, decryption errors will abort the LOAD or ALTER REGENERATE operations. With FORCE, decryption errors are ignored and the processing of the entities for which the error was hit is skipped (no attempt will be made to reencrypt them with the new key because theycan't be decrypted using the current key). FORCE is a la
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack http://stackoverflow.com/questions/209927/sql-server-2005-restoring-an-encrypted-db-on-a-different-server Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up SQL Server 2005 - Restoring an encrypted DB on a different server up vote 3 an error down vote favorite I have backed up an encrypted DB (symmetric key/certificate) and restored it on a different server. Unfortuantely we're having problems with the decryption... hoping someone can help. In the restored db, I can see the Symmetric Key and the Certificate in SSMS, but when I try to Open the key using the cert ( open symmetric key KeyA decryption by certificate CertB )I get the following very descriptive an error occurred error: Msg 15466, Level 16, State 1, Line 1 An error occurred during decryption. Any ideas? Thanks in advance. sql-server sql-server-2005 encryption symmetric-key share|improve this question edited Oct 16 '08 at 19:57 Mark Brady asked Oct 16 '08 at 19:30 user24531 17115 add a comment| 3 Answers 3 active oldest votes up vote 2 down vote http://blogs.msdn.com/lcris/archive/2007/11/16/sql-server-2005-restoring-the-backup-of-a-database-that-uses-encryption.aspx answers this: "When you restore a database that uses encryption features, there is only one thing you need to take care off - if the database master key (DbMK) needs a service master key (SMK) encryption, you need to regenerate this encryption. Note that this encryption is made by default when you create the DbMK, but it may be intentionally dropped, if you want tighter control of access to the encrypted data. Anyway, if you did have such SMK encryption for the DbMK, the steps to regenerate it are the following: OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password' ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY CLOSE MASTER KEY That's it - the database encryption features should now work as when the backup was taken. Also note that it doesn't matter if you restore the database on the server where the backup was taken or elsewhere. The only thing that m