CODEWITHSUNDEEP
sql-serverpython-3.xoledbconnectionadodbapi
user2961127
user2961127

Reputation: 1143

Unable to perform windows authentication for SQL Server using python

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;"')

Upvotes: 0

Views: 1535

Answers (1)

LaraRaraBoBara
LaraRaraBoBara

Reputation: 658

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'

Upvotes: 1

Related Questions