Unable to perform windows authentication for SQL Server using python

Question

My Python code shown below is written to create a SQL Server connection using Windows authentication. I have constraints to use adodbapi library for database connectivity.

Please can anyone tell me what is missing from this code? I referred to the library's documentation, but there is nothing mentioning Windows authentication.

I referred to a lot of articles about that exception. But they seems there's no help to understand the nature of exception and its resolution.

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Code:

import configparser
import adodbapi
config = configparser.ConfigParser()
config.read("C:/plugin/configsql.ini")
_SERVER_NAME = config['SQL']['SERVER_NAME']
_DATABASE = config['SQL']['DATABASE']
conn = adodbapi.connect("PROVIDER=MSOLEDBSQL;Data Source={0};Database={1};Integrated Security = True;".format(_SERVER_NAME,_DATABASE))
print(conn)

Exception:

Traceback (most recent call last):

File "C:\Arelle-master\venv1\lib\site-packages\adodbapi\adodbapi.py", line 113, in connect
co.connect(kwargs)

File "C:\Arelle-master\venv1\lib\site-packages\adodbapi\adodbapi.py", line 275, in connect
self.connector.Open() # Open the ADO connection

File "", line 3, in Open
File "C:\Arelle-master\venv1\lib\site-packages\win32com\client\dynamic.py", line 287, in ApplyTypes
result = self.oleobj.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args)

pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Provider', 'Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.', None, 1240640, -2147217887), None)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

File "winAuthentication.py", line 8, in
conn = adodbapi.connect("PROVIDER=MSOLEDBSQL;Data Source={0};Database={1};Integrated Security = True;".format(_SERVER_NAME,_DATABASE))

File "C:\Arelle-master\venv1\lib\site-packages\adodbapi\adodbapi.py", line 117, in connect
raise api.OperationalError(e, message)

adodbapi.apibase.OperationalError: (com_error(-2147352567, 'Exception occurred.', (0, 'Provider', 'Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.', None, 1240640, -2147217887), None), 'Error opening connection to "PROVIDER=MSOLEDBSQL;Data Source=MSSQLSERVER01;Database=TESTDB;Integrated Security = True;"')

LaraRaraBoBara · Accepted Answer · 2020-01-28 20:19:30Z

1

Have you tried Trusted_Connection=yes? Here is my connection string that uses windows authentication (using pyodbc) but should be the same connection parameter, not Integrated Security.

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=ServerName;'
                      'Database=DatabaseName;'
                      'Trusted_Connection=yes;')

Or perhaps Integrated Security = SSPI, found mentioned here http://adodbapi.sourceforge.net/quick_reference.pdf

 'Integrated Security=SSPI'

edited Jan 28, 2020 at 20:19

answered Jan 28, 2020 at 20:10

LaraRaraBoBara

6581 gold badge6 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2961127 Over a year ago

It doesn't works. It throws exception -

pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Driver for SQL Server', 'Invalid connection string attribute', None, 0, -2147217843), None)

LaraRaraBoBara Over a year ago

Also, you may want to look at these older (but perhaps similar) problems/answers? stackoverflow.com/questions/6086341/…

user2961127 Over a year ago

Thanks you. It worked. Updated 'Integrated Security=SSPI'

Collectives™ on Stack Overflow

Unable to perform windows authentication for SQL Server using python

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related