I have the following scenario.

I received access to a Windows SQL server with Domain/Username authentication attached to my personal Windows Active Directory and also our team’s Service Account’s Active Directory. When I test with my Windows work station, I can confirm that, I am able to programmatically fetch data from the SQL server. I tested this within my Windows machine with two Python scripts. One used pyodbc, and other used pymssql.

pyodbc example (local)

import pyodbc
conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};"
                      "SERVER=<server><instance>,<port>;"
                      "DATABASE=<database>;"
                      "Trusted_Connection=yes;"
                      )

cursor = conn.cursor()
cursor.execute("SELECT * FROM database.table")

for row in cursor.fetchall():
    print (row) 
cursor.close()

print("Completed!")

pymssql example (local)

import pymssql
conn = pymssql.connect(host='<server>', server='<instance>', port='<port>', database='<database>') 
cursor = conn.cursor()
cursor.execute("SELECT * FROM database.table")

for row in cursor.fetchall():
    print (row)
cursor.close()

The two scripts worked well in my personal Windows laptop. As you can see, in both scenarios, I did not have to specify my username or password. For example, when using pyodbc, I used ‘Trusted_Connection=yes‘. As, I was executing this script from my personal laptop which received the permission to the external Windows SQL server, this script automatically authenticate my request to the targeted SQL server and enable me to query the data from it. Similarly, when using pymssql, I was able to retrieve data directly from my local laptop.

However, my end objective is to retrieve data when I execute the script in a Linux Kubernets environment. Therefore, I extend the above two scripts while including my Active Directory <Domain><Username> and <password>. However, in both scenarios, the code does not get executed as per my expectation.i.e. the use of Active Directory <Domin><Username> and <password> combination with in Linux environment, throw me errors. The extended pyodbc and pymssql scripts are provided below. The received error messages are also stated.

pyodbc example

import pyodbc
conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};"
                      "SERVER=<server><instance>,<port>;"
                      "DATABASE=<database>;"
                      "Trusted_Connection=yes;"
                      "UID=<domain><username>;"
                      "PWD=<password>;"
                      "Integrated_Security=SSPI"
                      )

cursor = conn.cursor()
cursor.execute("SELECT * FROM database.table")

for row in cursor.fetchall():
    print (row) 
cursor.close()

print("Completed!")

pyodbc error encountered

    conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};"
pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

pymssql example

import pymssql
conn = pymssql.connect(host='<server>', server='<instance>', port='<port>', database='<database>', user='<domain\username>', password='<password>', tds_version='7.0') 
cursor = conn.cursor()
cursor.execute("SELECT * FROM database.table")

for row in cursor.fetchall():
    print (row)
cursor.close()

pymssql error encountered:

    conn = pymssql.connect(host='<server>', server='<instance>', port='<port>', database='<db>', user='<domain>\<username>', password='<password>', tds_version='7.0')  
  File "src/pymssql/_pymssql.pyx", line 659, in pymssql._pymssql.connect
pymssql.exceptions.OperationalError: (20009, b'DB-Lib error message 20009, severity 9:nUnable to connect: Adaptive Server is unavailable or does not exist (<server>)nNet-Lib error during Connection timed out (110)n')

I primarily followed Stack Overflow article, titled, Authenticate from Linux to Windows SQL Server with pyodbc, and SQL Server windows authentication linux python to accomplish my task. But, the steps I followed did not help me yet.

What steps am I missing? Do you see an issue in my code, or do you have alternate suggestions?