June 25, 2015 at 9:44 am
Hello All,
I am trying to implement the column encryption on one of the tables, have used the below link as the reference and got stuck at the last step.
http://benjii.me/2010/05/how-to-use-sql-server-encryption-with-symmetric-keys/
I have completed the following steps so far.
- CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘myStrongPassword’
- CREATE CERTIFICATE MyCertificateName
WITH SUBJECT = 'A label for this certificate'
- CREATE SYMMETRIC KEY MySymmetricKeyName WITH
IDENTITY_VALUE = 'a fairly secure name',
ALGORITHM = AES_256,
KEY_SOURCE = 'a very secure strong password or phrase'
ENCRYPTION BY CERTIFICATE MyCertificateName;
- CREATE PROCEDURE OpenKeys
AS
BEGIN
SET NOCCOUNT ON;
BEGIN TRY
OPEN SYMMETRIC KEY MySymmetricKeyName
DECRYPTION BY CERTIFICATE MyCertificateName
END TRY
BEGIN CATCH
-- Handle non-existant key here
END CATCH
END
-CREATE FUNCTION Encrypt
(
@ValueToEncrypt varchar(max)
)
RETURNS varbinary(256)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varbinary(256)
SET @Result = EncryptByKey(Key_GUID('MySymmetricKeyName'), @ValueToEncrypt)
-- Return the result of the function
RETURN @Result
END
- CREATE FUNCTION Decrypt
(
@ValueToDecrypt varbinary(256)
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varchar(max)
SET @Result = DecryptByKey(@ValueToDecrypt)
-- Return the result of the function
RETURN @Result
END
example by using the function
EXEC OpenKeys
-- Encrypting
SELECT Encrypt(myColumn) FROM myTable
-- Decrypting
SELECT Decrypt(myColumn) FROM myTable
when I ran the last command :
-- Decrypting
SELECT Decrypt(myColumn) FROM myTable
I get the following error :
Msg 257, Level 16, State 3, Line 2
Implicit conversion from data type nvarchar to varbinary is not allowed. Use the CONVERT function to run this query.
Edit - where will I use the convert function, in decrypt function or in select statement?
Can some one please help me in this regard?
June 25, 2015 at 10:09 am
You need to use the convert function, have a look at this thread
😎
June 25, 2015 at 10:15 am
I understand that I need to use convert function, but how and where, need a bit more explanation. Hope people wont mind.
June 25, 2015 at 10:38 am
qur7 (6/25/2015)
I understand that I need to use convert function, but how and where, need a bit more explanation. Hope people wont mind.
Did you look at the thread in my post, there is an example there both where and how.
😎
June 25, 2015 at 12:43 pm
I did read the article, and since I am new at SQL Server , so I need a bit more direction. The relevant convert function I see is below from the article you mentioned.
DECLARE @STRXHEX VARCHAR(50) = '0x1234567890abcdef';
SELECT CONVERT(VARBINARY(50),@STRXHEX,1)
Can you be a bit ore descriptive?
June 25, 2015 at 9:55 pm
qur7 (6/25/2015)
Can you be a bit ore descriptive?
I'll give it a try;-)
The error is coming from the Decrypt function when attempting to assign the output of DecryptByKey to the @Result variable, see the code below
😎
-- Decrypting
CREATE FUNCTION Decrypt
(
@ValueToDecrypt varbinary(256)
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varchar(max)
/* Explicit conversion from varbinary to varchar */
SET @Result = CONVERT(VARCHAR(MAX),DecryptByKey(@ValueToDecrypt),1)
-- Return the result of the function
RETURN @Result
END
June 26, 2015 at 8:29 am
Thank you very much. However, I still get the same error. just to recap this is what I did.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123'
..........................................................................................
CREATE CERTIFICATE MyCertificateName
WITH SUBJECT = 'A label for this certificate'
...............................................................................
CREATE SYMMETRIC KEY MySymmetricKeyName WITH
IDENTITY_VALUE = 'a fairly secure name',
ALGORITHM = AES_256,
KEY_SOURCE = 'a very secure strong password or phrase'
ENCRYPTION BY CERTIFICATE MyCertificateName;
......................................................................................
CREATE PROCEDURE OpenKeys
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
OPEN SYMMETRIC KEY MySymmetricKeyName
DECRYPTION BY CERTIFICATE MyCertificateName
END TRY
BEGIN CATCH
-- Handle non-existant key here
END CATCH
END
.....................................................................................................
CREATE FUNCTION Encrypt
(
@ValueToEncrypt varchar(max)
)
RETURNS varbinary(256)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varbinary(256)
SET @Result = EncryptByKey(Key_GUID('MySymmetricKeyName'), @ValueToEncrypt)
-- Return the result of the function
RETURN @Result
END
-- Decrypting
ALTER FUNCTION Decrypt
(
@ValueToDecrypt varbinary(256)
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varchar(max)
/* Explicit conversion from varbinary to varchar */
SET @Result = CONVERT(NVARCHAR(MAX),DecryptByKey(@ValueToDecrypt),1)
-- Return the result of the function
RETURN @Result
END
EXEC OpenKeys
-- Encrypting
SELECT dbo.Encrypt(CardNumber) FROM [Sales].[CreditCard]
-- Decrypting
SELECT dbo.Decrypt(CardNumber) FROM [Sales].[CreditCard]
The error message is as follows when ran the Decrypting
Msg 257, Level 16, State 3, Line 2
Implicit conversion from data type nvarchar to varbinary is not allowed. Use the CONVERT function to run this query.
I am using AdventuresWorks database, creditcard table.
June 29, 2015 at 6:24 am
I am still stuck at this, can any one help?
June 29, 2015 at 6:48 am
well, for me, the question is:
if you created A certificate, why are you not using it.
I've always created a certificate, and then used EncryptByCert and DecryptByCert to handle the routines.
no need to open keys, just call the function.
using your setup, here's an encryption/decryption pair:
SELECT EncryptByCert(Cert_ID('MyCertificateName'), '1234567812345678') AS [Evncalue]
--copy paste that value from above
SELECT CAST(DecryptByCert(Cert_ID('MyCertificateName'), 0xDE5D{snip example: paste the right value})
AS VARCHAR(100)) AS [DecValue]
Lowell
June 30, 2015 at 10:29 am
for a start
you have declared @Result as VARCHAR(MAX) so why are you converting to NVARCHAR(MAX) and so then forcing a further implicit conversion to VARCHAR(MAX)
I'd also suggest checking what the return type is for the EncryptByKey function called in function Encrypt to see if that also needs to be converted.
The error relates to something being converted to VARBINARY so check any assignments to variables of that type
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply