I'm writing a SQL CLR assembly that will be deployed to a third-party database server, but which needs to execute stored procedures residing in my database server. All the pieces are in place, but I don't know how to make the connection string configurable. Articles such as this: http://msdn.microsoft.com/en-us/library/ms345135(SQL.90).aspx repeatedly assert that connection strings should not be hardcoded in production (obviously), but give no suggestions for how to make the connection string configurable.
Is there any way to deploy a settings file or otherwise provide configuration settings to a SQL CLR 2005 assembly?
Best Answer
I'd recommend either a configuration table (also suggested by Gratzy) or adding them to the registry and looking them up there (assuming your running your SQL Server as a proxy domain user, their HKCU is the safest place).
The registry would be my choice since it doesn't involve any schema changes to your database, and is less likely to be accidentally/purposefully edited by the client once it's deployed.