1

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)
Improve this question
7
  • could this be an answer to your question? stackoverflow.com/a/64223277/17637655 Commented Jul 18, 2022 at 10:19
  • I checked all the settings mentioned in this link. They are enabled. Im still unable to connect Commented Jul 18, 2022 at 10:36
  • You need to open the firewall for port 1433 I think Commented Jul 18, 2022 at 10:43
  • It is open and Im able to connect with databricks runtime 7.4 LTS, with 10.4 LTS its failing Commented Jul 18, 2022 at 10:53
  • Aside... PORT=1433; isn't a connection string parameter. You'd normally use SERVER=tcp:serverNameOrIpAddress,portNumber; (note the comma), but specifying port 1433 isn't required because it's the default port. Commented Jul 18, 2022 at 11:40

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Are you using msodbcsql17 or msodbcsql18?
[ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.