how to fix the broken connection string in local.settings.json file on azure function app?

Question

This is the content inside local.settings.json file. here the password have special characterslike (=,%) which i suspect is the issue.

I have a sql trigger in the main function_app.py file. Also, is there any way i can not hardcode these values .

I have tried using urllib.parse to encode the password for use. I've configured an Azure Key Vault, and a function generates this connection string but couldn't use it . How do I pass it in here?"

{ 
"IsEncrypted": false, 
"Values": { "AzureWebJobsStorage": "", 
"FUNCTIONS_WORKER_RUNTIME": "python",
 "SQLConnectionString": "Server=tcp:test.database.windows.net,1433;Database=testdb;User Id=testuser;Password=HaZ_h2DRF8dHck=A4A%25fFg36erkd" } }

Answer 1

Hi Sudip, have you tried encoding % as %25 ?

eg"

"SQLConnectionString": "Server=tcp:test.database.windows.net,1433;Database=testdb;User Id=testuser;Password=HaZ_h2DRF8dHck=A4A%25fFg36erkd"

As for not hardcoding the password, you can look into using Azure key vault with your function, eg:

Name: SQLConnectionString

Value: @Microsoft.KeyVault(SecretUri=https://<YourKeyVaultName>.vault.azure.net/secrets/<secretName>/<secretVersion>)

reference:
https://learn.microsoft.com/en-us/azure/app-service/app-service-key-vault-references?tabs=azure-cli

Answer 2

Hello @Sudip Bala

It seems like you are trying to connect to a SQL database from your Azure Function App and you are facing issues with the password that contains special characters.

You can try encoding the password using urllib.parse.quote_plus() method before using it in the connection string.

Here is an example:

import urllib.parse 
password = "your_password_with_special_characters"
encoded_password = urllib.parse.quote_plus(password)
connection_string = f"Server=tcp:test.database.windows.net,1433;Database=testdb;User ID=your_username;Password={encoded_password};Encrypt=yes;Connection Timeout=30;"

Regarding not hardcoding the values, you can store the sensitive information like passwords, connection strings, etc. in Azure Key Vault and retrieve them at runtime in your code. You can use the Azure.Identity library to authenticate with Azure Key Vault and retrieve the secrets.

Here is an example:

from azure.identity import DefaultAzureCredential
from azure.keyvault.secrets import SecretClient
credential = DefaultAzureCredential()
secret_client = SecretClient(vault_url="https://your-key-vault-name.vault.azure.net/", credential=credential)

# Retrieve the secret value by name
password_secret = secret_client.get_secret("your-password-secret-name")
password = password_secret.value
# Use the password in the connection string
connection_string = f"Server=tcp:test.database.windows.net,1433;Database=testdb;User ID=your_username;Password={password};Encrypt=yes;Connection Timeout=30;"

---

I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.