Power Pivot - Impossible to change connection from SQLOLEDB to MSOLEDBSQL - Wizard wont keep the change

Question

Hi

I have a PowerPivot model that uses SQLOLEDB and was working fine connecting to SQL Server 2012 (on Windows server 2012)
We upgraded our serveur from SQL2012 to SQL2019 on an Azure VM. (ForceEncryption is off (=0) on the server)
Now provider SQLOLEDB does not connect to new server IN POWERPIVOT Existing Connection Wizard

Note : all machines have Microsoft OLE DB Driver for SQL Server v18.5 (or 18.6.4) (Tried 18.3 as well)

So I went to Excel, PowerPivot, Manage, Existing Connection, Edit the connection

Change for new server of course

Clicked (Advanced)

Changed provider from SQLOLEDB to MSOLEDBSQL
set Use Encryption for data=False (Not "Optional" which is for driver 19 that nobody wants. I did tried v19 - this absolutly never connected even with Encryption for data=Optional)
set Trust Server certificate=True (but anyway the server does not have any)
Click (Test connection). It connects successfully.
Then click (OK) , close Wizard.
There is another (Test Connection) in this window. It fails.
I go back to (Advanced) and the Provider is back to SQLOLEDB. The change to MSOLEDBSQL was not saved.

I copied the generated connection string and pasted it in one of my other c# program and it connects
The connection string is correct. PowerPivot wizard does not keep the selected provider after clicking (OK)

When the connection string has Provider=SQLOLEDB I get an SSL error (even when Use Encryption for data=False)
When the connection string has Provider=MSOLEDBSQL it connects. Unfortunatly the connection string show MSOLEDBSQL only while in the Advanced windows.
It revert back to SQLOLEDB has soon as the Advanced window is closed.

Use Encryption for data=False in all cases

I created a new Workbook, started PowerPivot, Add connection from database, Sql Server .... same problem once provider is changed and return to edit it is not changed.

So instead in this new workbook, I select from other source, , Selected Other(OLEDB/ODBC) (IF I select Sql Server it wont work) pasted the connect string that was generated from previous wizard ... IT WORKS.
If I dont paste the connectstring and use the Wizard, in the provider list there are:
Microsoft OLE DB Driver for SQL Server (This one works. It is proabably v18)
Microsoft OLE DB Provider for SQL Server (This one does not work. This is probably a PRE v18)

So for a brand new workbook and selecting OtherSource, Other (OLEDB/ODBC) it works
The problem is that I have several Excel file with PowerPivot models. I cannot create new ones
I need to be able to change the connection in current models

I have several Terminal Servers all on Azure now.
Some recent Azure VMs (showing Windows 10 and Excel 2022 and other in WIndows Server 2016 and Excel 2008)

My Azure VMS are:
Windows 10 and Excel 2022 build 14931. Works
Windows Server 2019 and Excel 2108. Works

All others VMs are running WIndows Server 2016 And Excel 2008 - NONE works (PowerPivot unable to connect after clicking (OK) on the Advanced window. Works only with the (Test connection) button.

Using EXACT SAME Workbook file.

I cannot simply upgrade the OS
I cannot simply upgrade Excel

Is it an Excel version issue OS version Issue ?

Not a TLS issue since the (Test connection) button works, and the connection string using Provider=MSOLEDBSQL with Encryption for data=False does work in a c# program of mine on all machines.

help please

Answer 1

I'm not sure why you tagged this with sql-server-general only, since this apparently an Excel problem.

But maybe the issue is that this antique version of Excel does not know about MSOLEDBSQL, and therefore drops it on the floor.

Microsoft OLE DB Driver for SQL Server (This one works. It is proabably v18)

Yes, this is MSOLEDBSQL.

Microsoft OLE DB Provider for SQL Server (This one does not work. This is probably a PRE v18)

Yes, this is SQLOLEDB. In between these, there were also SQLNCLI, SQLNCLI10 and SQLNCLI11, but you would need to install them to be able to use them. (SQLOLEDB comes with the OS.)

Answer 2

Hi
I am posting in this channel because I first posted in Exel channel and I was told to post in SQL channel. So here I am.
Never mind this question. I abandoned this route.
Excel 2020 ++ only will be able to run PowerPivot. Seem Excel 2008 is not compatible and aI spent to much time on this.
Thank you