December 25, 2011 at 9:45 pm
ALTER PROCEDURE [dbo].[spGetDecryptDataTes]
(
@caseid varchar(max)
)
AS
BEGIN
DECLARE @hexstr varchar(max), @bin varbinary (4000)
--SET @hexstr = @caseid--'0x1234abcdef'
EXEC usp_hexstrtovarbin @caseid, @bin OUTPUT
SELECT @bin
declare @vb varbinary(max)
set @vb = convert(varbinary(max),@bin)
OPEN SYMMETRIC KEY LinkFieldSymmetricKey
DECRYPTION BY CERTIFICATE LinkFieldCertificate;
--select CONVERT(VARCHAR(20),DECRYPTBYKEY(convert(varbinary(max),@bin,1))) AS PASSWORD
SELECT CONVERT(varchar, DecryptByKey(EncryptByKey(Key_GUID('LinkFieldSymmetricKey'), @bin))) as test
SELECT DecryptByKey(@vb)
--exec spGetDecryptDataTes '0x00D25D1B005BBC4E86FD45C7C3DDCC3701000000ED7007675C13943EC40EDF3E0BD14055110EBFE82A69C38F53DC908223CA472D'
END
i have tried all possible ways that i could think of...but my decryptbykey returns a null value.
Please Help
December 25, 2011 at 11:05 pm
Are you sure this encrypted data represent good data (not NULL value)? Please try it with another encrypted value.
December 25, 2011 at 11:21 pm
Yep....any indea how to decrypt through a stored procedure? m totally out of ideas 🙁 Please help
December 26, 2011 at 12:06 am
Try following...
CONVERT(nvarchar, DecryptByKey(@caseid)) AS test
For More: http://msdn.microsoft.com/en-us/library/ms181860.aspx
December 26, 2011 at 1:17 am
Please notice ‘Step 11 - Accessing the Encrypted Data’ in the article given below.
A user need to have permission to symmetric key and certificate to decrypt data, if they still try to decrypt then they will receive null for encrypted values.
December 28, 2011 at 2:29 pm
I'm not the sharpest tool in the shed, but does this have anything to do with it?
You convert it to varchar. Varchar by itself limits it to 10 characters by default, if you don't give it a size. If the outcome has length greater than 10, it may not be able to return it, thereby just returning null. Would making the varchar something bigger like varchar(2000) work better?
December 28, 2011 at 2:30 pm
cont'd:
I know that the result of a DECRYPTBYKEY call is a varbinary value, that's why you have to convert it to varchar.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply