+ Reply to Thread
Results 1 to 6 of 6

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

  1. 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

  2. Re: Encrytion - EncryptByKey(Key_guid('EncyptKey'),(

    Hi

    How big is the connection string column?

    John

    "Prem" 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




  3. Re: Encrytion - EncryptByKey(Key_guid('EncyptKey'),(

    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


  4. Re: Encrytion - EncryptByKey(Key_guid('EncyptKey'),(

    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" 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
    >




  5. Re: Encrytion - EncryptByKey(Key_guid('EncyptKey'),(

    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...erver/200801/1


  6. Re: Encrytion - EncryptByKey(Key_guid('EncyptKey'),(

    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...erver/200801/1
    >
    >


+ Reply to Thread