March 12, 2021 at 10:12 am
Version :Microsoft SQL Server 2019 (RTM-GDR)
Followed these links as underneath:
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 12, 2021 at 5:51 pm
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.
March 12, 2021 at 10:48 pm
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
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 14, 2021 at 8:15 pm
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.
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