Can't connect to MS SQL from Windows

[AllanLeanderRostockHansen]

I can't connect to a MS SQL Server instance using Turbodbc using Python 3.7.6. I installed Turbodbc using conda, as demonstrated in the documentation/Geting started.

I have tested the connection parameters with pymssql, which are able to connect and retrieve data.

So, this works:

import pymssql  # v2.1.4
conn = pymssql.connect(
                       host=db_host,
                       user=db_user,
                       password=db_password,
                       database=db
                       )

But this doesn't – I've tried all three drivers, and with the port argument included and omitted, as well as changing from UID, PWD to user, password :

import turbodbc  # v3.3.0
opt = turbodbc.make_options(prefer_unicode=True)
connection = turbodbc.connect(
                     driver='SQL Server',
                     # driver='SQL Server Native Client 11.0',
                     # driver='ODBC Driver 17 for SQL Server',
                     server=db_host,
                     port=db_port,
                     database=db,
                     # user=db_user,
                     # password=db_password,
                     UID=db_user,
                     PWD=db_password,
                     turbodbc_options=opt)

Any suggestion to resolve this is highly appreciated!

This is the error message

---------------------------------------------------------------------------
Error                                     Traceback (most recent call last)
~\Anaconda3\lib\site-packages\turbodbc\exceptions.py in wrapper(*args, **kwds)
     49         try:
---> 50             return f(*args, **kwds)
     51         except InternError as e:

~\Anaconda3\lib\site-packages\turbodbc\connect.py in connect(dsn, turbodbc_options, connection_string, **kwargs)
     43     connection = Connection(intern_connect(connection_string,
---> 44                                            turbodbc_options))
     45 

Error: ODBC error
state: 28000
native error code: 18456
message: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'REDACTED_DOMAIN\REDACTED_USER.

During handling of the above exception, another exception occurred:

DatabaseError                             Traceback (most recent call last)
<ipython-input-47-80302b5a5ac8> in <module>
      9                      UID=db_user,
     10                      PWD=db_password,
---> 11                      turbodbc_options=opt)

~\Anaconda3\lib\site-packages\turbodbc\exceptions.py in wrapper(*args, **kwds)
     50             return f(*args, **kwds)
     51         except InternError as e:
---> 52             raise DatabaseError(str(e))
     53         except InternInterfaceError as e:
     54             raise InterfaceError(str(e))

DatabaseError: ODBC error
state: 28000
native error code: 18456
message: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'REDACTED_DOMAIN\REDACTED_USER'.

[keitherskine]

What you're doing appears correct, @AllanLeanderRostockHansen , but it would really help to see the error message you are getting.

[AllanLeanderRostockHansen]

Sorry, I've updated the original post

[keitherskine]

It looks like you're using a Windows username (i.e. "domain\user") to log into SQL Server. Not sure how this is working with pymsql, but you may want to try adding "Trusted_connection=yes" to your connection string (and not use user/password), or alternatively use SQL Server authentication.

Ref: https://stackoverflow.com/a/37702329/2777612

Also, I believe you'll get the same behaviour from all those drivers you've tried, but I would definitely use the latest driver "ODBC Driver 17 for SQL Server" if you can. The others are very old now.

[AllanLeanderRostockHansen]

The more modern 2017 driver doesn't change the outcome:

#  driver='ODBC Driver 17 for SQL Server',
#  Trusted_connection='no',
# 
#  Error message:
# - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
DatabaseError: ODBC error
state: 28000
native error code: 18456
message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'accdom01\m87125_adm'.

Using Trusted_connection='yes' does seem to work by signing on using my Windows credentials, except that my normal Windows user doesn't have access to the database (which is why the user I try to log on with have the _adm suffix), and thus fails when I try to log on:

#  driver='ODBC Driver 17 for SQL Server',
#  Trusted_connection='yes',
# 
#  Error message:
# - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
DatabaseError: ODBC error
state: 28000
native error code: 18456
message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'ACCDOM01\M87125'.