What is the name of the driver to connect to Azure SQL Database from pyodbc in Azure ML?

Question

I'm trying to create a 'Reader' alternative to read data from Azure SQL Database using the 'Execute python script' module in Azure ML. while doing so, I'm trying to connect to Azure Sql using pyodbc library. here's my code:

def azureml_main(dataframe1 = None, dataframe2 = None):
    import pyodbc   
    import pandas as pd

    conn = pyodbc.connect('DRIVER={SQL Server}; SERVER=server.database.windows.net; DATABASE=db_name; UID=user; PWD=Password')
    SQLCommand = ('''select * from table1 ''')
    data_frame = pd.read_sql(SQLCommand, conn)
    return data_frame,

also tried to use a different driver name: {SQL Server Native Client 11.0}

Here is the error i'm getting:

Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Does anybody know which driver should I use?

just to make sure, I tried "{SQL Server}", "{SQL Server Native Client 11.0}" and "{SQL Server Native Client 10.0}" and got the same error

I also tried a different format:

conn = pyodbc.connect('DRIVER={SQL Server}; SERVER=server.database.windows.net; DATABASE=db_name; user=user@server; password=Password')

and

conn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0}; SERVER=server.database.windows.net; DATABASE=db_name; user=user@server; password=Password')

Answer 1

According to this answer, the connection string should be:

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=yoursqlAzureServer.database.windows.net,1433', user='yourName@yoursqlAzureServer', password='Password', database='DBName')

Note the difference in the format: different params for user, password and database vs. all in one in the first string.

Also related, see this Azure page: Connect to SQL Database by using Python on Windows. It states using pymssql, with no mention of pyodbc.