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
>