I'm trying to switch my MSOLEDBSQLB connection string to support older ADO Recordset & MS DataShape connections to use TRUSTED style connection string and not able too. I have SQL native client trusted connection working for my .NET Connection. But need it to work for OLEDBSQL. By the way I installed the latest OLE DB Driver as well. That is why it added another version "MSOLEDBSQL.1" reference to provider.
Note this connection string is working in .net application on same app server connection string for sql native ver. 10 so I'm assuming permissions working (Server=DBSERVER1;Database=MyDB;Trusted_Connection=True;)
This connection works! Note this is a Windows Server/SQL Server 2019. So the Provider is referencing the latest working MSOLEDBSQLBdriver.
WORKS if using SQL Credentials
Data Provider=MSOLEDBSQL.1;DataTypeCompatibility=80;Provider=MSDataShape;Data Source=DBSERVER1;Database=MyDB;MARS Connection=True;MarsConn=Yes;User Id=myDBSQLUser;Password=#########;"
DOES NOT WORK using Trusted
Provider=MSOLEDBSQL.1;SERVER=DBSERVER1;Database=MyDB;Integrated Security=SSPI; Provider=MSOLEDBSQL.1;SERVER=DBSERVER1;Database=MyDB;Trusted_Connection=yes;
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
I've seen it's supported in the MS docs, just can't get it to work. I also have my application using Impersonate=true.