Encrytion - EncryptByKey(Key_guid('EncyptKey'),(

I am trying to encrypt more 30 chars of plaintext with TRIPLE_AES, but SQL
gives me errors or autmatically truncates my ciphertext/plaintext to 30
plaintext characters.
This particular SQL Statement .
UPDATE tblDB
SET ConnectionString = EncryptByKey(Key_guid('EncyptKey'),('Example
connection string 33333333333333333333'))
WHERE Label = @.varLabel
The 30 character result I get is:
select * from tblDB
Result - '"Example connection string 33"
Does anyone have some suggestions? Thanks,
PremHi
How big is the connection string column?
John
"Prem" <u33747@.uwe> wrote in message news:7e4fff8cd40ad@.uwe...
>I am trying to encrypt more 30 chars of plaintext with TRIPLE_AES, but SQL
> gives me errors or autmatically truncates my ciphertext/plaintext to 30
> plaintext characters.
> This particular SQL Statement .
>
> UPDATE tblDB
> SET ConnectionString = EncryptByKey(Key_guid('EncyptKey'),('Example
> connection string 33333333333333333333'))
> WHERE Label = @.varLabel
> The 30 character result I get is:
> select * from tblDB
> Result - '"Example connection string 33"
> Does anyone have some suggestions? Thanks,
>
> Prem|||The connection string column is VarBinary 500
John Bell wrote:
>Hi
>How big is the connection string column?
>John
>>I am trying to encrypt more 30 chars of plaintext with AES_256, but SQL
>> gives me errors or autmatically truncates my ciphertext/plaintext to 30
>[quoted text clipped - 15 lines]
>> Prem
--
Message posted via http://www.sqlmonster.com|||Hi
This works fine!
CREATE SYMMETRIC KEY EncyptKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
GO
OPEN SYMMETRIC KEY EncyptKey
DECRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
GO
DECLARE @.encrypted varbinary(500)
SET @.encrypted = EncryptByKey(Key_guid('EncyptKey'),('Example connection
string 33333333333333333333'))
SELECT @.encrypted AS EncryptedValue, datalength(@.encrypted) As length,
CONVERT(varchar(100), DecryptByKey(@.encrypted))
AS 'Decrypted Value'
GO
CLOSE SYMMETRIC KEY EncyptKey
GO
DROP SYMMETRIC KEY EncyptKey
GO
How are you decrypting it?
John
"Prem via SQLMonster.com" <u33747@.uwe> wrote in message
news:7e5b1c9d8d930@.uwe...
> The connection string column is VarBinary 500
> John Bell wrote:
>>Hi
>>How big is the connection string column?
>>John
>>I am trying to encrypt more 30 chars of plaintext with AES_256, but SQL
>> gives me errors or autmatically truncates my ciphertext/plaintext to 30
>>[quoted text clipped - 15 lines]
>> Prem
> --
> Message posted via http://www.sqlmonster.com
>|||HI John
I appreciate your help.
I had to change my code like this
select convert(varchar(400), decryptbykey(test)) from dbtest
varchar(400) - did the trick. earlier i was just using varchar.
Thanks again !
John Bell wrote:
>Hi
>This works fine!
>CREATE SYMMETRIC KEY EncyptKey
>WITH ALGORITHM = TRIPLE_DES
>ENCRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
>GO
>OPEN SYMMETRIC KEY EncyptKey
>DECRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
>GO
>DECLARE @.encrypted varbinary(500)
>SET @.encrypted = EncryptByKey(Key_guid('EncyptKey'),('Example connection
>string 33333333333333333333'))
>SELECT @.encrypted AS EncryptedValue, datalength(@.encrypted) As length,
>CONVERT(varchar(100), DecryptByKey(@.encrypted))
>AS 'Decrypted Value'
>GO
>CLOSE SYMMETRIC KEY EncyptKey
>GO
>DROP SYMMETRIC KEY EncyptKey
>GO
>How are you decrypting it?
>John
>> The connection string column is VarBinary 500
>>Hi
>[quoted text clipped - 8 lines]
>> Prem
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1|||Hi
I realised after I posted that the issue was probably in your verification
process and that somewhere you had not given a length to the variable you
were using and therefore it defaulted to 30 characters.
I guess from now on you will know that it is best practice to explicitly
give a length to any variable length data type when declaring or casting.
John
"Prem via SQLMonster.com" wrote:
> HI John
> I appreciate your help.
> I had to change my code like this
> select convert(varchar(400), decryptbykey(test)) from dbtest
> varchar(400) - did the trick. earlier i was just using varchar.
> Thanks again !
> John Bell wrote:
> >Hi
> >
> >This works fine!
> >CREATE SYMMETRIC KEY EncyptKey
> >
> >WITH ALGORITHM = TRIPLE_DES
> >
> >ENCRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
> >
> >GO
> >
> >OPEN SYMMETRIC KEY EncyptKey
> >
> >DECRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
> >
> >GO
> >
> >DECLARE @.encrypted varbinary(500)
> >
> >SET @.encrypted = EncryptByKey(Key_guid('EncyptKey'),('Example connection
> >string 33333333333333333333'))
> >
> >SELECT @.encrypted AS EncryptedValue, datalength(@.encrypted) As length,
> >
> >CONVERT(varchar(100), DecryptByKey(@.encrypted))
> >
> >AS 'Decrypted Value'
> >
> >GO
> >
> >CLOSE SYMMETRIC KEY EncyptKey
> >
> >GO
> >
> >DROP SYMMETRIC KEY EncyptKey
> >
> >GO
> >
> >How are you decrypting it?
> >
> >John
> >
> >> The connection string column is VarBinary 500
> >>Hi
> >[quoted text clipped - 8 lines]
> >>
> >> Prem
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1
>sql

Encrytion - EncryptByKey(Key_guid('EncyptKey'),(

I am trying to encrypt more 30 chars of plaintext with TRIPLE_AES, but SQL
gives me errors or autmatically truncates my ciphertext/plaintext to 30
plaintext characters.
This particular SQL Statement .
UPDATE tblDB
SET ConnectionString = EncryptByKey(Key_guid('EncyptKey'),('Example
connection string 33333333333333333333'))
WHERE Label = @.varLabel
The 30 character result I get is:
select * from tblDB
Result - '"Example connection string 33"
Does anyone have some suggestions? Thanks,
Prem
Hi
How big is the connection string column?
John
"Prem" <u33747@.uwe> wrote in message news:7e4fff8cd40ad@.uwe...
>I am trying to encrypt more 30 chars of plaintext with TRIPLE_AES, but SQL
> gives me errors or autmatically truncates my ciphertext/plaintext to 30
> plaintext characters.
> This particular SQL Statement .
>
> UPDATE tblDB
> SET ConnectionString = EncryptByKey(Key_guid('EncyptKey'),('Example
> connection string 33333333333333333333'))
> WHERE Label = @.varLabel
> The 30 character result I get is:
> select * from tblDB
> Result - '"Example connection string 33"
> Does anyone have some suggestions? Thanks,
>
> Prem
|||The connection string column is VarBinary 500
John Bell wrote:[vbcol=seagreen]
>Hi
>How big is the connection string column?
>John
>[quoted text clipped - 15 lines]
Message posted via http://www.droptable.com
|||Hi
This works fine!
CREATE SYMMETRIC KEY EncyptKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
GO
OPEN SYMMETRIC KEY EncyptKey
DECRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
GO
DECLARE @.encrypted varbinary(500)
SET @.encrypted = EncryptByKey(Key_guid('EncyptKey'),('Example connection
string 33333333333333333333'))
SELECT @.encrypted AS EncryptedValue, datalength(@.encrypted) As length,
CONVERT(varchar(100), DecryptByKey(@.encrypted))
AS 'Decrypted Value'
GO
CLOSE SYMMETRIC KEY EncyptKey
GO
DROP SYMMETRIC KEY EncyptKey
GO
How are you decrypting it?
John
"Prem via droptable.com" <u33747@.uwe> wrote in message
news:7e5b1c9d8d930@.uwe...
> The connection string column is VarBinary 500
> John Bell wrote:
> --
> Message posted via http://www.droptable.com
>
|||HI John
I appreciate your help.
I had to change my code like this
select convert(varchar(400), decryptbykey(test)) from dbtest
varchar(400) - did the trick. earlier i was just using varchar.
Thanks again !
John Bell wrote:[vbcol=seagreen]
>Hi
>This works fine!
>CREATE SYMMETRIC KEY EncyptKey
>WITH ALGORITHM = TRIPLE_DES
>ENCRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
>GO
>OPEN SYMMETRIC KEY EncyptKey
>DECRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
>GO
>DECLARE @.encrypted varbinary(500)
>SET @.encrypted = EncryptByKey(Key_guid('EncyptKey'),('Example connection
>string 33333333333333333333'))
>SELECT @.encrypted AS EncryptedValue, datalength(@.encrypted) As length,
>CONVERT(varchar(100), DecryptByKey(@.encrypted))
>AS 'Decrypted Value'
>GO
>CLOSE SYMMETRIC KEY EncyptKey
>GO
>DROP SYMMETRIC KEY EncyptKey
>GO
>How are you decrypting it?
>John
>[quoted text clipped - 8 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200801/1
|||Hi
I realised after I posted that the issue was probably in your verification
process and that somewhere you had not given a length to the variable you
were using and therefore it defaulted to 30 characters.
I guess from now on you will know that it is best practice to explicitly
give a length to any variable length data type when declaring or casting.
John
"Prem via droptable.com" wrote:

> HI John
> I appreciate your help.
> I had to change my code like this
> select convert(varchar(400), decryptbykey(test)) from dbtest
> varchar(400) - did the trick. earlier i was just using varchar.
> Thanks again !
> John Bell wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200801/1
>

EncryptByKey Function always returning null

When I use EncryptByKey function to encrypt text using AES 128 bit key, I get always null result. this is how I do the encryption:

ALTER PROCEDURE [dbo].[ProcMyProc](@.ClearText nvarchar(50))
AS
BEGIN

OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD= 'MY_Password_128';

Declare @.Temp varbinary(8000);

Set @.Temp =EncryptByKey(Key_GUID('MyKey'),@.ClearText);

close symmetric key MyKey;

select @.Temp as temp;
END

The result I get for this procedure is null. Is there something wrong with this code?

Hey Muhammad,

I tried out your code and it seems to work for me. Can you try executing your script outside of the proc? You can add in "select * from sys.openkeys" after your open statement just to check to see if the key was actually opened.

Sung

|||

Also, if this still doesn't work, can you let us know what OS version you are using? Is it XP, 2000, or 2003?

Thanks
Laurentiu

|||I found the cause of the problem. I was misspelling the name of the key when passing it to the Key_GUID() function. but shouldn't this give an error saying there's no key with this name instead of just returning null?|||

Hi Muhammad,

The way our built-ins work is that they return null instead of returning an error. I don't remember why exactly this is the case, but I think it has to do with backwards compatibility and parsing logic of the built-ins.

Sung