[MSSQLSpatial] BCP not enabled when using Native Client driver

[WY-CGhilardi]

What is the bug?

I am attempting to upload a large repackage to a SQL Server table. Due to the size, I would like to use the BCP parameter mentioned in the driver docs.

I am using the GDAL executable distributed with my QGIS install, accessed via OSGeo4W shell and manually specifying the driver.

ogr2ogr --config MSSQLSPATIAL_USE_BCP TRUE --config MSSQLSPATIAL_BCP_SIZE 5000 -f "MSSQLSpatial" MSSQL:"driver=SQL Server Native Client 11.0;server=myservername;database=mydbname;trusted_connection=yes" -lco SCHEMA=dbo -nln mytablename "mygeopackagename.gpkg"  -a_srs "EPSG:6339" -overwrite -lco UPLOAD_GEOM_FORMAT=wkt -lco SPATIAL_INDEX=NO --debug ON

which results in:

ogr2ogr --config MSSQLSPATIAL_USE_BCP TRUE --config MSSQLSPATIAL_BCP_SIZE 5000 -f "MSSQLSpatial" MSSQL:"driver=SQL Server Native Client 11.0;server=myservername;database=mydatabasename;trusted_connection=yes" -lco SCHEMA=dbo -nln mylayername "mygeopackagename.gpkg"  -a_srs "EPSG:6339" -overwrite -lco UPLOAD_GEOM_FORMAT=wkt -lco SPATIAL_INDEX=NO --debug ON
GDAL: Auto register E:\QGIS\apps\gdal\lib\gdalplugins\gdal_ECW_JP2ECW.dll using GDALRegister_ECW_JP2ECW.
GDAL: Auto register E:\QGIS\apps\gdal\lib\gdalplugins\gdal_GEOR.dll using GDALRegister_GEOR.
GDAL: Auto register E:\QGIS\apps\gdal\lib\gdalplugins\gdal_HDF5.dll using GDALRegister_HDF5.
GDAL: Auto register E:\QGIS\apps\gdal\lib\gdalplugins\gdal_MrSID.dll using GDALRegister_MrSID.
GDAL: Auto register E:\QGIS\apps\gdal\lib\gdalplugins\ogr_MSSQLSpatial.dll using RegisterOGRMSSQLSpatial.
GDAL: Auto register E:\QGIS\apps\gdal\lib\gdalplugins\ogr_OCI.dll using RegisterOGROCI.
GDAL: Auto register E:\QGIS\apps\gdal\lib\gdalplugins\ogr_SOSI.dll using RegisterOGRSOSI.
GPKG: GeoPackage v1.2.0
GDAL: GDALOpen(mygeopackagename.gpkg, this=0000021FFAA5F820) succeeds as GPKG.
MSSQLSpatial: Use COPY/BCP: 1
ODBC: SQLDriverConnect(driver=SQL Server Native Client 11.0;server=myservername;database=mydatabasename;trusted_connection=yes)
GDAL: GDALOpen(MSSQL:driver=SQL Server Native Client 11.0;server=myservername;database=mydatabasename;trusted_connection=yes, this=0000021FFAA6DD00) succeeds as MSSQLSpatial.
MSSQLSpatial: DeleteLayer(mylayername)
GDALVectorTranslate: Using FID=fid and -preserve_fid
OGR_MSSQLSpatial: Using column fid as FID for table mylayername.
OGR2OGR: Using WriteArrowBatch()
OGR_MSSQLSpatial: Using column fid as FID for table mylayername.
ERROR 1: SQL Error SQLState=HY000, NativeError=0, Msg=[Microsoft][SQL Server Native Client 11.0]Connection is not enabled for BCP

Looking at supported formats, it seems to show BCP support listed

ogrinfo --formats

Supported Formats:
  ...truncated...
  PGeo -vector- (ro): ESRI Personal GeoDatabase
  MSSQLSpatial -vector- (rw+): Microsoft SQL Server Spatial Database (BCP)
  OGR_OGDI -vector- (ro): OGDI Vectors (VPF, VMAP, DCW)
  ...

In addition, I have also tried the other following drivers as part of the connection string. These all produce the same error as above.:

SQL Server
SQL Server Native Client 11.0
ODBC Driver for SQL Server 13
ODBC Driver for SQL Server 17
ODBC Driver for SQL Server 18

Steps to reproduce the issue

1. Download GDAL 3.8.5.
2. Attempt to upload to a mssql database with the MSSQLSPATIAL_USE_BCP_TRUE flag set

ogr2ogr --config MSSQLSPATIAL_USE_BCP TRUE --config MSSQLSPATIAL_BCP_SIZE 5000 -f "MSSQLSpatial" MSSQL:"driver=SQL Server Native Client 11.0;server=myservername;database=mydbname;trusted_connection=yes" "mygeopackagename.gpkg"   -overwrite --debug ON

Versions and provenance

OS: Windows

$ ogr2ogr --version
GDAL 3.8.5, released 2024/04/02


$ ogr2ogr --build
PAM_ENABLED=YES
OGR_ENABLED=YES
CURL_ENABLED=YES
CURL_VERSION=8.6.0
GEOS_ENABLED=YES
GEOS_VERSION=3.12.1-CAPI-1.18.1
PROJ_BUILD_VERSION=9.4.0
PROJ_RUNTIME_VERSION=9.4.0
COMPILER=MSVC 193833135

SQL Server Version: 13.0.7024.30

Full QGIS version info

QGIS version
3.36.1-Maidenhead
QGIS code revision
3e589453
Qt version
5.15.3
Python version
3.9.18
GDAL/OGR version
3.8.4
PROJ version
9.3.1
EPSG Registry database version
v10.098 (2023-11-24)
GEOS version
3.12.1-CAPI-1.18.1
SQLite version
3.41.1
PDAL version
2.6.0
PostgreSQL client version
16.2
SpatiaLite version
5.1.0
QWT version
6.1.6
QScintilla2 version
2.13.4
OS version
Windows 10 Version 2009

Additional context

I have also tried using other versions of GDAL executables but none of them allow me to use a BCP enabled upload either

1. the GDAL executable downloaded from the OSGeo4W network installer,
2. a conda environment (conda create --name gdal-env gdal -c conda-forge)
3. gdal-3.8.5-1928-x64-core.msi from manually installed from gisinternals.com

~Most of the previous online threads on this issue recommend re-downloading an executable from gisinternals.com but that website seems to be down so any links from older threads are dead.~

EDIT: my work VPN was the issue and the site worked on a personal machine/after turning VPN off

I have also confirmed

1. TCP/IP is enabled on the server
2. The account I am trying to use has ADMINISTER BULK OPERATIONS permission on the server
3. the bcp utility seems to be present and recognized

bcp --version
usage: bcp.exe {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]        [-K application intent]  [-l login timeout]

[rouault]

@szekerest any idea?