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 the 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

You can find the answer in this blog:

https://blogs.msdn.microsoft.com/bigdatasupport/2015/10/02/using-azure-sdk-for-python/

You can import data from SQL but you cannot do it directly with python.