As discussed earlier, you have to host the database in Azure and use ODBC Driver 18 instead of 17 and add the client IP address under firewall rule in SQL Server=>Networking
to resolve the issue.
Please try below steps and let me know if you face any issues:
Below are my findings for not able to deploy your function.
- ODBC Driver version for SQL Server:
Exception observed:
Result: Failure
Exception: DBAPIError: (pyodbc.Error) ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")
- You should use
ODBC Driver 18 for SQL Server
instead ofODBC Driver 17
inSQL_CONNECTION_STRING
.
"SQL_CONNECTION_STRING": "mssql+pyodbc://username:password@server/database?driver=ODBC+Driver+18+for+SQL+Server"
- Allow Client with IP address ‘[20.XXX.XX.XXX]’ to access the SQL server.
Exception: ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Cannot open server 'servername' requested by the login. Client with IP address '20.XXX.XX.XXX' is not allowed to access the server. To enable access, use the Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect. (40615) (SQLDriverConnect)")
To know the Client IP address which needs access, go to FunctionApp=>Logs
, you can see the ClientIP under Exceptions.
- Navigate to your
SQL Server=>Networking
, Add the client IP address under Firewall rule to allow the access to SQL server. - Add Application setting
"AzureWebJobsFeatureFlags": "EnableWorkerIndexing",
in the Environment variables of function app andlocal.settings.json
I have deployed your code to Azure and able to sync the triggers:
8:00:26 PM kpfn2105: Destination directory: /tmp/build/expressbuild 8:00:26 PM kpfn2105: Python Version: /tmp/oryx/platforms/python/3.11.12/bin/python3.11 8:00:26 PM kpfn2105: Creating directory for command manifest file if it does not exist 8:00:26 PM kpfn2105: Removing existing manifest file 8:00:26 PM kpfn2105: Running pip install... 8:00:38 PM kpfn2105: Syncing triggers... 8:00:40 PM kpfn2105: Querying triggers...
8:00:47 PM kpfn2105: HTTP Trigger Urls: http_app_func: https://kpfn2105.azurewebsites.net//%7B*route%7D
Hope this helps!
If this answers your query, do click Accept Answer
and Yes
for was this answer helpful, which may help members with similar questions.
If you have any other questions or are still experiencing issues, feel free to ask in the "comments" section, and I'd be happy to help.