Connection String OLEDBSQL Using Trusted Connection

SQL Server Administration
1

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;

Result..

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.

2

What type of application is thia for? Web or cliient app? What technology

3

Web application. Connection string to ADO Recordset with queries using MSDataShape requirement. SQL Server and Web Application share Active Directory. As I mention the security works with NATIVE client connection to SQL, just can;t figure out how to make the OLEDB to use trusted with MSDataShape

4

Is the app running on IIS? Are you using application pool? Or are you running it manually from your ide?

5

Yes IIS v10 with .NET 4+ as the target framework. So Windows VMs running SQL 2019 in Azure. The application pool is running under a service account in AD. The service account has DB OWNER on the database target.

6

I think Trusted_Connection overrides app pool and instead uses the default NT AUTHORITY\ANONYMOUS LOGON' which is probably what IIS service is running as.

What user do you have set in the application pool when using Trusted_Connection ?

Basically your issue is not related to SQL but permissions in the windows OS

7

Just discovered this use. I will try this and let you know my results..

Provider=MSDataShape;Data Provider=SQLNCLI;Integrated Security=SSPI;Initial Catalog=dbName

8

Thanks.. does look like SPN or Delegation for the machine name for this service account shared..

Error I get in SQL Error Log
Login succeeded for user 'A2\Sql#######DB1'. Connection made using Integrated authentication. [CLIENT: 10.2.34.#]

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided. [CLIENT: 10.2.34.#]