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

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 sql pyodbc

Solution 1:^[1]

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:^[2]

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:^[3]

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:^[4]

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:^[5]

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:^[6]

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:^[7]

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:^[8]

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:^[9]

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:^[10]

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:^[11]

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:^[12]

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:^[13]

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:^[14]

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:^[15]

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:^[16]

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

Solution 17:^[17]

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:^[18]

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:^[19]

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:^[20]

Make sure you have all drivers and db engine installed

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

Solution 21:^[21]

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:^[22]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2
Solution 3	wjandrea
Solution 4	Sanjiv
Solution 5	Madbeap
Solution 6	Ustin
Solution 7	dady7749
Solution 8	xiawi
Solution 9	Alireza Ali
Solution 10	Julio S.
Solution 11	Ajay Singh
Solution 12	Gurpreet Deol
Solution 13	Tarek Salha
Solution 14	user13839631
Solution 15	sakulachi8
Solution 16	Sachin Patel
Solution 17	Krishna Mohan Bandi
Solution 18	RobC
Solution 19	crazyX
Solution 20	Ricardo Vilaça
Solution 21	lam vu Nguyen
Solution 22	swaroop garlapati

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

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]

Solution 7:[7]

Solution 8:[8]

Solution 9:[9]

Solution 10:[10]

Solution 11:[11]

Solution 12:[12]

Solution 13:[13]

Solution 14:[14]

Solution 15:[15]

Solution 16:[16]

Solution 17:[17]

Solution 18:[18]

Solution 19:[19]

Solution 20:[20]

Solution 21:[21]

Solution 22:[22]