title: SQL Server Encryption and nhibernate
body: <p>We have a database that has a few encrypted columns (symetric key as described <a href="http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/" rel="nofollow noreferrer">here</a>). I'm trying to map these tables in a legacy app using using old-school NHybernate 2.1.2.4.</p>
  
  <p>I thought I would be able to map the encrypted columns using a formula, like this:</p>
  
  <pre><code>&lt;property name="Name" column="Name"&gt;
    &lt;formula&gt;
      OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE MyCert
      SELECT CONVERT(NVARCHAR(150), DECRYPTBYKEY(Name)) AS Name FROM [Company] WHERE [Company].[Id] = Id
    &lt;/formula&gt;
  &lt;/property&gt;
  </code></pre>
  
  <p>Unfortunately this does not work since I'm not allowed to use the open statement there.
  I can reproduce the problem in pure SQL by trying this:</p>
  
  <pre><code>SELECT 
      Company.Id, 
        (OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE MyCert 
        SELECT CONVERT(NVARCHAR(150), DECRYPTBYKEY(Name)) FROM [Company] WHERE Id = Company.Id),
      Address
    FROM Company;
  </code></pre>
  
  <p>This gives the same error since Open does not return anything, it's a void statement.</p>
  
  <p>Is there any way to get NHibernate to call Open somewhere else? Maybe just after opening a connection or in the session handling somewhere?</p>
  
  <p>Otherwise, is there a valid SQL syntax to get that statement working inside a query? </p>
  
  <p>Views, functions and stored procedures are not an option since they defeat the purpose of encrypted columns.</p>
  
  <p>---- Edit ----</p>
  
  <p>Doing the Open statement when the session is opened and putting this in the config:</p>
  
  <pre><code>&lt;property name="Name" column="Name" formula="CONVERT(NVARCHAR(150), DECRYPTBYKEY(Name))" /&gt;
  </code></pre>
  
  <p>Seems to fix half of the problem, the field is read-only :-(</p>
  
  <p>By the way, updating the field using plain SQL also gives crazy results. When I do this:</p>
  
  <pre><code>UPDATE Company 
    SET Name = ENCRYPTBYKEY(KEY_GUID('MyKey'), 'Hello World')
    WHERE Id=1000;
  </code></pre>
  
  <p>And then read it back using <code>CONVERT(NVARCHAR(150), DECRYPTBYKEY(Name))</code>, I get some interesting characters '效汬⁯潗汲d' back instead of 'Hello World'.</p>
  
  <p>I guess upgrading our SQL2005 database and using TDE as <a href="https://stackoverflow.com/users/105929/remus-rusanu">Remus Rusanu</a> mentioned is the best option.</p>