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

Question

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

Possible duplicate of [Geting Data from MSSQL Using pyodbc Error](https://stackoverflow.com/questions/42987940/geting-data-from-mssql-using-pyodbc-error) — jq170727, Sep 05 '17 at 01:59

Gord Thompson · Answer 1 · 2017-09-05T12:51:56.640

53

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)

edited Sep 05 '17 at 12:51

answered Sep 05 '17 at 12:30

Gord Thompson

116,920
32
215
418

4

"Do not put a space after the Driver keyword in the connection string." This one works for me.. I was confused initially that what was wrong.. :-) +1 for this.. – vikrant rana Jul 18 '19 at 09:05
1

I still get this error message on Windows without the space. – Adrian Keister Feb 16 '21 at 20:20
@AdrianKeister - That error message can have many different causes. Please [ask a new question](https://stackoverflow.com/questions/ask) with a [mcve]. – Gord Thompson Feb 16 '21 at 21:05
2

@GordThompson I figured out my error: on Windows you need to use the `{SQL SERVER}` driver. So now I check the `os.platform` string and use the right connection string accordingly. – Adrian Keister Feb 16 '21 at 21:10
2

@AdrianKeister - Good to hear that you got it working. However, be aware that the `{SQL Server}` driver that ships with Windows is pretty ancient (circa SQL Server 2000) and may prove limiting when working with current versions of SQL Server. Microsoft now maintains modern ODBC Drivers for Windows, Mac, and (at least some flavours of) Linux. `ODBC Driver 17 for SQL Server` is currently the newest. – Gord Thompson Feb 16 '21 at 21:18
@GordThompson Ah, I see. I went ahead and install driver 17 and reverted my code. I'll see how that works. – Adrian Keister Feb 16 '21 at 22:39
So for some of us, we need to specify the right driver? – Ac Hybl Nov 07 '22 at 05:40

score 43 · Answer 2 · edited Jun 07 '22 at 11:47

43

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}'

edited Jun 07 '22 at 11:47

Talha Tayyab

8,111
25
27
44

answered Nov 12 '20 at 12:50

Sanjiv

980
2
11
29

4

This is the correct answer in my opinion but some more details could be added. The Driver version specified needs to match the driver installed on the local machine that is running the code. The driver has to be compatible with the target server version but this is mostly a non issue because the drivers have pretty brought compatibility, especially if you use the current driver version (right now version 17) – TheColonel26 Aug 26 '21 at 13:28
This approach has worked for me. Thanks @Sanjiv – Hafiz Hashim May 24 '22 at 14:28
Ahh, yeah, this may be what people are looking for more often. – Ac Hybl Nov 07 '22 at 05:38
it's working, check your SQL version and enter, my side SQL version 13 like this : `{ODBC Driver 13 for SQL Server}` – Ramesh Nov 12 '22 at 16:09

score 21 · Answer 3 · edited Mar 23 '18 at 04:56

21

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}'

edited Mar 23 '18 at 04:56

wjandrea

28,235
9
60
81

answered Mar 23 '18 at 01:40

Mil

311
2
3

1

I have it the way you have it written here and I still get that same error that the question asker had – bernando_vialli Jul 11 '18 at 14:33
2

For some reason, the opposite worked for me. So I'd recommend trying out both. – Neelotpal Shukla Jun 16 '20 at 17:41
@bernando_vialli can you please see my answer below, I just put all options which can be in place, – sakulachi8 Dec 11 '20 at 05:23
@NeelotpalShukla I have two machines I am using, a personal laptop and a work laptop. For some reason, each works differently. I am not sure why tbh – Abdelrahman Shoman Sep 21 '21 at 07:12

Avnish Tiwary · Answer 4 · 2021-06-03T15:51:56.943

20

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.

edited Jun 03 '21 at 15:51

answered Sep 06 '19 at 09:10

Avnish Tiwary

2,188
22
27

Yes, this is weird, i did the same as "@Avnish alok" and it worked. Guess we just need to try different Driver={xxx} until it works. I was connecting to the below SQL Server "Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64) Jun 15 2019 00:26:19 Copyright (C) 2017 Microsoft Corporation Web Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor) " – sagar Dec 16 '19 at 20:34
I've got SQL Server 2019 installed and Avnish's fix worked for me, too. – David Ayres Dec 14 '20 at 20:06

score 10 · Answer 5 · answered Mar 13 '21 at 14:16

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

score 5 · Answer 6 · answered May 21 '19 at 08:58

5

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:

answered May 21 '19 at 08:58

Ustin

568
6
19

3

this lead me to my answer, because I had '{SQL Server Native Client 11.0}' from a tutorial and mine only worked with '{SQL Server}'. – user3553260 Jun 04 '19 at 22:48
2

This is wrong. You need to specify the driver that is installed on the machine running the python code. For example, Driver 17 worked for me when connecting to a SQL 2014 server, when my code ran on my dev PC, but when I moved it to the same server that was running the Server my code did not work andy more. I had Driver 17 installed on my Dev PC but the server had Driver 11 and 13 installed. So to just make everyting consitent I installed Driver 17 on the server and everthing worked. – TheColonel26 Aug 26 '21 at 13:25
@user3553260 I think using `'{SQL Server}'` might be an outdated approach; [this comment says that Driver is "ancient"](https://stackoverflow.com/questions/46045834/pyodbc-data-source-name-not-found-and-no-default-driver-specified#comment117095825_46055035) – Nate Anderson May 09 '23 at 19:59
Answers like [this one](https://stackoverflow.com/a/54206996/1175496) and [this one](https://stackoverflow.com/a/39440803/1175496) suggest that `ODBC` clients are the successor to the native client , and how `SQL Server` and `SQL Server Native Client` are outdated/deprecated. – Nate Anderson May 09 '23 at 20:02

score 3 · Answer 7 · answered Dec 10 '18 at 19:05

3

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

answered Dec 10 '18 at 19:05

dady7749

125
3
11

1

P.S I am not entirely sure which tab to look at for the Data Source Name. – dady7749 Dec 10 '18 at 19:07

score 3 · Answer 8 · edited May 26 '20 at 12:23

3

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>"

edited May 26 '20 at 12:23

xiawi

1,772
4
19
21

answered May 26 '20 at 08:06

Patrick

31
2

score 2 · Answer 9 · answered Sep 24 '19 at 17:52

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()

score 2 · Answer 10 · answered Nov 11 '20 at 10:35

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://learn.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver15

score 2 · Answer 11 · answered Sep 15 '21 at 15:45

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.

score 1 · Answer 12 · answered Aug 28 '19 at 17:30

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;"

score 1 · Answer 13 · answered Feb 27 '20 at 17:04

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.

score 1 · Answer 14 · answered May 05 '20 at 10:28

1

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.

answered May 05 '20 at 10:28

developer_009

317
3
11

this fixed my problem – oldpride Jul 29 '23 at 22:06

score 1 · Answer 15 · answered Jun 30 '20 at 08:44

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()`

score 1 · Answer 16 · answered Dec 11 '20 at 05:19

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

score 1 · Answer 17 · answered Feb 05 '21 at 09:35

1

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

answered Feb 05 '21 at 09:35

Sachin Patel

499
2
12

score 1 · Answer 18 · answered Apr 26 '22 at 04:06

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.")

score 0 · Answer 19 · edited Sep 16 '19 at 08:51

0

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,))

edited Sep 16 '19 at 08:51

RobC

22,977
20
73
80

answered Sep 16 '19 at 08:34

Srinivasan

11
2

score 0 · Answer 20 · answered Apr 05 '21 at 00:20

0

Make sure you have all drivers and db engine installed

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

answered Apr 05 '21 at 00:20

Ricardo Vilaça

846
1
7
18

1

You might need the [MS Access 2013 runtime](https://www.microsoft.com/en-us/download/details.aspx?id=39358) depending on your version of office as explained on this [Microsoft trouble shooting page](https://learn.microsoft.com/en-us/office/troubleshoot/access/cannot-use-odbc-or-oledb). – Paul Rougieux Apr 13 '21 at 13:51
For me it was the db engine that was missing – Ricardo Vilaça Apr 13 '21 at 14:37
Sorry for the down vote, I tried removing it but it's locked. – Paul Rougieux Apr 13 '21 at 20:10
No worries, i don't care for the points :) And leaving the link here might help someone someday who knows – Ricardo Vilaça Apr 14 '21 at 11:01

score 0 · Answer 21 · answered Jan 25 '22 at 15:52

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://learn.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

score 0 · Answer 22 · answered Apr 04 '23 at 07:31

None of the above worked for me. What did work for me was the following:

Instead of passing a connection string like this:

conn_str = "DRIVER={SQL Server};SERVER=127.0.0.1;DATABASE=test;UID=root;PWD=root"
conn = pyodbc.connect(connstring=conn_str)

I passed the connection details as following:

conn = pyodbc.connect(Driver="SQL Server",Server='127.0.0.1',Database='test',UID="root",PWD="root")

Hope this helps someone.

score -1 · Answer 23 · answered Oct 22 '20 at 03:43

-1

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

answered Oct 22 '20 at 03:43

swaroop garlapati

1
2

Shrey Sanja · Answer 24 · 2023-04-24T06:28:08.060

-3

if your system is 64 bit then make sure you have both python and sql 64 bit you will find both on the google how to download 64 bit python

edited Apr 24 '23 at 06:28

answered Apr 24 '23 at 06:26

Shrey Sanja

1
2

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

24 Answers24

Linked

Related