Decrypt Data From SQL Server 2008 R2

Question

I need some help translating this procedure (see below) to Entity Framework 4.0. Does anyone have any suggestions of how to port this over. The target project includes; Silverlight 4, WCF RIA Services, EF 4.0, SQL Server 2008 R2.

The only requirement I have is that it will need to be placed in the managed code and not in a stored procedure.

    Try 
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cn;
        cmd.CommandType = CommandType.Text;
        string sql = "OPEN SYMMETRIC KEY " + this._encryptKey;
        sql += " DECRYPTION BY CERTIFICATE " + this._encrpytCert; 
        sql += " SELECT TOP (1) CONVERT(nvarchar(50),DECRYPTBYKEY(Field1)) AS Name";
        sql += " FROM Table3"; 
        sql += " ORDER BY CONVERT(nvarchar(50),DECRYPTBYKEY(Field1))";
        cmd.CommandText = sql;
        Name = (String)cmd.ExecuteScalar();
        bRtn = false;
    }
        catch (Exception ex)
    {
        System.Diagnostics.Debug.Print(ex.ToString());
    }

Please let me know how I should set this up and thanks!

Oh, I'm sorry. I didn't know I was shouting. I'm new here. If you tell me please, how I was shouting I'll be careful not to do it again. Thanks! — Dave Navarro, Nov 09 '11 at 04:28
all caps in the title - i edited it for you. i don't have any good tips for your answer, other than i'm not sure why you can use a stored proc. — Jason, Nov 09 '11 at 04:56
Ok, thanks. I'll use proper case in the subject next time. As for the use of sprocs; for extra security reasons we want to keep the code that accesses the data tables outside of the database so that if the database was reviewed by someone unauthorised they would not know which tables and keys are used to provide the data. There's no way to 'map back' by reviewing sprocs. Thanks again for your help. — Dave Navarro, Nov 09 '11 at 05:31
Try looking at http://codingheadache.com/?tag=entity-framework. Hope it helps. — Richard Schneider, Nov 09 '11 at 06:54
Hello, Thanks for the reply. The link certainly has a lot of great information but what it's missing is how to translate / replicate the command text; cmd.CommandType = CommandType.Text; string sql = "OPEN SYMMETRIC KEY " + this._encryptKey; sql += " DECRYPTION BY CERTIFICATE " + this._encrpytCert; I havn't been able to find examples of someone writing T-SQL code in a Domain Service Class that handles the decryption key as shown above. Please let me know if you've seen any examples that demonstrate how to implement code like this. Thanks! ~ Dave — Dave Navarro, Nov 09 '11 at 14:44
Hello, Thanks again for the replies and links. I was able to get something working however I'm actually surprised it works because it seems very primitive. Since the data is read only and I only need to get it once (when the user logs into the app). I figured out that I could still use T-SQL commands in my method... so, I did just that. Here's a link to another forum post that shows the details; http://forums.silverlight.net/p/242102/604401.aspx/1?Re+Decryption+By+Certificate Thanks again for the replies! ~ Dave — Dave Navarro, Nov 10 '11 at 16:59

score 1 · Answer 1 · answered Mar 16 '13 at 03:15

You could run the query via the Entity Framework and get strongly typed results by using the ObjectContext.ExecuteStoreQuery<>() function (see this example).

Unfortunately, however, I don't think there's any way to get around having to generate the T-SQL statement yourself. While you can use many of SQL Server's functions in Linq-to-Entities queries via the SqlFuntions class, there is no function that translates SQL Server's DECRYPTBYKEY function, not to mention the fact that the Entity Framework won't generate a statement to open the key.

score 0 · Answer 2 · edited Jun 19 '14 at 23:51

To decrypt before querying sensitive data, all you need to do is to use a DbTransaction in the EF ObjectContext connection.

I.e.

connection.Open();
connection.BeginTransaction();
execute your "OPEN SYMMETRIC KEY..." command or stored procedure with ExecuteStoreCommand execute your sensitive data queries, stored procedures etc.
Commit or Rollback you transaction if required

This forces EF to maintain the same db connection because you have started a db transaction and it makes sense because you might execute a whole bunch of SP's as part of the same db transaction.

Decrypt Data From SQL Server 2008 R2

2 Answers2