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
>

encrypting a column

We have a SOX requirment that disallows the storing of passwords as plain
text... they must be encrypted in the database. Does SQL Server handle this
natively?Not in 2000. Use Windows authentication or use the MS crypto API / .NET
crypto classes.
SQL Server 2005 will support native encryption and password management
policies for SQL Server logins.
David Portas
SQL Server MVP
--