Use Python to Connect to SQL Server Using Windows Authentication

For this example we will be connecting to a SQL Server database using the Visual Studio Code (VS CODE), Windows Authentication and the pyodbc module. This is also similar to our recent post on connecting to MySQL using Visual Studio code in Linux and this post has more info on the Python extension if you do not have them installed.

We will setup a Python environment and install the module needed to connect to SQL Server using the currently logged in Windows user. In this example we are using Windows 10.

Prerequisites:

• Visual Studio Code
• SQL Server (version should be 2012 or later)
• Python 3.9.x

Quick Python Authentication Example

The important parameter is where the “username and password” were set, we are replacing it with “Trusted_Connection=yes” so the user account logged into Windows is used instead.

import pyodbc

conn_str = ("Driver={SQL Server};"
            "Server=sqlserver\sqlinst1;"
            "Database=DBName;"
            "Trusted_Connection=yes;")
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
cursor.execute("SELECT TOP(1000) * FROM sometable")

for row in cursor:
    print(row)

Update: This article was written using an older Driver. See the ODBC Driver for Linux article on Microsoft’s website for the version 18 driver. I haven’t had time to test but you may need to change…

"Driver={SQL Server};"

TO

"Driver={ODBC Driver 18 for SQL Server};"

Python and VS Code Terminal

We need to create an environment to work in. Start by creating a new folder and a new file called “main.py”, open “main.py” in Visual Studio code.

Then open the terminal using “Terminal -> New Terminal”.
Check that the python version is correct by typing:

python -V
python --version

Note: If you get stuck in a python shell just type “exit()” to get back to the terminal.

When you open a file with the extension of *.py you should see VS Code recognize this and display the Python version it is using in the bottom left corner. It will choose the system default version but we want to setup an environment to work within first.

Python Virtual Environment

The drivers and modules inside our environment will only be available to this project. This way it does not affect any other projects. You can create a new environment for each project to keep things well organized.

In terminal create a virtual environment folder called “myenv”

python -m venv myenv

We will reference our Interpreter here and activate the new environment, this is the Python.exe that the project will use. You should get a popup message,..

“We noticed a new virtual environment has been created. Do you want to select it for the workspace folder?”.
Click Yes and the Environment in the bottom left corner will change to “myenv”.

If there’s no popup you can just click the bottom left “Python 3.9.x” area and assign “\myenv\Scripts\python.exe”.

Activate the environment by typing:

.\myenv\Scripts\activate

The command prompt should now have the environment in parenthesis before the drive letter.

(myenv) PS C:\path\dbconnect

Install Python (pyodbc) SQL Server Driver

There are more than one SQL Server drivers for python, we are working with pyodbc. To install the driver type the following in your terminal window.

pip install pyodbc  

Warning: You’re company might have a firewall that rejects the SSL certificates to connect using pip. You may need to contact your security department to install pyodbc.

CERTIFICATE_VERIFY_FAILED Workaround

We ran into this when installing pyodbc for Linux, If you get the following:

SSLError(SSLCertVerificationError(1, ‘[SSL: CERTIFICATE_VERIFY_FAILED]

You can allow the pip install to continue by trusting the domains required:

pip install pyodbc --upgrade --trusted-host=pypi.python.org --trusted-host=pypi.org --trusted-host=files.pythonhosted.org 

Pyodbc Parameters

import pyodbc

conn_str = ("Driver={SQL Server};"
            "Server=sqlserver\sqlinst1;"
            "Database=DBName;"
            "Trusted_Connection=yes";)
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
cursor.execute("SELECT TOP(1000) * FROM sometable")

for row in cursor:
    print(row)

With pyodbc installed we can use the same code to connect except we replace the username and password with “Trusted_Connection”. Executing this (db tales com) windows authentication or the user that is logged into Windows or the account that VS Code is running under, if you opened it using”Open AS”.

---