1

I am able to login to SQL Server in SSMS using Windows authentication.

{Image removed due to exposure of real email address}

But when I try the same using pyodbc it fails:

import pyodbc

cnxn_str = (
    r'DRIVER=ODBC Driver 17 for SQL Server;'
    r'SERVER=.\SQLEXPRESS02;'
    r'Trusted_Connection=yes;'
    r'DATABASE=Scenario2025Max_Release 5.0_0523586A6813B825D031545D6E25A815;'
)

cnxn = pyodbc.connect(cnxn_str)

I get this error:

Traceback (most recent call last):

File "", line 30, in
pyodbc.InterfaceError: ('28000', '[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'MicrosoftAccount\XXXXXXXX@outlook.com'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot open database "Scenario2025Max_Release 5.0_0523586A6813B825D031545D6E25A815" requested by the login. The login failed. (4060); [28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'MicrosoftAccount\XXXXXXXXAccount@domain.com'. (18456); [28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot open database "Scenario2025Max_Release 5.0_0523586A6813B825D031545D6E25A815" requested by the login. The login failed. (4060)')

How is this possible?

UPDATE - Added SQL Server logs for the error run - enter image description here

Rohan Bapat
  • 343
  • 2
  • 4
  • 17
  • 1
    Is you database ***really*** called `Scenario2025Max_Release 5.0_0523586A6813B825D031545D6E25A815`? With respect, that's a terrible name for a database. – Thom A Oct 16 '21 at 12:30
  • Unfortunately its a system generated database name. – Rohan Bapat Oct 16 '21 at 12:34
  • 1
    Have a look at the SQL Server's logs and get the *real* connection error. If you don't understand it, then [edit] your question and add that error to the question (and this time if it contains PII ***don't*** expose said PII). – Thom A Oct 16 '21 at 12:35
  • I am not sure what you mean by "real connection error". I have posted the pyodbc connection message above. There is no connection error when I try connecting through SSMS. – Rohan Bapat Oct 16 '21 at 12:40
  • 3
    That's not the true authentication error... Authentication errors returned from SQL Server are explicitly vague on purpose. Get the ones from the logs. – Thom A Oct 16 '21 at 12:42
  • 1
    @RohanBapat, common reasons for the "cannot open database" error during login is the user account (MicrosoftAccount\XXXXXXXX@outlook.com) has not been added as a user to the database or the database does not exist or is unavailable for some reason. – Dan Guzman Oct 16 '21 at 15:31
  • In SSMS look at "SQL Server Logs" under "Management" in the Object Explorer – Charlieface Oct 16 '21 at 18:52
  • I have added the SQL Server logs from the run. Also the user account has been added to the database. I can access the database from SSMS using Windows Authentication, but not using pyodbc – Rohan Bapat Oct 17 '21 at 09:18
  • 1
    We need the line from the logs that actually reports the error – Charlieface Oct 17 '21 at 10:33
  • Those logs aren't helpful, they don't contain the authentication error(s), and don't post images of code/errors – Thom A Oct 17 '21 at 21:26
  • @RohanBapat, [this](https://stackoverflow.com/questions/16515420/connecting-to-ms-sql-server-with-windows-authentication-using-python/16515836) may help. – Zhorov Oct 18 '21 at 12:31

1 Answers1

0

I went through at least a hundred web pages over 2 full days on this, tried everything that was posted on StackOverflow threads, SQL/pyodbc/connectionsstrings blogs - restarting SQL Server, restarting my computer, modifying connection strings, changing SQL Server user permissions, but nothing worked.

Ultimately, I re-installed SQL Server entirely and I don't get the login failed error anymore, at least for now.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Rohan Bapat
  • 343
  • 2
  • 4
  • 17