asked on

SQL Server Encryption/Decryption

HI,

I have a sql Server 2005 sp4 server, in which on one of database i have encrypted one column(users password) by symmetric key. Now i am moving 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.

What to do and how.

Raja Jegan R

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..

Mandeep Singh

ASKER

Used exactly same script that i ran on first server.

Raja Jegan R

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

Open in new window

Mandeep Singh

ASKER

Hi,

On old server also it gives null result and also null in new server. But on old server application users are able to login. I am using the below script.

drop symmetric key PasswordFieldSymmetricKey
go
drop CERTIFICATE PasswordFieldCertificate
go
drop MASTER KEY
go
-------------------------------------------------------------------------
----------------------------------------------------------------------------
IF NOT EXISTS (
SELECT * FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##'
)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'rajesh@2025'
GO
IF NOT EXISTS (
SELECT * FROM sys.certificates WHERE name = N'PasswordFieldCertificate'
)
CREATE CERTIFICATE PasswordFieldCertificate WITH SUBJECT = 'Password Fields';
GO
CREATE SYMMETRIC KEY PasswordFieldSymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE PasswordFieldCertificate;
Go

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


OPEN MASTER KEY DECRYPTION BY PASSWORD = 'rajesh@2025'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

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


OPEN SYMMETRIC KEY PasswordFieldSymmetricKey                
   DECRYPTION BY CERTIFICATE PasswordFieldCertificate;                
                    
      select Login_Id,User_Name,convert(varchar,DecryptByKey(Password)) as Password,0 from UserLogin

Open in new window

Raja Jegan R

If you are receiving Null in Old Server, can you try checking once whether the password you have provided is correct or not..

Mandeep Singh

ASKER

I've requested that this question be deleted for the following reason:

Resolved

Raja Jegan R

Hi Mandeep,

If your issue is resolved, kindly request you to post the solution and accept your comment as a solution instead of Deleting the question..

ASKER CERTIFIED SOLUTION

Mandeep Singh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mandeep Singh

ASKER

Above mentioned way not worked for me, due to that i have to update password of all users.