CODEWITHSUNDEEP
c#silverlight-4.0entity-framework-4sql-server-2008-r2encryption-symmetric
Dave Navarro
Dave Navarro

Reputation: 31

Decrypt Data From SQL Server 2008 R2

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!

Upvotes: 3

Views: 1412

Answers (2)

Thyago
Thyago

Reputation: 11

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.

Upvotes: 0

Ryan
Ryan

Reputation: 2998

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.

Upvotes: 1

Related Questions