connection to microsoft azure sql database works in local enviornment but not in production

Issue

I am trying to open a web service app that will allow me to connect to the database on azure with python code, Ive tried using sqlalchemy and pyodbc and i am successfully able to connect to the database on my machine, in the local host, i can perform all necessary actions i want to there. but i want to be able to set this code up to be able to hit specific routes in an ajax call that can perform certain actions on my database, like flipping a users active flag to false. however the problem is that when i upload the python code to azure, using this guide here (https://docs.microsoft.com/en-us/azure/app-service/quickstart-python?tabs=bash&pivots=python-framework-flask) it just returns a 500 server error, i cant find anything in the trace as to why its not working, I thought that it might just be that my local machine is whitelisted in the IP address’s to the database, but even still if i add that app services IP address to the allowed IP’s it still returns a server error. here is the setup of the code:

from flask import Flask
from sqlalchemy import create_engine
import pyodbc

app = Flask(__name__)

@app.route('/')
def connection():
    Driver = "{ODBC Driver 13 for SQL Server}"
    Server = "server string"
    Port = 1433
    Database = "dbname"
    Uid = "user"
    Pwd = "pass"
    
    try:
        cnxn = pyodbc.connect(f'DRIVER={Driver};SERVER={Server};DATABASE={Database};Uid={Uid};Pwd={Pwd};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;')
        cursor = cnxn.cursor()
        cursor.execute(f"SELECT * FROM (Database Table) where id = 999999999;")

    for row in cursor:
        print('row = %r' % (row,))
    return "Connection to database successful"

I ommitted certain details but the syntax should remain intact, Again, in my local machine I can connect to the database and return data. but once it compiles the code in azure, it no longer works.

excuse the try statement. I was trying to catch the error and send it to the client in hopes of gathering more information from the 500 error, but it didnt work.

**Edit: its worth mentioning that if i remove the actual connection string and anything to do with connecting to the database, the code will return "connection to database successful" this leads me to believe that it isnt fully making a connection to the database at all. and thats why its erroring out, however the question remains, why can i connect in my local enviornment but not in azure?

Solution

So the short answer to this is the App service was missing the driver {ODBC Driver 13 for SQL Server} it wasn’t erroring out locally because I have that driver installed locally, but this driver isn’t available in the app. To get this to work I would have to install the App to a Virtual Machine to be able to download the driver appropriately.

fortunately however Azure web apps built on a linux base already come with {ODBC Driver 17 for SQL Server} So flipping the driver from 13 to 17 allowed the app to successfully connect to my Database.

Answered By – Austin Howard

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0