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

Polished

Answer 1

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

Dent

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) – Trustee

This approach has worked for me. Thanks @Dent – Juliettejulina

Ahh, yeah, this may be what people are looking for more often. – Ashy

it's working, check your SQL version and enter, my side SQL version 13 like this : {ODBC Driver 13 for SQL Server} – Ridgepole

If you do not have the ODBC driver, you need to Download ODBC Driver for SQL Server. – Exclude

Answer 2

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)

Ungrateful

"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.. – Dolly

I still get this error message on Windows without the space. – Wrestle

@AdrianKeister - That error message can have many different causes. Please ask a new question with a minimal reproducible example. – Ungrateful

@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. – Wrestle

@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. – Ungrateful

@GordThompson Ah, I see. I went ahead and install driver 17 and reverted my code. I'll see how that works. – Wrestle

So for some of us, we need to specify the right driver? – Ashy

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

Ceric

I have it the way you have it written here and I still get that same error that the question asker had – Henigman

For some reason, the opposite worked for me. So I'd recommend trying out both. – Haug

@Henigman can you please see my answer below, I just put all options which can be in place, – Rondon

@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 – Shelia

Answer 4

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.

Inconclusive

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 <X64> (Build 14393: ) (Hypervisor) " – Hydrophone

I've got SQL Server 2019 installed and Avnish's fix worked for me, too. – Wimsatt

Answer 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 is:

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

Anthracosilicosis

Answer 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:

Maxima

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}'. – Chronicles

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. – Trustee

@Chronicles I think using '{SQL Server}' might be an outdated approach; this comment says that Driver is "ancient" – Compensable

Answers like this one and this one suggest that ODBC clients are the successor to the native client , and how SQL Server and SQL Server Native Client are outdated/deprecated. – Compensable

Answer 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

Dollar

P.S I am not entirely sure which tab to look at for the Data Source Name. – Dollar

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

Cimmerian

Answer 9

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

Marmalade

Answer 10

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

Primrosa

Answer 11

In my case, I was using wrong drivers to connect to Microsft SQL server

import pyodbc
print(pyodbc.drivers()) # this will list down all the drivers available in your local PC

I noticed that I was using "SQL server" as a driver for connecting to "Microsoft SQL server". I installed new ODBC driver and it worked well.

Don't use "MySQL ODBC Unicode/ANSI driver" to connect to Microsoft SQL servers. Instead install latest (or older as per requirement) from here

Also make sure that SQL server is allowed to accept remote connections.

Pogge

Answer 12

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.

Aboutface

Answer 13

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.

Biomedicine

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

Adrenal

Answer 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

Rondon

Answer 16

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

Fireback

Answer 17

Make sure you have all drivers and db engine installed

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

Indeterminism

You might need the MS Access 2013 runtime depending on your version of office as explained on this Microsoft trouble shooting page. – Neubauer

For me it was the db engine that was missing – Capsaicin

Sorry for the down vote, I tried removing it but it's locked. – Neubauer

No worries, i don't care for the points :) And leaving the link here might help someone someday who knows – Capsaicin

Answer 18

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.

Alamode

Answer 19

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

Rejoin

Answer 20

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

Bezanson

Answer 21

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.

Seductive

Answer 22

My issue; trying to connect using MFA and my email, which has the permissions for this database. I also made sure that I could connect to the database in the first place, just to be sure.

First of all, look at all the drivers:

import pyodbc   
print(pyodbc.drivers())

Now you can set up your connection string; I had the list of available drivers. For Azure Authentication (now Microsoft Entra Id) this code worked for me:

connstring = 'Driver={ODBC Driver 17 for SQL Server};Server=<SERVER_NAME>;Database=<DB_NAME>;Uid=<me@myemailaddress>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryInteractive'

This opened a small window asking me to connect and add the Authentication code.

Jamshid

Answer 23

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

Hospers

Answer 24

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

Gigolo