bcputlity wrapper doesn't allow setting of TrustServerCertificate flag

[AllenSuttonValocity]

We have recently update to use Microsoft's version 18 SQL Server ODBC connection from Ubuntu 20.04, rather than version 17.

The version 18 ODBC by DEFAULT encrypts the connection and insists on a valid certificate chain.

We have a DB which presents a self-signed certificate, and therefore the connection needs to have the flag TrustServerCertificate set to allow the self-signed certificate.

We don't anticipate this server having a public CA issued cert installed.

bcputility has the TrustedConnection flag catered for

But it appears there is no mechanism to allow the TrustServerCertificate flag to be set. (If there is, can you please give details of how)

I would suggest an update to the bcputility wrapper to allow the flag to be set

[tomroh]

Does modifying the entry in /etc/odbc.ini with TrustServerCertificate=true or TrustServerCertificate=yes solve the issue?

[AllenSuttonValocity]

Thanks for the prompt response.

In R code, the credentials for DB connection are set as follows:

set connection credentials

credentials <- sprintf( "server=%s; database=avm; TrustServerCertificate=yes; driver=ODBC Driver 18 for SQL Server; uid=%s; pwd=%s" , Sys.getenv("CONNECTION_SERVER") , Sys.getenv("CONNECTION_UID") , Sys.getenv("CONNECTION_PWD") )

connect to database

con <- do.call(what = dbConnect, c("drv" = odbc::odbc(), .connection_string = credentials))

This works fine to set the TrustServerCertificate flag.

But for the bcpImport statement, current connection is

upload model fsd

  bcpImport(
    x = cbind(as.data.table(.BY), scored_data[, .(address_detail_pid, raw_fsd, deflated_fsd = raw_fsd)])
    , server = Sys.getenv("CONNECTION_SERVER")
    , driver = "ODBC Driver 18 for SQL Server"
    , database = "avm"
    , table = Id(catalog="avm", schema="avm", table="staging_fsd")
    , username = Sys.getenv("CONNECTION_UID")
    , password = Sys.getenv("CONNECTION_PWD")
    , trustedconnection = FALSE
    , stdout = FALSE
    , batchsize = 1000000L
    , overwrite = FALSE
  )

The odbcinst.ini file has the following

[ODBC Drivers] ODBC Driver 18 for SQL Server=Installed

[ODBC Driver 18 for SQL Server] Description=Microsoft ODBC Driver 18 for SQL Server Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1 UsageCount=1

And odbc.ini has

[ValocitySQL] Driver = ODBC Driver 18 for SQL Server

The DSN isn’t really used at all, because the credentials are formatted into a connection string.

If we can use changes to all of that to define a DSN with the flag set, but override the UID and PWD with the values from environment variables (retrieved from a secret store) then that would be fantastic. (Especially if the DSN can be used for both R to DB connection and bcp connection via bcputility call.)

Any tips/pointers would be great.

[tomroh]

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1
UsageCount=1
TrustServerCertificate=True

or

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1
UsageCount=1
TrustServerCertificate=true

or

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1
UsageCount=1
TrustServerCertificate=yes

Can you try those? I don't have an environment setup that I can use that is like yours to test. The easy option is to add another argument but there is already many arguments in the base function. Otherwise, I need a little time to get something setup and review the odbc connection vs bcp.

[tomroh]

https://github.com/tomroh/bcputility/pull/20

Install the latest version from github with:

devtools::install_github('tomroh/bcputility')

The arguments have significantly changed to handle more connection configurations. See ?makeConnectArgs.