SQL Encryption and Decryption using Symmetric Key

Posts   
 
    
rthakur
User
Posts: 10
Joined: 18-May-2017
# Posted on: 15-Sep-2017 11:12:18   

We are using SQL Encryption and decryption for a field, where the field (THB_File) is declared as varbinary(max) . The encryption is done using symmetric key. I have managed to get decrypted field information back and save encrypted information into the field in LLBLGen. Here are sql queries that I am seeking to achieve: Insert encrypted data:

OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;

INSERT INTO Thumbnail(THB_ID, THB_File)
VALUES (2,ENCRYPTBYKEY(KEY_GUID(N'SymmetricKey1'), 'FileName'))
CLOSE SYMMETRIC KEY SymmetricKey1;

Fetch decrypted data:

OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1

SELECT THB_ID, CONVERT(VARCHAR,DecryptByKey(THB_File)) as 'Decrypted Col2' 
FROM dbo.Thumbnail
WHERE THB_ID = 1

Close SYMMETRIC KEY SymmetricKey1

Here are my LLBLGen implementations: Insert encrypted data:

ThumbnailEntity e = new ThumbnailEntity();
            e.Fields[(int)ThumbnailFieldIndex.ThbFile].ExpressionToApply =
            new DbFunctionCall("EncryptByKey(KEY_GUID('SymmetricKey1'),convert(varchar,{0}))", new object[] { "fileName" });

            UnitOfWork.AddForSave(e);
            using (var adapter = new DataAccessAdapter(true))
            {
                adapter.ExecuteSQL("OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1");
                UnitOfWork.Commit(adapter);
                adapter.ExecuteSQL("CLOSE SYMMETRIC KEY SymmetricKey1");
            }

Fetch Decrypted data:

var decryptedField = new EntityField2("DecryptedColumn", new DbFunctionCall("CAST({0} as varchar(200))",
                        new object[]
                        {
                            new DbFunctionCall("DecryptByKey", new object[] { ThumbnailFields.ThbFile })
                        }));

            var queryFactory = new QueryFactory();
            var queryGetCrewEventType = queryFactory.Thumbnail.Where(ThumbnailFields.ThbId == 1)
                .Select(() => new Thumbnail()
                {
                   Identifier = ThumbnailFields.ThbId.ToValue<string>(),
                   File = decryptedField.ToValue<string>(),
                });

            List<Thumbnail> response;
            using (var adapter = new DataAccessAdapter(true))
            {
                adapter.ExecuteSQL("OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1");
                response = adapter.FetchQuery(queryGetCrewEventType);
                adapter.ExecuteSQL("CLOSE SYMMETRIC KEY SymmetricKey1");

                adapter.CloseConnection();
                adapter.Dispose();
            }

As you can see that I have kept the DataAccessAdapter(true) and managed to use ExecuteSQL to Open and close symmetric keys.

I want to know if there is any better approach to achieve encryption and decryption using symmetric keys. I would also appreciate some pointers on making my LLBLGen implementation better.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 15-Sep-2017 16:52:04   

what's your llblgen pro version? And I recon you don't use sqlserver 2016 with its own built-in column encryption?

Frans Bouma | Lead developer LLBLGen Pro
rthakur
User
Posts: 10
Joined: 18-May-2017
# Posted on: 19-Sep-2017 14:20:24   

Sorry for the late reply. We use the LLBLGEN Pro version 5.1, and SQL Server 2014.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 19-Sep-2017 22:06:41   

I believe you are on the right track and I have nothing to add.

rthakur
User
Posts: 10
Joined: 18-May-2017
# Posted on: 20-Sep-2017 05:59:34   

Thank you for your time. Much appreciated! simple_smile