OGR support for SQL Server with TLS1.2 encryption

Expected behavior and actual behavior.

Expected behavior: All connections to a SQL Server instance are opened without an SSL error.

The OGR API defaults to the "SQL Server" ODBC driver from Microsoft (version 10.00.16299.15, file SQLSVR32.DLL, older versions are probably still in use). This driver is no longer maintained by Microsoft and only supports TLS1.0

When connecting to a remote SQL Server host that enforces TLS1.2 on connections, the OGR utilities (and applications that use the OGR API, including MapServer) report an error that includes "[Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error".

Steps to reproduce the problem.

A remote SQL Server (2016 preferred) instance must be provisioned so that all communication is encrypted with TLS1.2. In our environment it was enabled by turning on “FIPS Compliance”, following "Instructions for using SQL Server 2014 in the FIPS 140-2-compliant mode", https://support.microsoft.com/en-us/help/3141890/instructions-for-using-sql-server-2014-in-the-fips-140-2-compliant-mod. It may be possible to turn on TLS 1.2 without full compliance, see for example "Enable TLS 1.2 for SQL Server 2012 without enabling FIPS policy", https://social.technet.microsoft.com/Forums/en-US/122b092a-4e3c-4b28-aa7d-4a0266760ae0/enable-tls-12-for-sql-server-2012-without-enabling-fips-policy.

This SQL Server instance must contain a database with a table with spatial data in a "geometry column". In addition the database should have the appropriate geometry_column" and "spatial_sys_ref" tables. The example shown here should be modified to match your test environment.

These examples assume use of OGR utilities from GDAL 2.3.2. A similar problem exists with GDAL 1.11.4.

Define a connection string that defaults to the "SQL Server" driver and references a TLS1.2-enabled SQL Server instance. For example, in the context of a Windows command shell, set c="MSSQL:server=1.2.3.4;database=testDatabase;uid=...;pwd=..." ogrinfo -so %c%

This returns an error that includes the following message: ERROR 1: Unable to initialize connection to the server for MSSQL:server=... [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error

Explicitly specifying "driver=SQL Server" also yields the same error.

Using the newer Microsoft ODBC driver fixes this issue. set c="MSSQL:driver=ODBC Driver 17 for SQL Server;server=1.2.3.4;database=testDatabase;uid=...;pwd=..." ogrinfo -so %c% returns table and geometry column information.

Assuming that you have a valid query, set c="MSSQL:server=1.2.3.4;database=testDatabase;uid=...;pwd=..." set q="select ..." ogr2ogr -sql %q% -f "GeoJSON" -overwrite -a_srs EPSG:4326 test.xml %c% results in an error starting with: ERROR 1: Unable to initialize connection to the server for MSSQL:server=... [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error

Using the newer driver, set q="SELECT ..." set c="MSSQL:server=1.2.3.4;database=testDatabase;uid=...;pwd=..." ogr2ogr -sql %q% -f "GeoJSON" -overwrite -a_srs EPSG:4326 test.xml %c% results in creation of the GeoJSON file. (The content is incorrect with this new driver, that is the subject of https://github.com/OSGeo/gdal/issues/1136).

Operating system

Windows 10 and Windows Server 2016.

The new Microsoft ODBC driver may be obtained from Microsoft at "Microsoft® ODBC Driver 11 for SQL Server® - Windows", https://www.microsoft.com/en-us/download/details.aspx?id=36434. Select "ENU\x64\msodbcsql.msi" or otherwise as appropriate for your hardware.

GDAL version and provenance

The problem was first found in GDAL 1.11.4 distributed with the GISInternals MapServer 6.4.3 "stable" x64 binaries distribution.

The problem is also present with the GISInternals MapServer 7.2.1 "stable" x64 binaries distribution as well as with the gdal203.dll recreated from the GISInternals MapServer 7.2.1 "stable" x64 source distribution.

OSGeo / gdal