Azure SQL Trigger Cant use Enviroment Variable as connectionstring

@henrik johansen

Welcome to the Microsoft Q&A Platform!

Thank you for reaching out about Azure SQL Trigger Can't use Environment Variable as connectionstring.

The error you're encountering (CS0182: an attribute must be a constant expression) occurs because in C#, attributes require constant values at compile time. The Environment.GetEnvironmentVariable() method returns a runtime value, which is not a constant.
You can’t pass the connection string as an environment variable directly within the attribute.

Instead of using the environment variable in the attribute, you can pass the connection string dynamically within the code by setting up the connection string at runtime.

For example, try to modify your function to retrieve the connection string inside the method:

[Function("WarningTrigger")]
public async Task Run(
    [SqlTrigger("[dbo].[SensorWarnings]")] IReadOnlyList<SqlChange<Model.SensorWarning>> changes,
    FunctionContext context)
{
    string connectionString = Environment.GetEnvironmentVariable("dbconnection");
    
    // Now use the connection string for your SQL query logic
    SensorDatabaseQuery query = new SensorDatabaseQuery(connectionString);
    await query.GetData();
}

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Hello henrik johansen,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you are having issues with Azure SQL Trigger while using environment variable as connection String.

Yes, the solution provided by @Khadeer Ali effectively addresses the issue by suggesting a method to retrieve the connection string at runtime within the function, which avoids the compile-time constant requirement.

For better approach to ensures that the connection string is managed securely and can be easily tested and maintained., this is an alternative method to address the issue of using environment variables for sensitive data in SQL triggers, by using dependency injection (DI). DI allows you to manage configuration and environment variables securely, making your code more modular and testable.

Steps you will need to do to Implement Dependency Injection in C#

1. Create an interface that outlines the methods your dependency will implement.

   public interface ISensorDatabaseQuery
   {
       Task GetData();
   }

2. Create a class that implements this interface.

   public class SensorDatabaseQuery : ISensorDatabaseQuery
   {
       private string _connectionString;
       public SensorDatabaseQuery(string connectionString)
       {
           _connectionString = connectionString;
       }
       public async Task GetData()
       {
           // Implementation for fetching data
       }
   }

3. In your Startup class, register the services with the dependency injection container.

   public class Startup
   {
       public void ConfigureServices(IServiceCollection services)
       {
           services.AddSingleton<ISensorDatabaseQuery>(provider =>
           {
               var connectionString = Environment.GetEnvironmentVariable("dbconnection");
               return new SensorDatabaseQuery(connectionString);
           });
       }
   }

4. Modify your Azure function to accept the dependency via constructor injection.

   [Function("WarningTrigger")]
   public async Task Run(
       [SqlTrigger("[dbo].[SensorWarnings]")] IReadOnlyList<SqlChange<Model.SensorWarning>> changes,
       FunctionContext context,
       ISensorDatabaseQuery query)
   {
       await query.GetData();
   }

By doing the above, your classes are less dependent on specific implementations, making it easier to change or replace dependencies. You can easily mock dependencies for unit testing and your code is cleaner and easier to maintain.

For more detailed information, you can refer to the documentation on Dependency Injection - https://zcusa.951200.xyz/en-us/dotnet/core/extensions/dependency-injection

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.