This was an interesting rabbit hole to go down. The Workflow Direct Connections Data Source Configuration docs have a section for
See a list of the direct connection data source drivers that Workflow supports.
- Microsoft Access 2003
- Microsoft Access 2007/2010
- CSV
- CSV 2010
- Microsoft Excel 2003
- Microsoft Excel 2007/2010
- Microsoft FoxPro
- Informix
- OracleOdbc
- OracleODP
- Pervasive
- MSSQL
- MySQL
Which seem to conditionally appear in the dropdown based on if you have Data Source Type of ODBC, OLE, or Direct selected.
What makes these "supported" is that code exists for each that knows how to construct a proper driver connection string with a combination of user-supplied and hardcoded values. Each one appears to check if it's installed.
Here, for example, are some snippets from the FoxPro code:
/// Fox pro odbc driver.
...
[DataSourceInstallCheckByType("Microsoft Visual FoxPro Driver", "ODBCDriverNotInstalled")]
...
/// Set the odbc connection string.
/// Access, Excel, and csv use the same provider, but have slightly different
/// connection strings
protected override void InitializeConnectionString()
{
string ext = Path.GetExtension(this.DatabaseName).ToUpperInvariant();
ext = ext.Replace(".", string.Empty);
if (string.IsNullOrEmpty(ext))
{
ext = "DBF";
}
this.ConnectionString = "Driver={Microsoft Visual FoxPro Driver};" +
"SourceType=" + ext + ";" +
"SourceDB=" + this.DatabaseName + ";" +
"Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO";
}
...
Pay attention to this line:
[DataSourceInstallCheckByType("Microsoft Visual FoxPro Driver", "ODBCDriverNotInstalled")]
That first parameter is the specific name of the ODBC driver Workflow is going to check for.
There is a single "Sql" driver file, much like the FoxPro one above. It starts with:
/// SQL ODBC adapter class.
...
[DataSourceInstallCheckByType("SQL Server", "ODBCDriverNotInstalled")]
....
protected override void InitializeConnectionString()
{
if (this.UseSSPI)
{
this.ConnectionString = "Driver={SQL Server};" +
"Server=" + this.Host + ";" +
"Database=" + this.DatabaseName + ";" +
"Trusted_Connection=yes;";
}
else
{
this.ConnectionString = "Driver={SQL Server};" +
"Server=" + this.Host + ";" +
"Database=" + this.DatabaseName + ";" +
"UID=" + this.User + ";" +
"PWD=" + this.Password + ";";
}
}
Again, note the ODBC driver name of "SQL Server". Also note the connection string constructor starting with "Driver={SQL Server};".
To check if the "supported" driver in question is installed, Workflow pulls the available values from the registry. Specifically, it checks the registry key:
\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers
Which contains a list of registered (?) ODBC drivers and a value indicating if they're installed.
(Note that "SQL Server" is a specific registered ODBC driver, distinct from "ODBC Driver 17/18 for SQL Server")
Then for each "Installed" driver, it loops through and pulls info for the driver-specific subkey:
We see here that the actual "SQL Server" driver is C:\Windows\System32\SQLSVR32.dll.
This turns out to be the first generation ODBC driver for SQL Server that, while ancient, is still supported. From Microsoft's Driver history for Microsoft SQL Server:
ODBC
There are three distinct generations of Microsoft ODBC drivers for SQL Server. The first "SQL Server" ODBC driver still ships as part of Windows Data Access Components. This driver isn't recommended for new development. Starting in SQL Server 2005, the SQL Server Native Client includes an ODBC interface and is the ODBC driver that shipped with SQL Server 2005 through SQL Server 2012. This driver also isn't recommended for new development. After SQL Server 2012, the Microsoft ODBC Driver for SQL Server is the driver that is updated with the most recent server features going forward.
It was updated in 2020 to support TLS 1.2:
Stack Exchange: Can SQLSRV32.DLL broker a TLS 1.2 handshake?
See also: Compatibility of ODBC driver SQLSRV32.dll with SQL Server versions
ConnectionStrings.com shows that this 1st gen "SQL Server" driver is identified as such in connection strings: Microsoft SQL Server ODBC Driver connection strings (Driver={SQL Server};)
Compared to: Microsoft ODBC Driver 17 for SQL Server connection strings (Driver={ODBC Driver 17 for SQL Server};)
So to wrap that up:
- The Workflow built-in "Sql" driver option uses the 1st gen "SQL Server" ODBC driver.
- "ODBC Driver 17 for SQL Server" (or 18, etc.) will not appear in the "Direct connections" built-in list, because the list is hardcoded.
That seems less than ideal and I'll bring it up internally. The core Workflow ODBC code is well over a decade old, and I suspect if this came up in the past, it was a "if it 'ain't broke, don't fix it" sort of thing.
If you want to use the current generation ODBC Driver for SQL Server, you will need to create a System DSN through the ODBC Data Source Administrator and explicitly select the driver version you want to use. Then select that DSN through Workflow's "Windows ODBC" option.
For Data Source Name (DSN) entries, Workflow looks these registry keys for the list and loops through them. System DSNs go under HKEY_LOCAL_MACHINE. Use a System DSN, not a user DSN, unless Workflow is running as an AD service account and you want to specifically restrict access/visibility to that account by creating a User DSN under the service identity.
\HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
