Our analytical lab (chemistry) has an information management system (LIMS) that uses a Microsoft SQL Server database. Technicians interact with the system through a Windows form application that is closed-source and permits only SQLOLEDB.1, MSOLEDBSQL, and SQLNCLI11.1 (Native Client 11) connections (choices are picked from a dropdown box at login). Our production server is SQL Server 2016 running on Windows Server 2012.
The lab has several analytical instruments that are constrained to Win 7 and Win XP for data acquisition. This is an immutable constraint.
On the production setup, all machines make a connection with legacy SQLOLEDB.1 but Win 10 Machines can alternatively connect with MSOLEDBSQL, and SQLNCLI11.1 as well. Win 7 and XP machines cannot.
I am prototyping a SQL Server 2019 upgrade on a Ubuntu server. As configured, no machines can connect to it using the legacy SQLOLEDB.1 connection. The LIMS application is able to connect to it from Win 10 using either MSOLEDBSQL or SQLNCLI11.1. The Win 7 and XP machines cannot connect with any of the protocols.
If this is to work, I have two choices:
- Somehow configure MSSQL on the Ubuntu server to accept SQLOLEDB.1 connections.
- Somehow configure XP and Win 7 machines to make MSOLEDBSQL or SQLNCLI11.1 connections.
I cannot figure out how to do either one.
Error messages: "could not connect to the database" (when using sqloledb1 indicating that the server does not accept this protocol as configured). "item cannot be found in the collection corresponding to the requested name or ordinal" (when using Native Client 11 indicating that Win 7 does not have the necessary connection capabilities).
As asked, I need to either configure the server to accept SQLOLEDB1 or configure Win 7 to make SQLNCLi11.1 connections.
The Win 7 machine never attempts to make the Native Client 11 connection because it doesn't have the required libraries for this protocol. It does attempt to make the sqloledb1 connection but it cannot because the SQL Server 2019 implementation is not, at least yet, configured to negotiate this legacy oledb connection. There is no error in the server log. The connect simply fails.
I should add that the Win 7 machine is able to make a connection to the production SQL Server 2016 database. But… none of our Win 7 or XP machines has ever been able to make a connection with anything but legacy OLEDB.
The MSOLEDBSQL driver will not install out of the box. The error thrown is KB2999226 Check Failed. I downloaded KB2999226-x86.msu but manually installing it is beyond me because the only mechanism I can see for applying updates on XP is to do it through the web interface (non-existent site). I can extract the .msu but the sprawling directory tree is perplexing. Perhaps there's a way to configure the server to accept legacy SQLOLEDB.1.
Best Answer
SQL Server Native Client 10 works for Win 10, Win 7, and Win XP.
I'm not sure not sure it was necessary yet but, for Win XP, I also installed windowsxp-kb4019276-x86-embedded-enu (and hacked the registry to enable TLS 1 & 2).
$2M worth of Mass Spectrometers and a $100K ICP-AES can now use the LIMS with SQL Server 2019.