Home » Blog » Topic » Encryption Decryption in SQL Server 2005

Encryption Decryption in SQL Server 2005

Andrew Jackson ~ Modified: September 4th, 2015 ~ ~ 1 Minute Reading

Home › Forums › Encryption Decryption in SQL Server 2005

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • September 4, 2015 at 12:16 pm #579 Score: 0
    Andrew Jackson
    Moderator
    1 pt

    I have encrypted one column by symmetric key in SQL Server 2005 sp4 server, Now i want to move this database from this server to another SQL Server 2008 r2. but application is not able to login users.

    I have recreated masterkey, symmetric key and certificate on new server but still not able to decrypt the password column.

    September 5, 2015 at 12:31 pm #583 Score: 0
    Stephen West
    Moderator
    4 pts

    Can you make the below verification to see whether your encryption and decryption works correctly or not..

    In Old Server

    OPEN SYMMETRIC KEY [key_name] DECRYPTION BY CERTIFICATE key_cert;
GO
SELECT encryptbykey(key_guid('key_name'), 'TestData')
GO

    In New Server

    OPEN SYMMETRIC KEY [key_name] DECRYPTION BY CERTIFICATE key_cert;
GO
DECLARE @blob varbinary(8000);
SET @blob = 'output from earlier select statement'
SELECT CONVERT(varchar(8000), decryptbykey(@blob));
GO

    Close symmetric keys in both servers

    CLOSE SYMMETRIC KEY [key_name];
GO
    September 10, 2015 at 10:37 am #626 Score: 0
    Lincoln Burrows
    Moderator
    16 pts

    Kindly confirm whether you have passed the same KEY_SOURCE, ALGORITHM and IDENTITY_VALUE for creating the symmetric key in the new server.
    If they are exactly the same values, then ideally it should work. If not, then try dropping the symmetric key and try creating it again with the values that are used in the first server.

    September 14, 2015 at 11:54 am #647 Score: 0
    Andrew Jackson
    Moderator
    1 pt

    These seems to have helped me. Thanks for the idea.

  • Author
    Posts
Viewing 4 posts - 1 through 4 (of 4 total)
  • You must be logged in to reply to this topic.