OLE DB Driver for SQL Server not working for external db host

Question

Hello, I am having the same issue with our Connection Manager (see link below - new device with VS2019 installed plus SQL Server Integration Services Projects / SQL Data Tools). The recommendation is to change the 'Provider' from SQL Server Native Client 11.0 to Microsoft OLE DB Driver for SQL Server or Microsoft OLE DB Provider for SQL Server. Neither of these work when connecting to an externally hosted database that previously worked with SNAC. On our supplier's suggestion we also installed ODBC Drive 17 for SQL Server but again received error messages that the user couldn't connect with no database or catalog options loading to select. I'm aware that there are other suggestions online regarding downloading older versions to support this but this seems counter intuitive given SQL Server Native Client 11.0 is deprecated. What is the recommended approach?

https://learn.microsoft.com/en-us/answers/questions/28268/sql-server-native-client-11-0-support-for-sql-serv

Answer 1

Supplier had created new profile with same username but different password.

Answer 2

Neither of these work What is the recommended approach?

"Not work" means what in details?

And the recommend approach for post is to provide usefull details, like error messages.

Answer 3

Hi @James, Thomas,

• You definitely should use Microsoft OLE DB Driver for SQL Server.
  Starting from 2018, all other Microsoft OLEDB providers are deprecated.
• By the way, you can use a UDL (Universal Data Link) file to test raw connectivity.

Supplier had created new profile with same username but different password.

Hopefully, that new profile resolved the issue.