Error while connecting to SQL server using pyodbc from Azure Synapse Notebook
I am facing the below error when trying to connect to Azure Synapse Dedicated SQL Pool from Azure Synapse Spark notebook using pyodbc
Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")
Below is the simple code that I am using -
import pyodbc
server = 'myServer'
database = 'myDB'
username = 'myUser'
password = 'myPassword'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';ENCRYPT=yes;UID='+username+';PWD='+ password)
I think the Synapse Workspace does not come with a pre-installed ODBC driver for SQL Server as compared to the Azure Functions. The same code runs successfully from Azure Functions.
I have gone through this link - https://datathirst.net/blog/2018/10/12/executing-sql-server-stored-procedures-on-databricks-pyspark/ and tried running the below code from my notebook cell -
%%sh
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
apt-get update
ACCEPT_EULA=Y apt-get install msodbcsql17
apt-get -y install unixodbc-dev
sudo apt-get install python3-pip -y
pip3 install --upgrade pyodbc
But it gives me the below error -
E: This command can only be used by root.
sh: 2: cannot create /etc/apt/sources.list.d/mssql-release.list: Permission denied
E: Could not open lock file /var/lib/apt/lists/lock - open (13: Permission denied)
E: Unable to lock directory /var/lib/apt/lists/
W: Problem unlinking the file /var/cache/apt/pkgcache.bin - RemoveCaches (13: Permission denied)
W: Problem unlinking the file /var/cache/apt/srcpkgcache.bin - RemoveCaches (13: Permission denied)
E: Could not open lock file /var/lib/dpkg/lock-frontend - open (13: Permission denied)
E: Unable to acquire the dpkg frontend lock (/var/lib/dpkg/lock-frontend), are you root?
E: Could not open lock file /var/lib/dpkg/lock-frontend - open (13: Permission denied)
E: Unable to acquire the dpkg frontend lock (/var/lib/dpkg/lock-frontend), are you root?
sudo: no tty present and no askpass program specified
Can someone please guide me on how to resolve this or install the ODBC driver from the Synapse notebook?
I tried to reproduce the error in my environment and got similar error.
The cause of error is the driver you are using for connection is not present in your synapse notebook.
To check the driver installed in your synapse notebook use the following commands.
import pyodbc
pyodbc.drivers()
Then use that driver to connect with SQL:
