Closed shane-borden closed 6 months ago
-u doesn't seem to be an option in the version of bcp I checked, however this is on Windows and not Linux.
usage: bcp {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]
with the error: certificate verify failed:self signed certificate
it could be more likely to be the -T option to trust the server certificate, which we currently only have set for using Windows authentication.
proc bcpComm { tableName filePath uid pwd server} {
upvar 3 authentication authentication
if {[ string toupper $authentication ] eq "WINDOWS" } {
exec bcp $tableName IN $filePath -b 500000 -a 16000 -T -S $server -c -t ","
} else {
exec bcp $tableName IN $filePath -b 500000 -a 16000 -U $uid -P $pwd -S $server -c -t ","
}
}
Please advise if -T works instead or provide more details on the -u option if this is only available on the Linux version.
I am using version 18 of the odbc tools on linux.
/opt/mssql-tools18/bin/bcp -v
BCP - Bulk Copy Program for Microsoft SQL Server.
Copyright (C) Microsoft Corporation. All Rights Reserved.
Version: 18.2.0001.1
/opt/mssql-tools18/bin/bcp -version
usage: /opt/mssql-tools18/bin/bcp {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] [-q quoted identifier]
[-t field terminator] [-r row terminator]
[-a packetsize] [-K application intent]
[-S server name or DSN if -D provided] [-D treat -S as DSN]
[-U username] [-P password or tokenfile]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values][-G Azure Active Directory Authentication]
[-h "load hints"] [-d database name]
[-Y encrypt option] [-u trust server certificate]
I will check to see if -T
works.
-T
did not work on its own:
Vuser 9:Loading Stock - 100000
Error in Virtual User 9: SQLState = 08001, NativeError = -1
Error = [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate]
SQLState = 08001, NativeError = -1
Error = [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722
child process exited abnormally
Vuser 9:FINISHED FAILED
OK, if you use ODBC Driver 17 does this work?
If so, then it is likely to be the move to encrypted connections by default as described here:
https://learn.microsoft.com/en-us/sql/connect/odbc/connection-troubleshooting?view=sql-server-ver16
We were looking if the -T option worked as this is the method to not check a self-signed certificate, however it looks like there are more steps required described here to configure encrypted connections on Linux:
https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-encrypted-connections?view=sql-server-ver16&tabs=client
In particular this recommends for ODBC "Encrypt=Yes; TrustServerCertificate=no;" which would suggest you cannot trust a self-signed certificate in this environment.
Have you followed all of the steps in the link above and are still getting the "verify failed:self signed certificate" error?
ODBC Driver 17 does not work with that option. Only option that works with SQL 2022 is the ODBC Driver 18
+ the -u
option.
I have tested SQL Server 2019 with ODBC Driver 17 for SQL Server and this completes the bcp based build without errors.
Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64)
Jan 12 2022 22:30:08
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Linux (Oracle Linux Server 8.5) <X64>
I will plan to run further tests firstly with ODBC Driver 18 against SQL Server 2019 and then ODBC Driver 18 against SQL Server 2022 to isolate the issue.
OK, so I can replicate the error with ODBC driver 18 against SQL Server 2019
Error = [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate]
SQLState = 08001, NativeError = -1
Error = [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722
Also yes, I now see as well that with this new version we now have -u for trust server certificate, so it looks like we need to add this option for ODBC Driver 18 and above.
./bcp
[-h "load hints"] [-d database name]
[-Y encrypt option] [-u trust server certificate]
From what I can see after downloading SQL Server 2022, bcp for SQL Server for Windows is dependent on ODBC Driver 17 and therefore there is no -u option for bcp Windows. So it looks like we need to add "-u" for Linux only if the driver is ODBC 18 or higher.
I think you are correct.
Pull request https://github.com/TPC-Council/HammerDB/pull/653 added. As noted in the PR the long-term solution is to extend the ODBC interface that HammerDB uses to include bcp based commands https://github.com/TPC-Council/HammerDB/issues/594 as using the executable is the cause of issues such as this where the login connection and the bcp connection are out of synch.
When building the schema against SQL Server 2022 the following error is encountered if
mssqls_use_bcp
is set to true:To Reproduce Steps to reproduce the behavior: Execute the buildschema process using the following settings against a SQL Server 2022 instance:
Expected behavior buildschema should finish with no error
HammerDB Version (please complete the following information):
HammerDB Interface (please complete the following information):
Operating System (please complete the following information):
Database Server (please complete the following information):
Database Client (please complete the following information):
Additional context This error can be overcome by editing the proc "proc bcpComm" in
mssqlsoltp.tcl
to contain the-u
option: Current code:exec bcp $tableName IN $filePath -b 500000 -a 16000 -U $uid -P $pwd -S $server -c -t ","
Updated code:exec bcp $tableName IN $filePath -b 500000 -a 16000 -U $uid -P $pwd -S $server -u -c -t ","
There may be additional areas this needs to be updated. If the server does not require the option, having it specified anyway does not produce any errors.