# Connecting to MS SQL Server with python

[![](https://books.netdev.com.tr/uploads/images/gallery/2023-01/scaled-1680-/image-1674163403049.png)](https://books.netdev.com.tr/uploads/images/gallery/2023-01/image-1674163403049.png)

## 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')

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

## Output

[![](https://books.netdev.com.tr/uploads/images/gallery/2023-01/scaled-1680-/image-1674163359177.png)](https://books.netdev.com.tr/uploads/images/gallery/2023-01/image-1674163359177.png)


## 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](https://support.microsoft.com/tr-tr/topic/kb3135244-microsoft-sql-server-i%C3%A7in-tls-1-2-deste%C4%9Fi-e4472ef8-90a9-13c1-e4d8-44aad198cdbe)

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