#1:
To use MSOLEDBSQL 18.x, there is no need to set Provider, it is the default value when Provider is not set.
SQLCA.DBMS = "MSOLEDBSQL SQL Server" SQLCA.AutoCommit = False SQLCA.DBParm = ""
To use MSOLEDBSQL 19.x, set Provider='MSOLEDBSQL19'. MSOLEDBSQL 19.x is supported starting from version 2022.
SQLCA.DBMS = "MSOLEDBSQL SQL Server" SQLCA.AutoCommit = False SQLCA.DBParm = "Provider='MSOLEDBSQL19'"
You can also directly copy the syntax from the Database Profiles dialog: select MSOLEDBSQL SQL Server from the Installed Database Interfaces treeview, click the New button; in the Connection tab, set Provider to MSOLEDBSQL (if using 18.x) or MSOLEDBSQL19. The connection string is automatically generated in the Preview tab.
Version 18 syntax will only work with Version 18 driver (it cannot work with Version 19 driver). Version 19 syntax will work with either Version 18 or 19 driver (it works with Version 18 driver if no Version 19 driver is installed).
#2:
Due to a change in Microsoft OLE DB Driver 19.x from Microsoft OLE DB Driver 18, you may fail to connect to the database. Please refer to the following about the change and the possible solution.
With the following combination of settings:
-
{base_registry_key}\Flag1 being the default 0 ({base_registry_key}: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI{major_version}.0\GeneralFlags);
-
{base_registry_key}\Flag2 being the default 0;
-
The "Encrypt Data" option in the DB profile being checked;
-
The "Trust Server Certificate" option in the DB profile being checked
If you use Microsoft OLE DB Driver 19.x, encryption occurs only if there's a verifiable server certificate, otherwise the connection attempt fails. If you use Microsoft OLE DB Driver 18, encryption always occurs, but may use a self-signed server certificate.
Therefore, if you have checked the "Encrypt Data" and "Trust Server Certificate" options in the DB profile, please take any of the following solutions to ensure the encryption will work correctly in Microsoft OLE DB Driver 19.x:
-
Change the {base_registry_key}\Flag2 to 1, or,
-
Provide a verifiable server certificate.
