Accessing Application Configuration Settings from SQL CLR

In some (most?) cases we don’t want to hard-code connection details. In this case it means the path to the web service. Imagine a situation where you are consuming a web service that you’ve developed yourself, or another team in your company. And the web service is running in several environments: development, acceptance, production. Of course we don’t want to change our code and recompile each time we need to deploy to a different environment. So we need a way to configure the web service location.

The answer here lies in the sqlservr.exe.config file. This is a file like any other .config file, with a possible content like this:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="MyWebService" value="http://MyWebServiceServer:MyWSPort/PathTo/MyWebService.asmx" />
  </appSettings>
</configuration>

This file needs to be put in the same folder as your sqlservr.exe that’s running your SQL Server instance.  By default this path is C:\Program Files\Microsoft SQL Server\MSSQL.1\{MSSQL10_50.MSSQLSERVER}\Binn\.  When you restart your SQL Server instance while this file is in that folder, SQL Server will pick up any configuration settings from that file.  These can then be accessed from your custom stored procedure in this way:

MyWebService myWS = new MyWebService();
myWS.Url = ConfigurationManager.AppSettings["MyWebService"];
myWS.MyWebMethod();

One of the issues I encountered (a bug) is if the first call through ConfigurationManager is to the static AppSettings method an error is returned indicating it couldn't read the key value. That error is:

"The value of the property 'key' cannot be parsed. * The error is: Request failed. (sqlservr.exe.Config line X)".

Now, the line that causes this exception is:

ConfigurationManager.AppSettings["MyWebService"]

There’s absolutely nothing wrong with that line of code. And, there’s nothing wrong with the configuration file.

So, after some debugging, I found that if I make some “dummy” call through ConfigurationManager to another method, then I can make the call to AppSettings with absolutely no issue. So, the following line is added just to provide a kludge of a workaround :

int firstcall = ConfigurationManager.ConnectionStrings.Count;

Now, this line does nothing other than initialize ConfigurationManager and force a de-serialization of the configuration file.

int firstcall = ConfigurationManager.ConnectionStrings.Count;

MyWebService myWS = new MyWebService();
myWS.Url = ConfigurationManager.AppSettings["MyWebService"];
myWS.MyWebMethod();