Use ogr2ogr/ogrinfo to connect to MSSQL on Ubuntu Server

Question

OS: Ubuntu Server 20.04; Software: GDAL/QGIS, ogr2ogr/ogrinfo

My goal is to use ogr to pull data from MSSQL as part of a longer process that I'm working on.

First step is to test out the connection using ogrinfo before even attempting the data pull. But using $ ogrinfo "MSSQL:server=ccgissql01z;database=[];uid=[];pwd=[]" yields an error message:

ERROR 1: Unable to initialize connection to the server for MSSQL:server=[];database=[];uid=[];pwd=[],

Try specifying the driver in the connection string from the list of available drivers:
ODBC Driver 18 for SQL Server

Specifying a driver using ;driver=ODBC Driver 18 for SQL Server; doesn't work.

Using isql yields a successful connection

Any ideas as to what's going wrong?

Answer 1

By playing around with isql and ogrinfo I was able to figure out that what I was missing was trustservercertificate=yes.

So the final command looks like this:

ogrinfo -so \
"MSSQL:driver=ODBC Driver 18 For SQL Server;\
server=[server];\
trustservercertificate=yes;\
uid=[user];\
pwd=[pass];\
database=[db]"