Setting Connection String as App Setting/Environment Variable in Azure Function

Question

In my Azure Function, I have specified an Environment Variable/App Setting for a database connection string. I can use the Environment Variable when I run the Function locally on my Azure Data Science Virtual Machine using VS Code and Python.

However, when I deploy the Function to Azure, I get an error: KeyValue is None, meaning that it cannot find the Environment Variable for the connection string. See error:

Exception while executing function: Functions.matchmodel Result: Failure
Exception: KeyError: 'CONNECTIONSTRINGS:PDMPDBCONNECTIONSTRING'
Stack:   File "/azure-functions 
  host/workers/python/3.7/LINUX/X64/azure_functions_worker/dispatcher.py", line 315, in 
  _handle__invocation_request self.__run_sync_func, invocation_id, fi.func, args)
File "/usr/local/lib/python3.7/concurrent/futures/thread.py", line 57, in run
  result = self.fn(*self.args, **self.kwargs)
File "/azure-functions-host/workers/python/3.7/LINUX/X64/azure_functions_worker/dispatcher.py", 
  line 434, in __run_sync_func
  return func(**params)
File "/home/site/wwwroot/matchmodel/__init__.py", line 116, in main
File "/home/site/wwwroot/matchmodel/production/dataload.py", line 28, in query_dev_database
  setting = os.environ["CONNECTIONSTRINGS:PDMPDBCONNECTIONSTRING"]
File "/usr/local/lib/python3.7/os.py", line 679, in __getitem__
  raise KeyError(key) from None'

I have tried the following solutions:

• Added "CONNECTIONSTRINGS" to specify the Environment Variable in the Python script (which made it work locally) setting = os.environ["CONNECTIONSTRINGS:PDMPDBCONNECTIONSTRING"]

• Used logging.info(os.environ) to output my Environment Variables in the console. My connection string is listed.

• Added the connection string as Application Setting in the Azure Function portal.

• Added the Connection String as Connection Strings in the Azure Function portal.

Does anyone have any other solutions that I can try?

Answer 1

Actually you are almost get the right the connection string, however you use the wrong prepended string. Further more detailed information you could refer to this doc:Configure connection strings.

Which string to use it depends on which type you choose, like in my test I use a custom type. Then I should use os.environ['CUSTOMCONNSTR_testconnectionstring'] to get the value.

From the doc you could find there are following types:

• SQL Server: SQLCONNSTR_
• MySQL: MYSQLCONNSTR_
• SQL Database: SQLAZURECONNSTR_
• Custom: CUSTOMCONNSTR_

Answer 2

I was struggling with this and found out this solution:

import os

setting = os.getenv("mysetting")

Answer 3

In Functions application settings, such as service connection strings, are exposed as environment variables during execution. You can access these settings by declaring import os and then using

setting = os.environ["mysetting"]

As Alex said, try to remove CONNECTIONSTRINGS: from the name of the environment variable. In azure portal just add mysetting in application settings as keyName.

Answer 4

I figured out the issue with help from George and selected George's answer as the correct answer.

I changed the code to os.environ["SQLCONNSTR_PDMPDBCONNECTIONSTRING"] but also I had been deploying the package from VS Code to Azure via the following code using Azure Command Line Interface (Azure CLI). This code overwrites Azure App Settings with the local.settings.json.

func azure functionapp publish <MY_FUNCTION_NAME> --publish-local-settings -i --overwrite-settings -y

I think that was causing changes to the type of database that I had specified in Azure App Settings (SQL Server), so when I had previously tried os.environ["SQLCONNSTR_PDMPDBCONNECTIONSTRING"] it didn't work because I was overwriting my Azure settings using my local.settings.json, which did not specify a database type.

I finally got it to work by deploying using the VS Code Extension called Azure Functions (Azure Functions: Deploy to Function App). This retains the settings that I had created in Azure App Services.