1

When I try to run the below code I am getting following error. Tried referring other posts but it does not seem to help.

The server principal "{Myuser}" is not able to access the database "{database}" under the current security context. (916) (SQLExecDirectW)'

import pyodbc 
cnxn = pyodbc.connect("Driver={SQL Server};"
                      'Server=XXXXXXX;'
                      'Port=xxxx'
                      'Username={service_account}'
                      'Password={password of service_account}'
                      'Database={detabase};'
                      'Trusted_Connection=yes;')

results = cnxn.cursor()

results.execute('SELECT * FROM {database}.dbo.{tablename}')

for row in results:
    print(row)
Vaibhav
  • 102
  • 1
  • 9
  • it seems that you need to pass actual values in your connection string. – Dave C Dec 21 '18 at 17:01
  • I have passed actual values, i have masked them for the security purpose. – Vaibhav Dec 21 '18 at 17:06
  • my guess is that it is taking windows authentication rather than sql server authentication, since {Myuser} is my user name rather than the value passed in 'Username={service_account}' – Vaibhav Dec 21 '18 at 17:09
  • If you pass a user/pass - the trusted connection option shouldn't be set to true; that would override the user/pass and use your windows credentials. – Dave C Dec 21 '18 at 17:10
  • removing 'Trusted_Connection=yes;' or changing to 'Trusted_Connection=false;' is giving the same result. – Vaibhav Dec 21 '18 at 17:27
  • I'll leave this for the python experts then... the connection string above doesn't look properly formatted to me (based on a 2 minute google search), but I'm not familiar enough with python to say that concretely, yet it *does* seem you're hitting SQL Server. The error is pretty clear from SQL -- the wrong user seems to be in use. Are you missing ; after each item perhaps? Or is that a result of over editing? – Dave C Dec 21 '18 at 17:38
  • when i use cnxn = pyodbc.connect("Driver={SQL Server};" 'Server=XXXXXXX;' 'Port=xxxx;' 'Username={service_account};' 'Password={password of service_account};' 'Database={detabase};' 'Trusted_Connection=yes;') I get General network error. Check your network documentation – Vaibhav Dec 21 '18 at 17:50
  • Dave- appreciate your time on this, can you please upvote this questions. – Vaibhav Dec 21 '18 at 17:59

2 Answers2

2

If you are going to use Microsoft's ODBC driver for SQL Server and Trusted_Connection=yes (SQL Server Windows authentication) then you don't pass the Windows credentials in the connection string. Instead, you run your Python app as the Windows user. Ways to do that include

  • launching the Python app from a Windows command prompt using the RUNAS command, or
  • Shiftright_clicking your app's icon, then choose "Run as different user".

Another option might be to use the FreeTDS ODBC driver. It supports the older NTLM authentication protocol and allows you to specify the DOMAIN, UID, and PWD (for Windows authentication) in the connection string.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
0

This worked for me:

import pyodbc 
password = "<password>"
conn = pyodbc.connect("Driver={SQL Server};Server=<host>;Port=<port>;UID= 
<username>;PWD=" + password + ";Database=<dbname>;")
cursor = conn.cursor()

cursor.execute('SELECT * FROM <table>')

for row in cursor:
    print(row)
Vaibhav
  • 102
  • 1
  • 9