CODEWITHSUNDEEP
pythonsql-serverapache-sparkdatabrickspyodbc
u6765
u6765

Reputation: 113

Getting error while connecting to sql server using pyodbc in databricks 10.4 LTS runtime

Please Help...

Im getting below error while trying to connect to sql server with databricks runtime 10.4 LTS , while the connection was successfull with databricks runtime 7.4 LTS

Error:

OperationalError: ('08001', '[08001] [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 (10054) (SQLDriverConnect)')

I'm using below installation steps in an init script

dbutils.fs.put("/databricks/scripts/driversqlodbc.sh", """
#!/bin/bash
sleep 10
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 """, True)

And my connection string is like this

odbc_driver= '{ODBC Driver 17 for SQL Server}'
conn = pyodbc.connect('DRIVER='+odbc_driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+db_user+';PWD='+ password,autocommit=True)

** EDIT **

Below is the output of cat /etc/os-release

NAME="Ubuntu"
VERSION="20.04.4 LTS (Focal Fossa)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 20.04.4 LTS"
VERSION_ID="20.04"
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
VERSION_CODENAME=focal
UBUNTU_CODENAME=focal

Now my script looks like this

#!/bin/bash
sleep 10
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools18
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc
sudo apt-get install -y unixodbc-dev
sudo apt-get install python3-pip -y
pip3 install --upgrade pyodbc

And Connection String

odbc_driver= '{ODBC Driver 18 for SQL Server}'
conn = pyodbc.connect('DRIVER='+odbc_driver+';SERVER=tcp:'+server+',1433;DATABASE='+database+';UID='+db_user+';PWD='+ password,autocommit=True)

Upvotes: 1

Views: 750

Answers (1)

HeyMan
HeyMan

Reputation: 1855

Maybe an issue with the pyodbc version. We had the same problem. The latest pyodbc update to pyodbc==4.0.34 (5 days ago) causes some issues, see https://github.com/mkleehammer/pyodbc/issues/1079 for example.

So we changed the following line in setup.py:

"pyodbc~=4.0.32",

to

"pyodbc==4.0.32",

Note the double == sign. It works with 4.0.32 for us.

Upvotes: 1

Related Questions