Using SQL Server Application Roles through C#

I've not had to do this with C# before (did it with Delphi no problem), and found that there is an issue using connection pooling.

The problem is that the connection seems to be in error once a role has been set. The test I performed was as follows :

1. Set approle
2. call a stored procedure to populate a datatable from c#
3. call the stored procedure again.

On the second call to the stored procedure, I got an error whatever I did. If you do not set the approle, you will get a permission denied error. If you set the approle then you get a nasty looking error telling you the connection needs to be reset. Either way doesn't work.

As I am reluctant to use a dedicated connection (for a start this would involve rewriting alot of code, not to mention any performance issues), I needed a proper solution. And here it is :

1. Open connection.
2. exec sp_setapprole
3. run sql
4. exec sp_unsetapprole
5. close connection.

The only tricky bit is that you have to get the @cookie from sp_setapprole and pass it into sp_unsetapprole to free it up. This is a varbinary, so needs to be handled in a byte[] type.

So, pseudo-ish code is

public DataSet getSomeData()
{
connection = getConnection();
connection.Open();
byte[] cookie = SetApprole(connection, approle, approlepassword);
....
call some sql using connection
....

UnsetApprole(connection, cookie);
}

public byte[] SetApprole(SqlConnection connection, string approle, string approlePassword)
{
StringBuilder sqlText = new StringBuilder();

sqlText.Append("DECLARE @cookie varbinary(8000);");
sqlText.Append("exec sp_setapprole @rolename = '" + approle + "', @password = '" + approlePassword + "'");
sqlText.Append(",@fCreateCookie = true, @cookie = @cookie OUTPUT;");
sqlText.Append(" SELECT @cookie");

if (connection.State.Equals(ConnectionState.Closed))
connection.Open();

using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlText.ToString();
cmd.CommandTimeout = commandTimeout;
return (byte[])cmd.ExecuteScalar();
}
}

And the final trick - calling sp_unsetapprole. This gave me issues as it errored with a 15422 (Application roles can only be activated at the ad hoc level) on a CommandType.Text execution. It has to be a CommandType.StoredProcedure to work :

public void UnsetApprole(SqlConnection connection, byte[] approleCookie)
{
string sqlText = "exec sp_unsetapprole @cookie=@approleCookie";

if (connection.State.Equals(ConnectionState.Closed))
connection.Open();

using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_unsetapprole";
cmd.Parameters.AddWithValue("@cookie", approleCookie);

cmd.CommandTimeout = commandTimeout;
cmd.ExecuteNonQuery();
}
}

Remember to add all your favourite try...catch blocks etc.

Bootstrapping GAC in ClickOnce Visual Studio 2005

One of the main shortcomings of ClickOnce is the inability to install GAC related items, and to execute addition installation requirements such as creating registry settings.
To accomplish this you must create a custom Bootstrap which is installed as part of the pre-requisites. This is easier than it sounds. In essence :

Create a project which includes all the references required.
Create an installation project that loads the references into the GAC (and does other pre-requisite actions)
Copy the msi to the boot strapper folder for Visual Studio (e.g. C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages or C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages)
Create a product.xml to identify the msi.

*Update - make sure you create a subfolder to hold the language specific EULA. Without this it will break!

Alter the Prerequisites in the click once publish tab to include this new project.

There are some nice articles on this, not least some example code from dasBlonde.

Also :

Installsite bunch of links
MSDN bootstrap manifest generator

Deploying Office Tools SE with VS 2005 / Creating Custom Bootstrap

I'm in the process of working out how to include Office 2007 PIA/VSTO redistributable packages with my application (Office 2007 Ribbon app). Unfortunately these packages do not appear in the Pre-requisites is VS 2005.

As part of my investigations I stumbled on a nice Custom Bootstrap Manifest Generator written by David Guyer. Looks to be very useful, yet to use but worth bookmarking.

Find it here : Bootstrapper Manifest Generator

Also, I have found a nice article on how to deploy Office 2003/2007 solutions using Windows Installer. It's a 2 part walkthrough.

Of major interest is the Package.xml file for PIA (2003/2007).

I've run through this and it all works - though not exactly how I wanted it to. You have to execute the setup.exe in order to install the .Net 2.0, VSTO and PIA. In is interactive, requiring some clicks on next buttons. I really wanted it to be silent, but I guess as this only needs to be run the once then it is OK. The MSI can be executed silently thereafter to install updates so it's good enough.