Search code examples
c#sql-serversqlclr

C# SQL CLR cannot read environment variable

I have a CLR dll written in C# that behaves strangely (to me). I successfully created the assembly in SQL Server with permission_set = unsafe and created SQL functions that call the dll's functions. When I run the functions in SQL Server and it gets to the part of the code that looks up an environment variable (the environment variable exists for both system and my user) it is getting null for the value. However, if I take the same code, compile it as an executable, and then run it outside of SQL Server (same computer) it can find the environment variable just fine. If I remove the environment variable portion and just hardcode the value into the DLL everything works as expected. Note: the code opens a socket with an external service and is able to do so even when run in SQL Server so I am fairly confident it isn't a permissions issue with the DLL. The relevant code is below:

string propertiesFile = Environment.GetEnvironmentVariable(ENVIRONMENT_VARIABLE_NAME);

I am using SQL Server Express with SQL Server Management Studio version 12.0.2000.8 on a Windows 7 machine and I did try restarting my SQL Server Management Studio after the environment variable had been created.

Thanks for reading!

Solution

  • Thanks to artm for pointing out the use of targets. When I changed the code to: 

    string propertiesFile = Environment.GetEnvironmentVariable(ENVIRONMENT_VARIABLE_NAME, EnvironmentVariableTarget.Machine);

    everything worked fine.