Error 28000: Login failed for user DOMAIN\\user with pyodbc

Question

I am trying to use Python to connect to a SQL database by using Window authentication. I looked at some of the posts here (e.g., here), but the suggested methods didn't seem to work.

For example, I used the following code:

cnxn = pyodbc.connect(driver='{SQL Server Native Client 11.0}',
                      server='SERVERNAME', 
                      database='DATABASENAME',               
                      trusted_connection='yes')

But I got the following error:

Error: ('28000', "[28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]
Login failed for user 'DOMAIN\\username'. (18456) (SQLDriverConnect); [28000] [Microsoft]
[SQL Server Native Client 11.0][SQL Server]Login failed for user 'DOMAIN\\username'. 
(18456)")

(Note that I replaced the actual domain name and user name with DOMAIN and username respectively, in the error message above.)

I also tried using my UID and PWD, which led to the same error.

Lastly, I tried to change the service account by following the suggestion from the link above, but on my computer, there was no Log On tab when I went to the Properties of services.msc.

I wonder what I did wrong and how I can fix the problem.

Gord Thompson · Answer 1 · 2020-12-02T19:13:29.557

Connecting from a Windows machine:

With Microsoft's ODBC drivers for SQL Server, Trusted_connection=yes tells the driver to use "Windows Authentication" and your script will attempt to log in to the SQL Server using the Windows credentials of the user running the script. UID and PWD cannot be used to supply alternative Windows credentials in the connection string, so if you need to connect as some other Windows user you will need to use Windows' RUNAS command to run the Python script as that other user..

If you want to use "SQL Server Authentication" with a specific SQL Server login specified by UID and PWD then use Trusted_connection=no.

Connecting from a non-Windows machine:

If you need to connect from a non-Windows machine and the SQL Server is configured to only use "Windows authentication" then Microsoft's ODBC drivers for SQL Server will require you to use Kerberos. Alternatively, you can use FreeTDS ODBC, specifying UID, PWD, and DOMAIN in the connection string, provided that the SQL Server instance is configured to support the older NTLM authentication protocol.

Is there a way to connect as another Windows User who is different to the one logged in? — Sandeep, Feb 20 '18 at 06:17
Sandeep - if the item is a stored procedure, it is possible to create an "Execute As" statement within the stored procedure that allows the query to run using pre-defined credentials. — Jamie, Dec 02 '20 at 19:03

score 7 · Answer 2 · answered Dec 29 '17 at 03:45

7

I tried everything and this is what eventually worked for me:

import pyodbc
driver= '{SQL Server Native Client 11.0}'

cnxn = pyodbc.connect(
    Trusted_Connection='Yes',
    Driver='{ODBC Driver 11 for SQL Server}',
    Server='MyServer,1433',
    Database='MyDB'
)

answered Dec 29 '17 at 03:45

Reddspark

6,934
9
47
64

1

that worked! for {ODBC Driver 17 for SQL Server} (SQL Express 2019) I had to remove the port number to make it work. – Hector Marcia Jul 06 '20 at 20:39

score 4 · Answer 3 · answered Jun 08 '16 at 03:59

4

Try this cxn string:

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;PORT=1433;DATABASE=testdb;UID=me;PWD=pass')

http://mkleehammer.github.io/pyodbc/

answered Jun 08 '16 at 03:59

Merlin

24,552
41
131
206

I assume I should replace `localhost` with the actual server name? Either way, I got errors... – Alex Jun 08 '16 at 04:15
This Worked !! Thanks. – pylearner Jun 07 '19 at 06:02
1

Microsoft's ODBC drivers do not use a Port= parameter. If you need to connect to a port other than the default (1433) you must append it to the Server argument with a comma, e.g., `Server = mydbserver.mycompany.com,49242` – Vamsi Sistla Mar 10 '20 at 23:15

score 1 · Answer 4 · answered Apr 05 '18 at 19:35

1

I had similar issue while connecting to the default database (MSSQLSERVER). If you are connecting to the default database, please remove the

database='DATABASENAME',

line from the connection parameters section and retry.

Cheers, Deepak

answered Apr 05 '18 at 19:35

Deepak Venugopal

11
1

MSSQLSERVER is the (database) server instance name, not the database name. A SQL Server instance can contain many databases but you will nearly always want to include the database name in the connection string so you are not hard coding the database name in individual queries etc. – Chris Walsh Apr 05 '18 at 19:47

score 0 · Answer 5 · edited Mar 01 '19 at 04:48

0

The first option works if your credentials have been stored using the command prompt. The other option is giving the credentials (UId, Psw) in the connection.

The following worked for me:

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=yourServer;DATABASE=yourDatabase;UID=yourUsername;PWD=yourPassword')

edited Mar 01 '19 at 04:48

MarredCheese

17,541
8
92
91

answered Mar 01 '19 at 01:41

JAFER

1

score 0 · Answer 6 · answered Jan 06 '20 at 04:36

import pyodbc   #For python3 MSSQL

cnxn = pyodbc.connect("Driver={SQL Server};"   #For Connection
                   "Server=192.168.0.***;"
                   "PORT=1433;"
                   "Database=***********;"
                   "UID=****;"
                   "PWD=********;")
cursor = cnxn.cursor()                        #Cursor Establishment
cursor.execute('select site_id from tableName')   #Execute Query

rs = cursor.fetchall() 
print(rs)

score 0 · Answer 7 · answered Jan 13 '20 at 03:56

A slightly different use case than the OP, but for those interested it is possible to connect to a MS SQL Server database using Windows Authentication for a different user account than the one logged in.

This can be achieved using the python jaydebeapi module with the JDBC JTDS driver. See my answer here for details.

score 0 · Answer 8 · answered Jan 09 '23 at 17:13

Note that you may need to change the authentication mechanism. For example, my database is using ADP. So my connection looks like this

pyodbc.connect(
    Trusted_Connection='No',
    Authentication='ActiveDirectoryPassword',
    UID=username,
    PWD=password,
    Driver=driver,
    Server=server,
    Database=database)

Error 28000: Login failed for user DOMAIN\\user with pyodbc

9 Answers9

Linked

Related