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

Question

I am trying to create an alternative to Reader "to read data from an Azure SQL database using the Execute python script module in Azure ML . By doing this, I am trying to connect to Azure Sql using the pyodbc library. Here is 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 using a different driver name: {SQL Server 11.0 Native Client}

Here is the error I get:

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

Does anyone know which driver should I use?

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

I received a response from azure support:

It is currently not possible to access sql azure dbs from within "execute python script". As you suspected, this is due to the missing odbc drivers in the runtime. suggested workarounds: a) use a reader module or b) export to blobs and use the Azure Python SDK to access these blocks http://blogs.msdn.com/b/bigdatasupport/archive/2015/10/02/using-azure -sdk-for-python.aspx

Currently, it is not possible to connect to the SQL server from the "execute python script" module in Azure-ML. If you want to change it, vote here.

Answer 2

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 format: different parameters for the user, password and database compared to all in one in the first line.

Also see this Azure page: Connect to SQL Database using Python on Windows . It states pymssql , without mentioning pyodbc .

Answer 3

you will need the Microsoft ODBC driver to use pyodbc. You can download it here: https://www.microsoft.com/en-us/download/details.aspx?id=36434 . After downloading it, try using the following connection string:

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

If this line does not work, try the following:

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

If you still cannot use pyodbc let me know.

Cheers
Dating

Answer 4

The ultimate source of truth :-) for connection strings is:

https://www.connectionstrings.com/sql-azure/

Use it as a guide to create your own.

Answer 5

The name of the SQL database driver for Pyodbc should be {SQL Server Native Client 10.0} , as some reasons are lower.

• The connection string for the SQL database displayed on the Azure portal is shown in the following figure. 1 and 2.

Figure 1. On the old Azure portal

Figure 2. On the new Azure portal (version 11.0 later, then v10.0)

2. According to the comments of the Pyodbc codes , see Figure 3 below.

Fig. 3.