Connecting to MS SQL Server with python

The problem

Sql server connection cant find the sql engine

The last line looks for a sql server engine named SQL SERVER however we don't use it anymore.

Collecting pypyodbc
Using cached https://files.pythonhosted.org/packages/62/94/a5bb72a83366c3249d60c7c465b25cb4252b6be4cc2b13eef048e8e73085/pypyodbc-1.3.6.tar.gz
Requirement already satisfied: setuptools in /usr/lib/python3/dist-packages (from pypyodbc)
Building wheels for collected packages: pypyodbc
Running setup.py bdist_wheel for pypyodbc ... done
Stored in directory: /root/.cache/pip/wheels/bd/68/0f/b23f408ec9ad90e883abc69ae16bf87ac822259de735c9f77c
Successfully built pypyodbc
Installing collected packages: pypyodbc
Successfully installed pypyodbc-1.3.6
root@miharbines:/home/miharbines/Test# python3 dbtest1.py
Traceback (most recent call last):
File "dbtest1.py", line 3, in <module>
connection = pypyodbc.connect('Driver={SQL Server};Server=x.y.z.d;DATABASE=trade;UID=sa;PWD=x.x.x.x.x.x')
File "/usr/local/lib/python3.6/dist-packages/pypyodbc.py", line 2454, in _init_
self.connect(connectString, autocommit, ansi, timeout, unicode_results, readonly)
File "/usr/local/lib/python3.6/dist-packages/pypyodbc.py", line 2507, in connect
check_success(self, ret)
File "/usr/local/lib/python3.6/dist-packages/pypyodbc.py", line 1009, in check_success
ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)
File "/usr/local/lib/python3.6/dist-packages/pypyodbc.py", line 983, in ctrl_err
raise OperationalError(state,err_text)
pypyodbc.OperationalError: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found")

Solution

İnstall MS SQL version 17 db engine from ms repos

sudo -i
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list
sudo apt update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql17
sudo apt-get install -y unixodbc-dev

Switch the driver

From

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=server_name;DATABASE=database_name;UID=user;PWD=password')

conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=server_name;DATABASE=database_name;UID=user;PWD=password')

Output

2nd Problem

My friend was using MS SQL 2014 default install and TLS 1.0 was the only option from produced by MS SQL 2014, we had to install the latest service packs so it can support newer TLS version such as tls 1.2 or 1.3

here is the link for turkish one

After patching the server 2014, the connection got established and code worked as expected.