SQL Server 2012 SqlCLR reads configurations from machine.config instead of sqlservr.exe.config

Question

I have a SQLCLR project deployed to SQL Server 2012, the project reads some app settings from the configuration file, the configuration file for sql must be inside the \Binn folder of the sql root folder, but it fails to read the settings from there and instead reads the configurations from the machine.config file, and when I tried to add the system.configuration to the sql assemblies I am getting the below error:

The assembly name 'System.Configuration' being registered has an illegal name that duplicates the name of a system assembly

Is there anything that I am missing there?

Aran

Answer 1

Ok - as a solution you can connect to the same database as the one that your function is deployed to, using the following code I connect to the database and read the settings that I want from the database.

 using (SqlConnection conn = new SqlConnection(**"context connection = true"**))
        {            
            conn.Open();
            SqlCommand cmd = new SqlCommand("SELECT Value FROM GlobalConfigSettings WHERE ID = " + ConstFynixConnectionStringId, conn);
            SqlDataReader dr = cmd.ExecuteReader();
            while(dr.Read())
            {
                fynixConnString = dr["Value"].ToString();
            }
            conn.Close();                  
        }