How to I connect to an On-Prem SQL server using pyodbc using a Service Account (not my own AD account)

user_17017893 0
Viewed 200

I am attempting to use pyodbc to connect to an On-Premise/Physical SQL server instance using an AD service account created for the purpose.

I'm building the connection string as:

import pyodbc 
   
server = 'FULLY_QUALIFIED_SERVER_NAME.xx.xxx.xxx,1433'
database = 'TargetDB'
username = 'DOMAIN\USERNAME'
password = 'PASSWORD' 
cnxn = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + server + ';UID=' + username + ';PWD=' + password + ';Authentication=ActiveDirectoryPassword;'
print(cnxn)
pyodbc.connect(cnxn)

When I run from cmd line:

PS C:\Conn_Sql\DA_CONNECT_SQL_TEST> python __init__.py
DRIVER={ODBC Driver 17 for SQL Server};SERVER=FULLY_QUALIFIED_SERVER_NAME.xx.xxx.xxx,1433;UID=DOMAIN\USERNAME;PWD=PASSWORD;Authentication=ActiveDirectoryPassword;
Traceback (most recent call last):
  File "__init__.py", line 12, in <module>
    pyodbc.connect(cnxn)
pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user ''. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user ''. (18456)")
PS C:\Conn_Sql\DA_CONNECT_SQL_TEST>

If you don't want to scroll, the error is: [SQL Server]Login failed for user ''

I have reached out to every resource I can internally, and there seems to be no one able to point me to an answer.

AS A NOTE: when I try to log into SQL server similarly, I get this same error. Text

The ONLY way I am able to connect using this account is by logging into the server using SSMS, launched AS the Service Account.

I hope this is something simple.

azure-active-directory sql-server python
1 Answers

The ODBC driver does not support Windows Authentication (NTLM or Kerberos) with credentials you provide. You must either

  1. Run the process as the target account or perform Windows Impersonation of the account, eg with runas or installing your code as a Windows Service with the target service account.

  2. Install a Windows Credential in the Windows Credential store for the user running the application.

  3. Run the app with runas /netonly to use provided credentials for NTLM authentication.

user_72977000
Related