PYODBC--Data source name not found and no default driver specified

Python Problem Overview

import pyodbc
connection = pyodbc.connect('Driver = {SQL Server};Server=SIWSQL43A\SIMSSPROD43A;'
                            'Database=CSM_reporting;Trusted_Connection=yes;')

Error:

connection = pyodbc.connect('Driver = {SQL Server};Server=SIWSQL43A\SIMSSPROD43A;'
    pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Python Solutions

Solution 1 - Python

Do not put a space after the Driver keyword in the connection string.

This fails on Windows ...

conn_str = (
    r'DRIVER = {SQL Server};'
    r'SERVER=(local)\SQLEXPRESS;'
    r'DATABASE=myDb;'
    r'Trusted_Connection=yes;'
)
cnxn = pyodbc.connect(conn_str)

... but this works:

conn_str = (
    r'DRIVER={SQL Server};'
    r'SERVER=(local)\SQLEXPRESS;'
    r'DATABASE=myDb;'
    r'Trusted_Connection=yes;'
)
cnxn = pyodbc.connect(conn_str)

Solution 2 - Python

I'm using Django 2.2

and got the same error while connecting to sql-server 2012. Spent lot of time to solve this issue and finally this worked.

I changed driver to

> 'driver': 'SQL Server Native Client 11.0'

and it worked.

Solution 3 - Python

I've met same problem and fixed it changing connection string like below. Write

'DRIVER={ODBC Driver 13 for SQL Server}'

instead of

'DRIVER={SQL Server}'

Solution 4 - Python

I am also getting same error. Finally i have found the solution.

We can search odbc in our local program and check for version of odbc. In my case i have version 17 and 11 so. i have used 17 in connection string

> 'DRIVER={ODBC Driver 17 for SQL Server}'

Solution 5 - Python

Local Ms Sql database server need or {ODBC driver 17 for SQL Server} Azure Sql Database need{ODBC driver 13 for SQL SERVER}

Check installed drivers here => Installed ODBC Drivers

Format for connection to Azure Sql Database is :

import pyodbc
conn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};'
                      'SERVER=tcp:nameServer.database.windows.net,1433;'
                      'DATABASE=Name database; UID=name; PWD=password;')

Format for connection to Ms SQL Databse Local:

import pyodbc
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};'
                      'SERVER=server.name;' // example Doctor-Notebook\\MSSQLEXPRESS
                      'DATABASE=database.name; Trusted_connection = yes')

Solution 6 - Python

I faced this issue and was looking for the solution. Finally I was trying all the options from the https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Windows , and for my MSSQL 12 only "{ODBC Driver 11 for SQL Server}" works. Just try it one by one. And the second important thing you have to get correct server name, because I thought preciously that I need to set \SQLEXPRESS in all of the cases, but found out that you have to set EXACTLY what you see in the server properties. Example on the screenshot:

Solution 7 - Python

You could try:

import pyodbc
# Using a DSN
cnxn = pyodbc.connect('DSN=odbc_datasource_name;UID=db_user_id;PWD=db_password')

Note: You will need to know the "odbc_datasource_name". In Windows you can search for ODBC Data Sources. The name will look something like this:

Data Source Name Example

Solution 8 - Python

The below code works magic.

 SQLALCHEMY_DATABASE_URI = "mssql+pyodbc://<servername>/<dbname>?driver=SQL Server Native Client 11.0?trusted_connection=yes?UID" \
                              "=<db_name>?PWD=<pass>"

Solution 9 - Python

I have had the same error on python3 and this help me:

conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};'
                      'SERVER=YourServerName;'
                      'DATABASE=YourDatabaseName;UID=USER_NAME;PWD=PASS_WORD;')

remember python is case-sensitive so you have to mention DRIVER,SERVER,... in upper case. and you can visit this link for more information:

<https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver15>

Solution 10 - Python

In my case, the exact same error was caused by the lack of the drivers on Windows Server 2019 Datacenter running in an Azure virtual machine.

As soon as I installed the drivers from https://www.microsoft.com/en-us/download/details.aspx?id=56567, the issue was gone.

Solution 11 - Python

for error : pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

No space between the driver and event

connection = Driver={SQL Server Native Client 11.0}; "Server=servername;" "Database=dbname;" "Trusted_Connection=yes;"

Solution 12 - Python

Below connection string is working

import pandas as pd
import pyodbc as odbc

sql_conn = odbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=SERVER_NAME;DATABASE=DATABASE_NAME;UID=USERNAME;PWD=PASSWORD;')

query = "SELECT * FROM admin.TABLE_NAME"
df = pd.read_sql(query, sql_conn)
df.head()

Solution 13 - Python

Apart from the other answers, that considered the connection string itself, it might simply be necessary to download the correct odbc driver. My client just faced this issue when executing a python app, that required it. you can check this by pressing windows + typing "odbc". the correct driver should appear in the drivers tab.

Solution 14 - Python

Create a DSN something like this (ASEDEV) for your connection and try to use DSN instead of DRIVER like below:

enter code here
import pyodbc
cnxn = pyodbc.connect('DSN=ASEDEV;User ID=sa;Password=sybase123')
mycur = cnxn.cursor()
mycur.execute("select * from master..sysdatabases")
row = mycur.fetchone()
while row:
    print(row)
    row = mycur.fetchone()`

Solution 15 - Python

I was facing the same issue whole day wasted and I tried all possible ODBC Driver values

import pyodbc
connection = pyodbc.connect('Driver = {SQL Server};Server=ServerName;'
                            'Database=Database_Name;Trusted_Connection=yes;')

In place of Driver = {SQL Server} we can try these option one by one or just you can use with you corresponding setting, somehow in my case the last one works :)

Driver={ODBC Driver 11 for SQL Server} for SQL Server 2005 - 2014
Driver={ODBC Driver 13 for SQL Server} for SQL Server 2005 - 2016
Driver={ODBC Driver 13.1 for SQL Server} for SQL Server 2008 - 2016
Driver={ODBC Driver 17 for SQL Server} for SQL Server 2008 - 2017

Driver={SQL Server} for SQL Server 2000
Driver={SQL Native Client} for SQL Server 2005
Driver={SQL Server Native Client 10.0} for SQL Server 2008
Driver={SQL Server Native Client 11.0} for SQL Server 2012

Solution 16 - Python

You need to download Microsoft ODBC Driver 13 for SQL Server from Microsoft ODBC Driver 13

Solution 17 - Python

Thank you Avinash. brilliant. I tried to connect to MS Azure database using PyCharm. It worked.

server = ''
database = ''
username = ''
password = ''
driver = 'SQL Server Native Client 11.0'
connection1 = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password + ';TDS_Version=8.0')
print("Connected.")

Solution 18 - Python

Try below:

import pyodbc

server = 'servername'

database = 'DB'

username = 'UserName'

password = 'Password'

cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

cursor = cnxn.cursor()


cursor.execute('SELECT * FROM Tbl')

for row in cursor:
    print('row = %r' % (row,))

Solution 19 - Python

Have you installed any product of SQL in your system machine ? You can download and install "ODBC Driver 13(or any version) for SQL Server" and try to run if you havent alerady done.

Solution 20 - Python

Make sure you have all drivers and db engine installed

https://www.microsoft.com/en-us/download/details.aspx?id=54920

Solution 21 - Python

server = '123.45.678.90'
database = 'dbname'
username = 'username'
password = 'pwork'
driivver = '{ODBC Driver 17 for SQL Server}'
samgiongzon='DRIVER='+driivver+';SERVER='+server+\
                 ';DATABASE='+database+';UID='+username+\
                 ';PWD='+password+';Trusted_Connection=no;'
pyodbc.connect(samgiongzon, autocommit=True)

it worked for me; you need to install driver from here

> https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15

or (in ubuntu) sudo apt-get install unixodbc-dev if you get an error with pip install pyodbc

Solution 22 - Python

if any one are trying to access the database which is hosted in azure then try to give the driver as ODBC Driver 17 for SQL Server

Content Type	Original Author	Original Content on Stackoverflow
Question	user8560985	View Question on Stackoverflow
Solution 1 - Python	Gord Thompson	View Answer on Stackoverflow
Solution 2 - Python	Avnish alok	View Answer on Stackoverflow
Solution 3 - Python	Mil	View Answer on Stackoverflow
Solution 4 - Python	Sanjiv	View Answer on Stackoverflow
Solution 5 - Python	Madbeap	View Answer on Stackoverflow
Solution 6 - Python	Ustin	View Answer on Stackoverflow
Solution 7 - Python	dady7749	View Answer on Stackoverflow
Solution 8 - Python	Patrick	View Answer on Stackoverflow
Solution 9 - Python	Alireza Ali	View Answer on Stackoverflow
Solution 10 - Python	Julio S.	View Answer on Stackoverflow
Solution 11 - Python	Ajay Singh	View Answer on Stackoverflow
Solution 12 - Python	Gurpreet Deol	View Answer on Stackoverflow
Solution 13 - Python	Tarek Salha	View Answer on Stackoverflow
Solution 14 - Python	user13839631	View Answer on Stackoverflow
Solution 15 - Python	sakulachi8	View Answer on Stackoverflow
Solution 16 - Python	Sachin Patel	View Answer on Stackoverflow
Solution 17 - Python	Krishna Mohan Bandi	View Answer on Stackoverflow
Solution 18 - Python	Srinivasan	View Answer on Stackoverflow
Solution 19 - Python	crazyX	View Answer on Stackoverflow
Solution 20 - Python	Ricardo Vilaça	View Answer on Stackoverflow
Solution 21 - Python	lam vu Nguyen	View Answer on Stackoverflow
Solution 22 - Python	swaroop garlapati	View Answer on Stackoverflow