0

I enter a new Windows username and password with run as different user to connect to SSMS. I need to import the data there into Python.

However I get an error: InterfaceError: ('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)")

I am trying the following code:

import pyodbc 

conn = pyodbc.connect('Driver={SQL Server Native Client 11.0};'
                      'Server=Server;'
                      'Database=Database;'
                      'UID=domain\username;'
                      'PWD=password;')
Ezgi Turalı
  • 1
  • 1
  • 1
    You *can't* pass Windows Authentication credentials like SQL authentication. Either use a trusted connection and run the application as the appropriate user, or use impersonation (not sure how you do that in Python). – Thom A Dec 12 '22 at 12:59
  • On a separate note, why are you using the SQL Server Native Client? That's was discontinued many years ago. – Thom A Dec 12 '22 at 13:00
  • I will try impersonation. My company uses SQL Server Native Client, I cannot object :D – Ezgi Turalı Dec 12 '22 at 13:07
  • I strongly recommend you do. You really should be using the latest OLEDB or ODBC drivers. SQL SERVER Native Client doesn't support *any* of the supported SQL Server versions apart from SQL Server 2014, and that has less than 2 years extended support left. – Thom A Dec 12 '22 at 13:09
  • @EzgiTuralı why are you trying to connect as another user in the first place? The whole point of using Windows Authentication is to use the current account instead of having to store credentials. Using impersonation means you'll have to store credentials once again. – Panagiotis Kanavos Dec 12 '22 at 13:18
  • @EzgiTuralı as for using SQL Server Native Client, it's not only discontinued, Microsoft's own Python tutorials say they support the ODBC provider. `My company uses` isn't a good reason - do they really have a policy saying `Intentionally ignore Microsoft's guidance and forcefully install obsolete drivers that are guaranteed to cause errors`? The obsolete driver is *not* available by default on machines, you have to install it. Why install that instead of the *good* driver? – Panagiotis Kanavos Dec 12 '22 at 13:21
  • @PanagiotisKanavos For the work I need to do, I need to connect to a different database with a different id. But my windows user needs to stay the same. When I look at ODBC, I can see ODBC Driver 17 for SQL Server and SQL Server, I tried them too, but I get the same error. – Ezgi Turalı Dec 12 '22 at 13:33
  • You'll always get this error if you try to use Windows credentials as if they were SQL Server credentials. That's simply *not* how Windows Authentication works. With Windows authentication, instead of specifying a username/password, you use `Integrated Security=True` and the client will connect using the current user's account. You don't need to specify or store an account and password. That works without problem in all drivers and versions, as long as the client and server machines are in the same domain. – Panagiotis Kanavos Dec 12 '22 at 13:48

0 Answers0