Encrypt/Decrypt issue

Question

Encrypt/Decrypt issue

mtz676

SSCrazy Eights

Points: 9176
More actions
March 12, 2021 at 10:12 am

#3856880

Version :Microsoft SQL Server 2019 (RTM-GDR)
Followed these links as underneath:
https://www.mssqltips.com/sqlservertip/2431/sql-server-column-level-encryption-example-using-symmetric-keys/
https://www.surekhatech.com/blog/column-level-encryption-and-decryption-in-mssql
I created 2 tables.
For the first table I inserted individual rows (with encryption as in the links) and when I did a decrypted select.The result came out perfectly.No issues.The result showed the original password column,encrypted password column and then the decrypted password column.
The other table I imported excel data with ~2000 rows.Has a password(needs to be encrypted) .I added an extra column(varbinary(max)) which will carry encrypted data (encryted password values)and ran the update as in the above links.Data became encrypted.But when I am trying to read the data using a decrypted select the decrypted column only shows the 1st character of the password column. Not sure whats happening here.Any advice. Thanks

March 14, 2021 at 6:44 pm

This was removed by the editor as SPAM
March 14, 2021 at 7:19 pm

This was removed by the editor as SPAM
March 15, 2021 at 3:49 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24689 More actions · Answer 1

Rather than having us jump through the blogs and guess on the syntax for your UPDATE statement (and the probable typo), could you post the TSQL you used to do the UPDATE?

Also, to confirm, when you pulled the data from Excel to SQL Server, the data looked correct (ie the unencrypted password column contained the full password, not just the first character)?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

mtz676 SSCrazy Eights Points: 9176 More actions · Answer 2

Hello @Brian,

Method1 :This works:
Data is encrypted upon insert.
USE MyDB;
GO
OPEN SYMMETRIC KEY SK1
DECRYPTION BY CERTIFICATE C1;
GO

INSERT into 
[dbo].[SQLCredentials] 
([Server],
[Password], 
[Password_Encrypted]) 
VALUES 
(N'ABCXYZ ',N'sdf^jh&k*',ENCRYPTBYKEY(key_guid('SK1'),'sdf^jh&k*'))
GO
CLOSE SYMMETRIC KEY SK1;
GO

USE MyDB;
GO
OPEN SYMMETRIC KEY SK1
DECRYPTION BY CERTIFICATE C1;
GO
SELECT Server,[Password],[Password_Encrypted] AS 'EncryptedPwd',
CONVERT(varchar, DecryptByKey([Password_Encrypted])) AS 'DecryptedPwd'
FROM [MyDB].[dbo].[SQLCredentials] order by Server
CLOSE SYMMETRIC KEY SK1;
GO
The above individual encrypt inserts and decrypt selects work.
*********************************************************************
Method2: Does not work
Inserted data from the excel worksheet into [dbo].[SQLCredentials].
Then added an extra column Password_Encrypted - varbinary(MAX) to store encrypted password data from Password column as underneath.

USE MyDB;
GO
-- Opens the symmetric key for use
OPEN SYMMETRIC KEY SK1
DECRYPTION BY CERTIFICATE C1;
GO
UPDATE [dbo].[SQLCredentials]
SET [Password_Encrypted] = EncryptByKey(Key_GUID('SK1'),[Password])
FROM [dbo].[SQLCredentials];
GO
-- Closes the symmetric key
CLOSE SYMMETRIC KEY SK1;
GO

But when I try to read back it shows only the first character from the password column.

USE MyDB;
GO
OPEN SYMMETRIC KEY SK1
DECRYPTION BY CERTIFICATE C1;
GO
SELECT Server,[Password],[Password_Encrypted] AS 'EncryptedPwd',
CONVERT(varchar, DecryptByKey([Password_Encrypted])) AS 'DecryptedPwd'
FROM [MyDB].[dbo].[SQLCredentials] order by Server
CLOSE SYMMETRIC KEY SK1;
GO

Thanks

This reply was modified 3 years, 1 month ago by mtz676.

frederico_fonseca SSCoach Points: 16011 More actions · Answer 3

CONVERT(varchar, DecryptByKey([Password_Encrypted])) AS 'DecryptedPwd'

first - NEVER use any data type without specifying its type

decryptbykey returns a varbinary(8000) - so convert it to a varchar(8000) or varchar(max)

second - if the bytes returned contain a hex(00) it will not show up on ssms - and many programs will also "eat" anything after it.

so to confirm it is returning something do a display of the returned binary value as a string of hex values so you can confirm it is indeed returning something and not just 1 char.