Connect to SQL Server with user credentials of another domain

Question

How can I connect to a SQL Server database using user login/password that is in another domain?

If I use my account to connect to DB, it works fine:

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=server_name;DATABASE=testdb;UID=MY_Domain\\username;PWD=pass; Trusted connection=YES')

But I need to use another user's credentials like

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=server_name;DATABASE=testdb;UID=Another_Domain\\username;PWD=pass; Trusted connection=YES')

When I try the latter I get an error of "failed login for MY_Domain\username" rather than for the user "Another_Domain\username".

In both cases by running SQL Server Management Studio I can use Windows Authentication to connect to the db.

Answer 1

You can not pass a UID and Password and set Trusted_connection=True (your second connection string) to connect as a (impersonated) windows user. You can either connect as a SQL Server user (username and password) or as a windows authenticated user (trusted connection).

Your code should impersonate the windows user (as SSMS does) and then set Trusted_connection=True only.

This MSDN page WindowsIdentity.Impersonate has an example.

Since this works from SSMS it suggests there is the necessary trust between the domains.