4

The window user details is different from the Sql Server user I log in. So I had tried to use pyodbc connect to the database using the username(Admin_JJack) and password. But the connection show fails for the Window User(Jack) and I don't know where goes wrong.

my connection string :

connection = pyodbc.connect(
    "Driver={"SQL Driver"};"
    "Server= "ServerName";"
    "Database="DatabaseName";"
    "UID="UserName";"
    "PWD="Password";"
    "Trusted_Connection=yes"
)

pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'Jack'. (18456) (SQLDriverConnect);

How to connect to the database using sql server authentication ?

Jack Lim
  • 195
  • 2
  • 5
  • 16
  • Are `UID` and `PWD` credentials for a SQL Server login (SQL Server authentication) or are they for a Windows login that is different from the Windows account that is running the Python code? – Gord Thompson Nov 13 '18 at 03:16
  • Are you able to login using the same credentials with other database client? – Paulo Scardine Nov 13 '18 at 03:16
  • @PauloScardine Yes, I able to use that username(Admin_JJack) and password t log in at SSMS. – Jack Lim Nov 13 '18 at 03:20
  • @GordThompson I using Remote Desktop Connection to run the script and connection I build to login the sql server using the username(Admin_JJack) and password. – Jack Lim Nov 13 '18 at 03:30

3 Answers3

13

When you use "Trusted_Connection=yes" both the UID and PWD keys are ignored and the Windows account is used for authentication.

If you want to use the UID and PWD values for authentication instead of the Windows NTLM account you must use "Trusted_Connection=No" or remove this option from the connection string.

Trusted_Connection

Specifies whether a user connects through a user account by using either Kerberos [RFC4120] or another platform-specific authentication as specified by the fIntSecurity field (for details, see [MS-TDS] section 2.2.6.4).

The valid values are "Yes", "1", or empty string, which are equivalent, or "No". If the value "No" is not specified, the value "Yes" is used.

If the value is "No", the UID and PWD keys have to be used to establish a connection with the data source.

If the DSN key and the UID key are not included in the connection string or if the value of the UID key is an empty string, the value of the Trusted_Connection key has to be "Yes". If the Trusted_Connection key is not specified in the connection string, the value has to be obtained from the contents of the settings in the DSN key. If the Trusted_Connection key is not specified in DSN or if the given DSN does not exist, the default value is "No".

If the value of the Trusted_Connection key is "Yes", both the UID and PWD keys are ignored. Otherwise, the UID key has to be specified.

In Microsoft implementations, this user account is a Windows user account and NTLM authentication [MSDN-NTLM] is used when the value of the Trusted_Connection key is "Yes".

source: https://msdn.microsoft.com/

Paulo Scardine
  • 73,447
  • 11
  • 124
  • 153
4
slightly different syntax:

conn = pyodbc.connect('Driver={SQL Server};'
                        'Server=dbServer1;'
                        'Database=db1;'
                        'UID=user1;'
                        'PWD=uSer1Pass!;'
                        'Trusted_Connection=no;')
Lawrence Patrick
  • 341
  • 2
  • 6
0

For windows authentication you need to put Trusted_Connection=yes; instead of uid and pwd, if that does not work, try the DSN method (https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/connect-to-an-odbc-data-source-sql-server-import-and-export-wizard?view=sql-server-ver15) I recommend doing option 1, but it's your call

Garlic Bread Express
  • 105
  • 2
  • 16