locked
Msg 15466, ... An error occurred during decryption. RRS feed

  • 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


    Wednesday, July 11, 2012 7:14 AM

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 amber zhang Tuesday, July 17, 2012 8:22 AM
    Friday, July 13, 2012 12:44 PM

All replies

  • Hi,

    The Service Master Key is the root of the SQL Server encryption hierarchy that is used to encrypt another key. you could check the key under  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\Security by using Registry Editor.

    If you cannot find it, execute the following command to recreate the missing Key:
    Use master;
    Go
    ALTER SERVICE MASTER KEY FORCE REGENERATE;
    Go

    Even if the key is there, just take a backup of the key and try to regenerate the key. Hope it will fix the issue.


    Thanks & Regards, Pramilarani.R

    Wednesday, July 11, 2012 7:44 AM
  • I already tried to force regenerate the key but still have same error message. Any other suggestions?

    Thanks and regards.

    Wednesday, July 11, 2012 8:17 AM
  • Hi,

    If you have downtime, Please try to recycle the SQL Instance before and after trying the option.

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123808


    Thanks & Regards, Pramilarani.R

    Wednesday, July 11, 2012 8:49 AM
  • I haven't tested this, and I have not worked that much with encryption in SQL Server.

    But reasonably, if you want to encrypt on one server and decrypt on another server, one of the following must apply:

    1) Both servers must have the same service master key.
    2) At some point you must open a key through a password.

    As I understand your post, you delete the key in the restored database, and at that point you have lost all possibilities to decrypt the data. The fact that you create a new key with the same name is not going to help. While the name is the same, it is a different key.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 11, 2012 8:51 AM
  • I haven't tested this, and I have not worked that much with encryption in SQL Server.

    But reasonably, if you want to encrypt on one server and decrypt on another server, one of the following must apply:

    1) Both servers must have the same service master key.
    2) At some point you must open a key through a password.

    As I understand your post, you delete the key in the restored database, and at that point you have lost all possibilities to decrypt the data. The fact that you create a new key with the same name is not going to help. While the name is the same, it is a different key.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    I see, I there a way to recreate the Master Key on the restored server just like the one from the original server? will my issue will be solved then?

    Hi,

    If you have downtime, Please try to recycle the SQL Instance before and after trying the option.

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123808


    Thanks & Regards, Pramilarani.R

    When I run :

    USE master;
    GO
    OPEN Master KEY
        DECRYPTION BY PASSWORD = '68F19A00-5DBF-486F-8B9F-84F342F206F4';
    ALTER Master KEY
        DROP ENCRYPTION BY SERVICE MASTER KEY;
    ALTER Master KEY
        ADD ENCRYPTION BY SERVICE MASTER KEY;
    CLOSE MASTER KEY;
    GO

    I get below errors:

    Msg 15151, Level 16, State 1, Line 1
    Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.
    Msg 15151, Level 16, State 1, Line 3
    Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.
    Msg 15151, Level 16, State 1, Line 5
    Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.
    Msg 15581, Level 16, State 3, Line 7
    Please create a master key in the database or open the master key in the session before performing this operation.

    Wednesday, July 11, 2012 9:09 AM
  • Hi,

    Can you find the key in the registry, it will be in this location.

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\Security .

    I Hope you are Sysadmin on this box , if not please try with the ID which has Sysadmin access on that box.


    Thanks & Regards, Pramilarani.R

    Wednesday, July 11, 2012 9:21 AM
  • Hi,

    Can you find the key in the registry, it will be in this location.

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\Security .

    I Hope you are Sysadmin on this box , if not please try with the ID which has Sysadmin access on that box.


    Thanks & Regards, Pramilarani.R

    when I go to RegEdit

    I don't find my server instance name under Microsoft SQL server.

    I found a node called "Instance Names" but it doens't contain my instance name, However I found the "Security" node under "MSSQL.1" and it have 2 files Default (REG_SZ file) and Entropy (Reg_BINARY)

    Wednesday, July 11, 2012 9:57 AM
  • I've played around a little. Since I don't know all your steps, I can't say where you go wrong - but you should definitely not drop any keys when you have restored the database.

    I ran this on the source server:

    CREATE DATABASE cryptic
    go
    USE cryptic
    go
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ForTestONly'
    go
    CREATE ASYMMETRIC KEY asymme WITH ALGORITHM = RSA_1024
    go
    CREATE SYMMETRIC KEY symme WITH ALGORITHM = AES_256
         ENCRYPTION BY ASYMMETRIC KEY asymme
    go
    CREATE TABLE secrets (id            int NOT NULL PRIMARY KEY,
                                                hemligt varbinary(400) NULL)
    go
    OPEN SYMMETRIC KEY symme DECRYPTION BY ASYMMETRIC KEY asymme
    go
    INSERT secrets (id, hemligt)
        VALUES (1, encryptbykey(key_guid('symme'), 'Official Secrets'))
    go
    CLOSE SYMMETRIC KEY symme
    go
    BACKUP DATABASE cryptic TO DISK = 'c:\temp\cryptic.bak'
    BACKUP MASTER KEY TO FILE = 'C:\temp\thekey.clé'
         ENCRYPTION BY PASSWORD = 'This is another password'

    Then on the other server I ran in one query window:

    RESTORE DATABASE cryptic FROM DISK = 'C:\temp\cryptic.bak'
    WITH MOVE 'cryptic' TO 'C:\temp\cryptic.mdf',
             MOVE 'cryptic_log' TO 'C:\temp\cryptic.ldf',
             REPLACE
    go
    USE cryptic
    go
    RESTORE MASTER KEY FROM FILE = 'C:\temp\thekey.clé'
    DECRYPTION BY PASSWORD = 'This is another password'
    ENCRYPTION BY PASSWORD = 'This is the third password'
    FORCE
    go
    USE tempdb

    And in a second window, I ran:

    [sql]
    USE cryptic
    go
    --OPEN MASTER KEY DECRYPTION BY PASSWORD = 'ForTestONly'
    go
    OPEN SYMMETRIC KEY symme DECRYPTION BY ASYMMETRIC KEY asymme
    go
    SELECT id, convert(varchar(40), decryptbykey(hemligt))
    FROM secrets
    go
    USE tempdb
    go
    DROP DATABASE cryptic
    {/sql]

    And this worked. If I don't do RESTORE MASTER KEY, I need to use the commented OPEN MASTER KEY command above.

    Previously I suggested that you have to copy the service key between servers. This is incorrect. When you run RESTORE MASTER KEY, the database master key is re-encrypted with the service master key of the receiving server.

    What I don't like of the above is that I need to use the FORCE option. This is apparently because I have not opened the service master key, but I have not figured out how do that.

    I ran the above on SQL 2008.

    If this does not answer your questions, please fill in steps that I have left out.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by amber zhang Thursday, July 12, 2012 1:44 AM
    Wednesday, July 11, 2012 10:07 PM
  • Dear Erlang,

    Your code worked fine on both servers.

    Thanks for explaining below point:
    Previously I suggested that you have to copy the service key between servers. This is incorrect. When you run RESTORE MASTER KEY, the database master key is re-encrypted with the service master key of the receiving server.

    This is the code I used:

    On server A:

    Create table "Users":
    UserId, , Varchar(10)
    WindowsLogin, nVarchar(50)
    WindowsPassword, nVarchar(50)

    1st SP to Encrypt password:

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

    CREATE PROCEDURE [dbo].[SaveEncryptedPwd]       
                    @UserId AS VARCHAR(10),
                    @WindowsPassword AS VARCHAR(50),
                    @WindowsLogin AS VARCHAR(50)
    AS

    BEGIN

    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

    OPEN SYMMETRIC KEY SSN_Key_01
       DECRYPTION BY CERTIFICATE ExchangePassword;

    UPDATE USERS
    SET WindowsPassword = EncryptByKey(Key_GUID('SSN_Key_01'), @WindowsPassword)
    WHERE UserId = @User;

    END

    2nd SP to Decrypt the Password:

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

    CREATE PROCEDURE [dbo].[ReadDecryptedPwd]    
                    @User AS VARCHAR(10)
    AS

    BEGIN

    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

    OPEN SYMMETRIC KEY SSN_Key_01
       DECRYPTION BY CERTIFICATE ExchangePassword;

    SELECT WindowsLogin,
        CONVERT(varchar(50), DecryptByKey(WindowsPassword)) AS 'WindowsPassword'
        FROM Users
    WHERE userid = @User
    AND WindowsPassword IS NOT NULL

    END

    On Server B:

    I restored the Database containing above 2 Sp's.
    Then run the decrypt sp for a user:

    exec ReadDecryptedPwd 'SomeUser'

    result:
    --------

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

    (1 row(s) affected)

    -------------------------------------------------------------------------------------------


    I got the encrypt/decrypt script and key creation from MSDN exemple on the web.
    I am new in encyption and cannot find a solution for this issue...
    any help is greatly appreciated, tia

    Regards





    Thursday, July 12, 2012 10:46 AM
  • 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 amber zhang Tuesday, July 17, 2012 8:22 AM
    Friday, July 13, 2012 12:44 PM
  • hi,

    just visit the page and decrypt entire sql script decrypt procedure http://www.fixsqlserver.org/sql-script-decrypt-procedure.html

    thanks..

    Sunday, July 15, 2012 9:34 AM
  • 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.

    <p><span style="color:blue">OPEN</span> MASTER <span style="color:blue">KEY</span> DECRYPTION <span style="color:blue">BY</span> PASSWORD = <span style="color:#a31515">'23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'</span>
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY</p><p>it worked but when I run: EXEC ReadDecryptedPwd 'Someuser'<br/>i get the same error...</p>
    

    Dear Erland, (Sorry about Erlang)

    Thanks for your reply.
    I tried runing below on corrupt server:

    OPEN MASTER KEY DECRYPTION BY PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

    It worked fine.

    But I still get the error when I run my Decrypt SP.
    EXEC ReadDecryptedPwd 'SomeUser'

    Any suggestions?

    • Edited by Barabello Tuesday, July 17, 2012 10:27 AM post didnt include my comments
    Tuesday, July 17, 2012 10:23 AM
  • hi,

    just visit the page and decrypt entire sql script decrypt procedure http://www.fixsqlserver.org/sql-script-decrypt-procedure.html

    thanks..

    Hi Admin,

    What does your software does exactly? have you tried it with my issue with success?

    Tuesday, July 17, 2012 10:28 AM
  • Please review below. I have run and tested this on SQL 2005 SP4. If you can't this to run, please review the exact version of SQL 2005 on both servers and make sure that you have Service Pack 4 on both. "SELECT @@version" should return 9.0.5000 or higher. When I tested to restore the database on SQL 2005 SP2, this did not work. (But the error message was different from yours.)

    If you can get my scenario to work, but yours don't, please review your setup and compare to mine to find differences. If you don't see what is going on, please post your complete script.

    This I what I ran on the source server:

    CREATE DATABASE Cryptic
    go
    USE Cryptic
    go
    CREATE TABLE Users (UserId                    varchar(10)    NOT NULL,
                                            WindowsLogin        nvarchar(50) NOT NULL,
                                            WindowsPassword nvarchar(50) NOT NULL)
    go
    INSERT Users (UserId, WindowsLogin, WindowsPassword)
         VALUES ('someuser', 'DOMAIN\someuser', 'Not encrypted')
    go
    CREATE PROCEDURE [dbo].[SaveEncryptedPwd]
                                                 @UserId                    AS varchar(10),
                                                 @WindowsPassword AS nvarchar(50),
                                                 @WindowsLogin        AS nvarchar(50) AS
    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',
                 START_DATE = '20120101';
    
            CREATE SYMMETRIC KEY SSN_Key_01
                    WITH ALGORITHM = AES_256
                    ENCRYPTION BY CERTIFICATE ExchangePassword;
    END
    
    OPEN SYMMETRIC KEY SSN_Key_01    DECRYPTION BY CERTIFICATE ExchangePassword;
    
    UPDATE Users
    SET        WindowsPassword = EncryptByKey(Key_GUID('SSN_Key_01'), @WindowsPassword)
    WHERE    UserId = @UserId;
    
    CLOSE SYMMETRIC KEY SSN_Key_01
    go
    CREATE PROCEDURE [dbo].[ReadDecryptedPwd] @User AS nvarchar(10) AS
    
    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
    
    OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE ExchangePassword;
    
    SELECT WindowsLogin, CONVERT(nvarchar(50), DecryptByKey(WindowsPassword)) AS 'WindowsPassword'
    FROM     Users
    WHERE    UserId = @User
        AND    WindowsPassword IS NOT NULL
    go
    EXEC [SaveEncryptedPwd] 'someuser', 'The cryptic password', NULL
    go
    EXEC ReadDecryptedPwd 'someuser'
    go
    BACKUP DATABASE Cryptic TO DISK = 'C:\temp\cryptic.bak' WITH INIT
    go
    USE tempdb
    go
    DROP DATABASE Cryptic
    [sql]
    
    I copied the backup file and on the target server I ran with success:
    
    [sql]
    RESTORE DATABASE Cryptic FROM DISK = 'C:\temp\cryptic.bak' WITH
    MOVE 'Cryptic' TO 'C:\Temp\Cryptic.mdf',
    MOVE 'Cryptic_log' TO 'C:\Temp\Cryptic_log.ldf',
    REPLACE
    go
    USE Cryptic
    go
    OPEN MASTER KEY DECRYPTION BY
                    PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
    go
    SELECT * FROM sys.symmetric_keys
    go
    EXEC ReadDecryptedPwd 'someuser'
    go
    SELECT * FROM sys.symmetric_keys
    go
    USE tempdb
    go
    DROP DATABASE Cryptic

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, July 17, 2012 1:04 PM
  • I tried your code, But I got an error on the first server (A) (the one where my sp's usualy works) where I created The DB Cryptic.

    Msg 15151, Level 16, State 1, Procedure ReadDecryptedPwd, Line 16
    Cannot find the symmetric key 'SSN_Key_01', because it does not exist or you do not have permission.

    The version on this server (A) is :

    Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    The version on the other server (B) is:

    Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)   Oct 14 2005 00:33:37   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    I will install SP4 on server A and B. then Backup the Database (with encrypt, Decrypt Stored procedures) from server A  then Restore on server B. And try to run to see if it works.

    But I have a question, will the SP4 Upgrade affect anything on server A? it is a production environement and I don't wanna mess with the keys or anything else there.

    Thanks again Erland for your support.

    Wednesday, July 18, 2012 5:48 AM
  • Microsoft SQL Server 2005-  9.00.2047.00(Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988- 2005 Microsoft Corporation Developer Editionon Windows NT 5.2(Build 3790:Service Pack 2)

    That's SQL Server 2005 SP2

    The version on the other server (B) is:

    Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)   Oct 14 2005 00:33:37   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    That's SQL Server 2005 RTM. Or if you like, a late beta version of SQL 2005.

    But I have a question, will the SP4 Upgrade affect anything on server A? it is a production environement and I don't wanna mess with the keys or anything else there.

    Of course, if this is a critical production environment, you should not throw in service packs on a whim. Then again, a service pack is likely to  resolves more issues than it introduces. And specifically, if you would run into a serious problem with your current environment and open a case with Microsoft, it is very likely that MS will ask you to install SP4 before do anything more. However, there is a small risk that you run into plan regressions, or that you happen to be dependent on some incorrect behaviour that has been fixed.

    As with all upgrades, you need to do the amount of testing that you deem critical for your system. If you are running a third-party application on the server, you should consult the vendor to see what they support. If they are serious, they will tell you that you should upgrade to SP4.

    I tried your code, But I got an error on the first server (A) (the one where my sp's usualy works) where I created The DB Cryptic.

    Msg 15151, Level 16, State 1, Procedure ReadDecryptedPwd, Line 16

    Cannot find the symmetric key 'SSN_Key_01', because it does not exist or you do not have permission.

    I can't say that I like this test on key id 101. Overall, you would not put the creation of the keys in to the stored procedure, but that is something that you create in advance.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 18, 2012 9:24 AM