DBI connecting to MS SQL Server TLS 1.2

PearlNovice has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, One of the file I have to process is anticipated to be a very large file when we go into production and so I've chosen perl instead of my usual VB script. However, I've run into problem with DBI throwing up the below error:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error (SQL-0
+8001) [state was 08001 now 01000]
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (SECCreate
+Credentials()). (SQL-01000) [state was 01000 now 01S00]
[download]

SSL has been turned off on the server where my script is run from. TLS1.0 and TLS1.1 are turned off also. I tested turning TLS1.0 back on my script will run without issue, but I'm not supposed to to use TLS1.0. So my question is what do I need to do to get my code work with TLS1.2? Below is how to currently attempt to get the DB connection using DBI:

sub get_connection {
    my ($DSN, $DBServer, $DBUser, $PW) = @_;
    my $dbh = DBI->connect("dbi:ODBC:Driver={SQL Server};Server=${DBSe
+rver};DSN=${DSN};UID=${DBUser};PWD=${PW};KeepAlive=1");
    return $dbh;
}
[download]

Thanks for your help!

Comment on DBI connecting to MS SQL Server TLS 1.2 Select or Download Code

Replies are listed 'Best First'.
Re: DBI connecting to MS SQL Server TLS 1.2 by Tux (Canon) on Sep 30, 2019 at 07:01 UTC
I recently worked from perl on Linux with a MS SQL server database, and got the best results with FreeTDS: `my $dbh = DBI->connect ("dbi:ODBC:mssql_freetds", $username, $password +, \%dbi_attributes);` [download] `$ cat ~/.odbc.ini [mssql_freetds] Description = My MS SQL database Driver = FreeTDS TDS version = 7.2 Trace = No Server = mysql.server.local Port = 1433 Database = DatabaseName User = UserName Password = PassWord Client Charset = UTF-8` [download] The biggest difference between FreeTDS and the MS ODBC driver is the return type of UUID field. The MS ODBC does not allow nested queries, whereas the FreeTDS driver does. So I used the ODBC driver to make a CSV dump of the database and the FreeTDS driver to actually work with the database. For ODBC I did `my $dbh = DBI->connect ("dbi:ODBC:mssql_odbc", $username, $password, \ +%dbi_attributes);` [download] `$ cat ~/.odbc.ini [mssql_odbc] Description = My MS SQL database Driver = ODBC Driver 17 for SQL Server Server = mysql.server.local Database = DatabaseName User = UserName Password = PassWord` [download] Also make sure you put the fully qualified hostname in the server name. `localhost` will not work. Enjoy, Have FUN! H.Merijn	[reply] [d/l] [select]
Re: DBI connecting to MS SQL Server TLS 1.2 by soonix (Chancellor) on Sep 29, 2019 at 17:49 UTC
I'm not currently at work, so can't test. My suggestions: I don't put the user name and password in the connection string, my connect statement looks like `my $dbh = DBI->connect("dbi:ODBC:DRIVER=SQL Server;SERVER=HOST\\INSTAN +CE;DATABASE=DBNAME", $DBUser, $PW) or die $DBI::errstr;` [download] (the actual connection string including the (single) backslash comes from a config file, but that shouldn't matter) Did you test the exact same ODBC connection with another tool, e.g. MS QUERY/Excel/Access? Do you use 32bit or 64bit Perl?	[reply] [d/l]
Re^2: DBI connecting to MS SQL Server TLS 1.2 by PearlNovice (Initiate) on Sep 29, 2019 at 23:12 UTC
Thank you for your suggestion. No, the connection string used with other tools (e.g. Microsoft ssms) are slightly different. With other tools connection string specify all the details. Here I'm using Windows ODBC to define the DSN and pass in the DSN name via the DSN= parameter. I did this because I came across another article somewhere, where someone else had run into problem with SSL / TLS and he ended up defining an ODBC DSN, then passing both the DSN as well as the server name. I use 64bit Perl. Having said that I've figured out what my problem is now. Even though I upgraded my ODBC driver and defined my new DSN based on the new ODBC driver (which supports TLS1.2) my driver specification was still wrong like this: `"dbi:ODBC:Driver={SQL Server}"` [download] I mistakenly assumed that the `"dbi:ODBC:Driver={SQL Server}"` is generic and defined somewhere within DBI. However having searched the DBI::ODBC package and not able to see where the driver is specified I thought maybe I need to specify the exact driver name as appeared under the list of drivers in the Windows ODBC applet, and that fixed it. So my updated code is like this (and it works now): `"dbi:ODBC:Driver={ODBC Driver 17 for SQL Server}"` [download]	[reply] [d/l] [select]