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.
LVL 6
Mandeep SinghDatabase AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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..
0
Mandeep SinghDatabase AdministratorAuthor Commented:
Used exactly same script that i ran on first server.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mandeep SinghDatabase AdministratorAuthor Commented:
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

0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
If you are receiving Null in Old Server, can you try checking once whether the password you have provided is correct or not..
0
Mandeep SinghDatabase AdministratorAuthor Commented:
I've requested that this question be deleted for the following reason:

Resolved
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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..
0
Mandeep SinghDatabase AdministratorAuthor Commented:
Hi,

I have resolved this by creating master key, certificate and then update all users password. and communicate users about their new password and told to reset them.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mandeep SinghDatabase AdministratorAuthor Commented:
Above mentioned way not worked for me, due to that i have to update password of all users.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.