NHibernate version: 1.2
App: Asp.Net 2.0
DB:SQL Server 2000 (or 2005)

Some of the data in the app I'm working on needs to be stored encrypted. It's almost a certainty that we're going to be using XPCrypt (http://www.xpcrypt.com/products.html ) and I'm now faced with the task of getting it running with NHibernate.

Basically, what I need to do is the equivalent of running:

// ** Test code for setting encryption password for current sql server session **
//string conString = ConfigurationManager.ConnectionStrings["sqlServer"].ToString();
//SqlConnection con = new SqlConnection(conString);
//SqlCommand decrypt = new System.Data.SqlClient.SqlCommand("master..xp_crypt_set_var 'keyname','password'", con);

//con.Open();
//decrypt.ExecuteNonQuery();
//con.Close();
// **


At the start of every session (and then again at the start of a new query if the session lasts longer than a SQL Session)

I'm going to start researching how to do this now, but any pointers from anyone who's done something similar, or who has a good idea about a high level approach would be appreciated. Is there a way I can tell NHibernate to do this without altering the NHibernate source?

I'll post on how I do it if I find a nice solution.

One other thing I should ask - does anyone know if it'll be troublesome having NHibernate use a single table view instead of a table for each entity?

Cheers,

Tim.

That seemed so easy I must have overlooked something. I'll hammer it tomorrow, but looking good so far :-)

I created a class MyConnectionProvider:

public class MyConnectionProvider: NHibernate.Connection.DriverConnectionProvider
{
public override IDbConnection GetConnection()
{
IDbConnection connection = base.GetConnection();
IDbCommand command = connection.CreateCommand();
command.CommandText = "exec master..xp_crypt_set_var 'MyKey','MyPassword'";
command.ExecuteNonQuery();
return connection;
}
}

And then specified it in the config:

<add key="hibernate.connection.provider" value="MyAssembly.MyConnectionProvider, MyAssembly" />

Thanks to Ayende for pointing me in the direction of IConnectionProvider.

Cheers,

Tim.