Cant connect to Azure SQL from Azure function

Question

I opened Azure Function and wanted to connect it to Azure SQL with entity frameworks (not core) code first approach, and I cant seem to connect them successfully

I have tryied 2 conventions in my local.settings.json file

1:

"Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true",
    "AzureWebJobsDashboard": "UseDevelopmentStorage=true",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet",
    "sqldb_connection": "{myConnectionString}"
  },

2:

,
  "ConnectionStrings": {
    "BankContext": "{myConnectionString}"
  }

• BankContext is the name of context model

Fuether more, when im debugging it it shows me another connection string to my local MSSQL server.

Thanks alot!

Answer 1

You need to provide more code details on how you are setting up your DbContext to have a more personalized answer, but I will give you the following tips:

1. Use DI as you would use in a normal .NET project with Microsoft's built-in DI container. You would need Microsoft.Azure.Functions.Extensions.DependencyInjection To do so, use the following configuration:

    [assembly: FunctionsStartup(typeof(Startup))]
   
    namespace YOUR_NAME_SPACE
    {
        public class Startup : FunctionsStartup
        {
            public override void Configure(IFunctionsHostBuilder builder)
            {
                string SqlConnection = 
                    Environment.GetEnvironmentVariable("AZURE_SQL_CONNECTIONSTRING");
   
                builder.Services.AddDbContext<PeopleDbContext>(
                    options => options.UseSqlServer(SqlConnection));
            }
        }
    }
   

• Make sure you have AZURE_SQL_CONNECTIONSTRING locally in local.settings.json

2. Make sure your Azure environment is synced, otherwise, this will crash. To do so make sure to include the environment variable on the app service environment variables in the portal

3. Now you can use Dependency Injection to request your context, services that use the context as usual.

Answer 2

Have you tried it like this (an object with the property ConnectionString for the context):

connectionStrings": {  
    "BankContext": {  
        "ConnectionString": "{myConnectionString}",  
        "ProviderName": "System.Data.SqlClient"  
    }  
}

Otherwise you could read the connectionstring explicitly from the settings and provide is when calling this function on the OptionsBuilder object:

.UseSqlServer(Environment.GetEnvironmentVariable("SqlConnectionString"));

Answer 3

I think you should configure it manually in the portal.

Go to your Azure Functions and add the Connection String with the name sqldb_connection.