Unable to connect SQL server to Azure ML pipeline?

MOHAMMED ABDULLA T 6

Hi,
I am trying to connect sql server using pyodbc and to extract some procedure for Azure ML pipeline. Since I am not able to install pyodbc library in Azure ML, pipeline connection is not happening. Kindly let me know is there any alternative method to connect sql server and execute the procedure. Code is as bellow

conn = pyodbc.connect(

Ramr-msft 17,611 Reputation points

2022-03-17T15:56:44.36+00:00

@MOHAMMED ABDULLA T Thanks for the question. You can always from within your train script use pyodbc to access on prem SQL server as long as there’s network connectivity.
You will need to define a custom docker image to configure the driver if you use on demand AML compute. It’s easier to use VM:

See this: https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Linux

You may need to run !az login in a separate cell to login first.

import pyodbc
import struct
from itertools import chain, repeat
import json
import subprocess
command = "az account get-access-token --resource=https://database.windows.net"
response = subprocess.check_output(command, shell=True)
token = json.loads(response)['accessToken']

tokenb = bytes(token, "UTF-8")
encoded_bytes = bytes(chain.from_iterable(zip(tokenb, repeat(0))))
tokenstruct= struct.pack("<i", len(encoded_bytes)) + encoded_bytes
SQL_COPT_SS_ACCESS_TOKEN = 1256

conn_string = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=YOUR_SERVICE;DATABASE=YOUR_DB"
conn = pyodbc.connect(conn_string, attrs_before = { SQL_COPT_SS_ACCESS_TOKEN:tokenstruct })
Ramr-msft 17,611 Reputation points

2022-03-17T16:19:56.973+00:00

@MOHAMMED ABDULLA T Thanks for the question. Can you please share error details that you are getting.
MOHAMMED ABDULLA T 6 Reputation points

2022-03-17T19:36:34.207+00:00

@Ramr-msft I am trying to install pyodbc library in azure ml pipeline. Please see Document where I am following the procedure to install. It is mentioned that installation of pyodbc is not allowed. Please see image.
MOHAMMED ABDULLA T 6 Reputation points

2022-03-18T06:38:32.8+00:00

Hi @Ramr-msft ,
Please see the reply on above post.
Ramr-msft 17,611 Reputation points

2022-03-23T05:16:08.013+00:00

@MOHAMMED ABDULLA T Thanks for the details. Please check the update for post.

2 answers

Ramr-msft 17,611 Reputation points

2022-03-21T07:28:13.357+00:00

@MOHAMMED ABDULLA T Thanks for the details. That's not the suggested way, please follow the Import Data doc to bring the Azure Sql database into pipeline.

https://learn.microsoft.com/en-us/azure/machine-learning/component-reference/import-data

Are you using the on-premise SQL server?. If yes we have forwarded to the product team to support in the near future. For unsupported storage solutions, and to save data egress cost during ML experiments, move your data to a supported Azure storage solution.
Here is the document for supported storage types.
Please sign in to rate this answer.
Benjamin Carter 0 Reputation points

2023-10-06T00:06:54.07+00:00

Least satisfying response ever. Perfectly accurate, but it is sad that MSFT is making it challenging to use their tools universally. This level of controlling the environment is why I refuse to use Apple products, and is making me seriously reconsider my existing commitment to MSFT.
Sign in to comment
Thiago Barral 0 Reputation points

2023-03-27T20:57:16.32+00:00
import os os.system(f"pip install pyodbc") import pyodbc
Please sign in to rate this answer.

0 comments No comments
Sign in to comment