You might possibly be trying to use 32 bit legacy drivers.
Try running C:\Windows\SysWOW64\odbcad32.exe to create a DSN that connects to your SQL Server database using the 32 bit SQL Server driver. First test the connectivity using the GUI.
Then try specifying your DSN in your connection string like so:
DSN=myDsn;Uid=myUsername;Pwd=;
The 64-bit version is located in:
C:\Windows\System32\odbcad32.exe
P.S. I've not tried this but the provider name for the 32-bit SQL Server is actually "SQL Server". It'd be nice if all you had to do was replace SQLNCLI11 with SQL Server. Here's my file DSN I created with the 32 bit version--notice the driver...
[ODBC]
DRIVER=SQL Server
UID=sa
WSID=P13-0000
APP=Microsoft® Windows® Operating System
SERVER=mcdba1
We had a nightmare setting something similar up, specifically with the issue of connecting to multiple Informix environments. I had several Informix instances that needed to be linked to a single SQL Server instance for warehousing purposes, and was told by our resident SME that this was impossible; only a single Informix instance could be referenced per server.
I would consistently receive the same errors as you, and nothing I was able to dredge up online proved helpful.
Ultimately, the solution had several parts, of which you've done the first few:
- The correct
IBM Informix ODBC Driver must be installed on the database server.
- Use the SetNet32 application provided with the drivers and set up the Server Information tab appropriately. Additionally, set up a corresponding System DSN entry.
The trick with the Linked Server setup is to use the following (items in codeblocks are literal):
- Provider:
Microsoft OLE DB Provider for ODBC Drivers
- Product Name:
Ifxoledbc
- Data Source: $YourSystemDSNName
- Provider String
- Driver =
{IBM INFORMIX ODBC DRIVER}
- Database = $YourDatabaseName
- etc...
If you're more of a manual SQL kind of person, see below. The provider string is pulled into a variable for inline commenting purposes only, while still having executable code. Modify as needed:
DECLARE @provider NVARCHAR(4000);
SET @provider = N'Driver={IBM INFORMIX ODBC DRIVER};'
+ N'DATABASE= ;' --Informix DB name
+ N'HOST= ;' --Informix Hostname or IP adddress
+ N'SERVICE= ;' --Informix service name, ex. ifx1_tcp1
+ N'PROTOCOL= ;' --Informix protocol, ex. onsoctcp
+ N'SERVER= ;' --Informix server, ex. ifx1
+ N'DB_LOCALE=en_US.819; CLIENT_LOCALE=en_US.819;'; --Change to your locale, as needed
EXEC master.dbo.sp_addlinkedserver
@server = N'UCCX', --Linked Server system name
@srvproduct=N'Ifxoledbc',
@provider=N'MSDASQL',
@datasrc=N' ', --Your System DSN Name
@provstr= @provider;
Since you're dealing with a single instance, it should be enough to simply match the values used to create your System DSN. If you need to add additional linked servers for other instances, that can be done following exactly the same format described above without additional DSN entries.
I hope that helps.
Best Answer
See Does Microsoft OLE DB Provider for SQL Server support TLS 1.2 and the authoritative answer from Dan Guzman. He references a newer version of OLEDB that supports TLS 1.2, which can be downloaded at Microsoft® OLE DB Driver 18 for SQL Server®
And to specifically answer your questions, there is no way for us to know if the installer can be forced to use a different provider. It's certainly possible if it uses a connection string template that can be modified, but based on my experience I would say it's highly unlikely you'll be able to modify the installer to use a different provider.
As for logging what type of connection--see if the SQL Server error log contains any information when the failure occurs. You can also set up an extended event in SQL Server to capture information about logins (there is "Connection Tracking" template). The client_app_name field collected by "Connection Tracking" template will include the information that the client passes about the provider. This can sometimes be blank, though, as it depends on the client to set that information. Typically a provider will have that information included automatically unless the developer does something to override it.