CODEWITHSUNDEEP
pythonsql-serverwindowsauthenticationodbc
par
par

Reputation: 103

Python ODBC Connect to SQL Server with Service Account (trusted_connection=no)

I connect to my server no problem with SQL Alchemy or Pyodbc so long as I use a trusted connection:

pyodbc.connect("Driver={SQL Server};Server=myServer;Port=1433;Database=myDB;trusted_connection=yes")

sqlalchemy.create_engine('mssql://myServer/myDB?trusted_connection=yes&driver=SQL+Server')

But I need to connect with a service account with basic Windows Authentication. When I try to add the UID/PWD like so:

sqlalchemy.create_engine("mssql+pyodbc://myUserName:myPassWord@myServer?driver=SQL+Server?trusted_connection=no")

pyodbc.connect('DRIVER={SQL Server};SERVER=myServre;DATABASE=myDB;UID=myUserName;PWD=myPassword')

I get the error "Login failed for user 'myUserName'. (18456) (SQLDriverConnect); [28000] "

Bonus try:

sqlalchemy.create_engine("mssql+pyodbc://myUserName:myPassword@myDB?driver=SQL+Server?trusted_connection=no")

Returns '[Microsoft][ODBC Driver Manager] Data source name too long (0) (SQLDriverConnect)'

Using SQL Server 2018.

I've verified that the service account has all the right permissions on the DB I'm trying to connect to. FWIW, I've also tried connecting using my own Windows credentials instead of the service account's (but with trusted_connection=no) and I get the same error messages.

Upvotes: 1

Views: 5931

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89141

get the error "Login failed for user 'myUserName'. (18456) (SQLDriverConnect); [28000] "

None of the Microsoft ODBC drivers support using Windows Integrated Authentication (NTLM or Kerberos) using provided credentials. This connection string

DRIVER={SQL Server};SERVER=myServre;DATABASE=myDB;UID=myUserName;PWD=myPassword

is for SQL Auth, where you have a login and a database user created in SQL Server.

eg:

use mydb
create login myUserName with password='myPassword'
create user myUserName for login myUserName
grant select to myUserName

To use Windows Auth with this driver you have to run your program as the target user, do Windows-level impersonation, store a credential in the Windows Credential store, or use runas /netonly.

Upvotes: 3

Related Questions