Configuring remote database connection strings in SQL CLR Assemblies

Question

I am writing an SQL CLR assembly that will be deployed to a third-party database server, but which must execute stored procedures located on my database server. All parts are in place, but I do not know how to configure the connection string. Such articles: http://msdn.microsoft.com/en-us/library/ms345135(SQL.90).aspx have repeatedly stated that connection strings should not be hardcoded during production (obviously), but do not give any recommendations regarding how to configure the connection string.

Is there a way to deploy the configuration file or otherwise provide configuration options for building SQL CLR 2005?

Answer 1

I would recommend either a configuration table (also suggested by Gratzy), or adding them to the registry and looking there (assuming you start your SQL Server as a proxy domain user, their HKCU is the safest place).

The registry will be my choice, as it does not provide for any schema changes in your database and is less likely to be accidentally / purposefully edited by the client after it is deployed.

Answer 2

Here is an article on Using the application configuration file (app.config / web.config) in SQL Server CLR Integration

I'm not sure how well this works on a third-party instance of Sql Server.