how to find ODBC driver-specific arguments

Question

Is there a way to programmatically discover the named arguments for ODBC connections?

For instance, using the "ODBC Driver 17 for SQL Server", the ODBC Data Source Administrator (in Windows) allows for authentication using a "Login ID" and "Password", yet when connecting programmatically, we need to use uid= and pwd=, respectively. These two configuration points are clearly listed in configuration documentation (e.g., https://db.rstudio.com/databases/microsoft-sql-server/), but there are several other configuration options that the driver supports that are less- or un-documented.

(For SQL Server, there are more docs, including https://learn.microsoft.com/en-us/sql/connect/odbc/windows/driver-aware-connection-pooling-in-the-odbc-driver-for-sql-server?view=sql-server-ver15, that lists several other arguments (though not their possible values). That works for this driver, though since this page has not been updated for Driver 17 we have to assume that all options are unchanged.)

There are programmatic ways to learn what drivers are available, and what pre-defined data sources are available:

odbc::odbcListDrivers()
#                             name        attribute value
# ...truncated...
# 33 ODBC Driver 17 for SQL Server       UsageCount     1
# 34 ODBC Driver 17 for SQL Server         APILevel     2
# 35 ODBC Driver 17 for SQL Server ConnectFunctions   YYY
# 36 ODBC Driver 17 for SQL Server        CPTimeout    60
# 37 ODBC Driver 17 for SQL Server    DriverODBCVer 03.80
# 38 ODBC Driver 17 for SQL Server        FileUsage     0
# 39 ODBC Driver 17 for SQL Server         SQLLevel     1

odbc::odbcListDataSources()
#                 name                   description
# 1             somedb ODBC Driver 17 for SQL Server
# 2 SQLite3 Datasource           SQLite3 ODBC Driver

but nothing to discover connection arguments. Perhaps something akin to this non-existent function:

discover_odbc_arguments("ODBC Driver 17 for SQL Server")
# [1] "Address"                "AnsiNPW"                "App"                   
# [4] "Database"               "Encrypt"                "PWD"                   
# [7] "Trusted_Connection"     "TrustServerCertificate" "UID"                   

(I used SQL Server as an example here, but I'm interested in more general methods. And since I'm looking for programmatic mechanisms, I'd prefer to avoid suggestions to read the docs for each driver.)

(I am not opposed to non-R methods.)

---

Some non-R attempts that did not work. Using a mssql odbc driver library as an example with known parameter patterns.

$ strings /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1  \
  | egrep -i 'Trusted.Conn|AnsiDPW|TrustServer'
### <no output>
$ grep -rli 'Trusted.Conn|AnsiDPW|TrustServer' /opt/microsoft/msodbcsql17/
### <no output>

Answer 1

You need to somehow call SQLBrowseConnect function to get all available parameters of connection string for specific ODBC driver or DSN. Unfortunately, as I can see this call isn’t implemented in R ODBC packages. See documentation and example code at the link for more information.