locked
Encrypting & Decrypting a field of data type Text RRS feed

  • Question

  •  

    Hello,

     

    I need to encrypt and decrypt a field of data type text. Any ideas?

     

    Thanks,

    Swamy

    Wednesday, August 6, 2008 11:43 PM

Answers

  •   SQL Server EncryptByKey built-in only take binary, varbinary, char, varchar, nchar or nvarchar as plaintext, or data can be explicitly/implicitly converted to any of these data types. It is possible to convert text data type to nvarchar/varchar in order to encrypt it, the risk is that it may cause data loss.

     

    CREATE TABLE [tDemoOriginal]( PtData text)

    CREATE TABLE [tDemoEncrypted]( Cipher varbinary(8000))

    go

    INSERT INTO [tDemoOriginal] values(convert( text, N'test data 1' ))

    INSERT INTO [tDemoOriginal] values(convert( text, N'test data 2' ))

    go

     

    if((SELECT count(*) FROM [tDemoOriginal] WHERE datalength(PtData) > 7900) > 0)

    BEGIN

       PRINT 'WARNING: The table contains data that will be lost during encryption'

       SELECT datalength( PtData ) as 'length', * FROM [tDemoOriginal] WHERE datalength( PtData ) > 7900

    END

    go

     

    INSERT INTO [tDemoEncrypted]

    SELECT encryptByKey(

            key_guid('key1'),

            convert( varchar(8000), PtData ))

        FROM [tDemoOriginal]

    go

     

    SELECT convert(varchar(8000),

              decryptByKey(Cipher))       

        FROM [tDemoEncrypted]

    go

     

    -- This time the data will be beyonf the limit set, and may be lost during the encryption

    INSERT INTO [tDemoOriginal] values(convert( text, REPLICATE( 'abc', 8000)))

    go

     

    if((SELECT count(*) FROM [tDemoOriginal] WHERE datalength(PtData) > 7900) > 0)

    BEGIN

       PRINT 'WARNING: The table contains data that will be lost during encryption'

       SELECT datalength( PtData ) as 'length', * FROM [tDemoOriginal] WHERE datalength( PtData ) > 7900

    END

    go

     

    -- Encrypt by key will return null if it fails to encrypt

    --

    INSERT INTO [tDemoEncrypted]

    SELECT encryptByKey(

            key_guid('key1'),

            convert( varchar(8000), PtData ))

        FROM [tDemoOriginal]

    go

     

    -- See the last row

    --

    SELECT * FROM [tDemoEncrypted]

    SELECT convert(varchar(8000),

              decryptByKey(Cipher))       

        FROM [tDemoEncrypted]

    go

     

    For detailed information on data types please visit BOL:

    ·       CAST and CONVERT (T-SQL) http://msdn.microsoft.com/en-us/library/ms187928.aspx

    ·       Using text and image Data http://msdn.microsoft.com/en-us/library/ms189574.aspx

     

      I hope this information helps,

     

      -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Thursday, August 7, 2008 4:41 AM

All replies

  • Here is a great step-by-step example and overview of encrypting a column in SQL Server 2005.

     

    http://msdn.microsoft.com/en-us/library/ms179331.aspx

    Thursday, August 7, 2008 12:57 AM
  •   SQL Server EncryptByKey built-in only take binary, varbinary, char, varchar, nchar or nvarchar as plaintext, or data can be explicitly/implicitly converted to any of these data types. It is possible to convert text data type to nvarchar/varchar in order to encrypt it, the risk is that it may cause data loss.

     

    CREATE TABLE [tDemoOriginal]( PtData text)

    CREATE TABLE [tDemoEncrypted]( Cipher varbinary(8000))

    go

    INSERT INTO [tDemoOriginal] values(convert( text, N'test data 1' ))

    INSERT INTO [tDemoOriginal] values(convert( text, N'test data 2' ))

    go

     

    if((SELECT count(*) FROM [tDemoOriginal] WHERE datalength(PtData) > 7900) > 0)

    BEGIN

       PRINT 'WARNING: The table contains data that will be lost during encryption'

       SELECT datalength( PtData ) as 'length', * FROM [tDemoOriginal] WHERE datalength( PtData ) > 7900

    END

    go

     

    INSERT INTO [tDemoEncrypted]

    SELECT encryptByKey(

            key_guid('key1'),

            convert( varchar(8000), PtData ))

        FROM [tDemoOriginal]

    go

     

    SELECT convert(varchar(8000),

              decryptByKey(Cipher))       

        FROM [tDemoEncrypted]

    go

     

    -- This time the data will be beyonf the limit set, and may be lost during the encryption

    INSERT INTO [tDemoOriginal] values(convert( text, REPLICATE( 'abc', 8000)))

    go

     

    if((SELECT count(*) FROM [tDemoOriginal] WHERE datalength(PtData) > 7900) > 0)

    BEGIN

       PRINT 'WARNING: The table contains data that will be lost during encryption'

       SELECT datalength( PtData ) as 'length', * FROM [tDemoOriginal] WHERE datalength( PtData ) > 7900

    END

    go

     

    -- Encrypt by key will return null if it fails to encrypt

    --

    INSERT INTO [tDemoEncrypted]

    SELECT encryptByKey(

            key_guid('key1'),

            convert( varchar(8000), PtData ))

        FROM [tDemoOriginal]

    go

     

    -- See the last row

    --

    SELECT * FROM [tDemoEncrypted]

    SELECT convert(varchar(8000),

              decryptByKey(Cipher))       

        FROM [tDemoEncrypted]

    go

     

    For detailed information on data types please visit BOL:

    ·       CAST and CONVERT (T-SQL) http://msdn.microsoft.com/en-us/library/ms187928.aspx

    ·       Using text and image Data http://msdn.microsoft.com/en-us/library/ms189574.aspx

     

      I hope this information helps,

     

      -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Thursday, August 7, 2008 4:41 AM
  • Thanks Raul! That was very helpful.

     

    -- Swamy

    Friday, August 8, 2008 1:37 AM