topic Re: CLI error connecting to SQL Server in Administration and Deployment

CLI error connecting to SQL Server

rgreen33 — Tue, 24 Oct 2017 15:11:15 GMT

"Occasionally", we are getting the following error on our jobs that are connecting to SQL server.

154 LIBNAME sqlpr SQLSVR Datasrc=sqlprod SCHEMA=dbo AUTHDOMAIN="SQL Server - SQLPROD" ;

ERROR: CLI error trying to establish connection: [SAS][ODBC SQL Server Wire Protocol driver]Socket closed. : [SAS][ODBC SQL Server

Wire Protocol driver]SSL Handshake Failure reason [Unknown SSL Error].

ERROR: Error in the LIBNAME statement.

Probably 98% of the time, these jobs run without error. I have about 10 jobs that connect to SQL Server. And, I have 2 different SQL Servers that I am connecting to. Jobs pointing to both servers are failing with the above error. Some nights, all run fine without error. I have adjusted the times, and that does not seem to make a difference. Oh, and I also have 2 SAS environments PROD and DEV, with my jobs running on each...both showing the same symptoms.

We have looked in some log files, but we are having no luck at even locating this issue. Has anyone else had this problem? Any ideas on where to look? We are in a Linux environment. We have looked at SSSD logs and SSL logs, trying to match things up with the run times of the jobs. We are just not finding anything...and the failures seem to be completely random.

Any help and/or advice is greatly appreciated.

Thanks,

Ricky

Re: CLI error connecting to SQL Server

alexal — Tue, 24 Oct 2017 15:14:10 GMT

@rgreen33,

These loggers should help determine the problem. Please add them to your logconfigloc.

<logger name="App.tk.eam.ssl">
   <level value="TRACE" />
</logger>
<logger name="App.tk.eam.ssl.Buffer">
   <level value="TRACE" />
</logger>

Re: CLI error connecting to SQL Server

nhvdwalt — Tue, 24 Oct 2017 15:33:57 GMT

Are these not maybe long running jobs and then then the firewall closes the connection ?

Re: CLI error connecting to SQL Server

rgreen33 — Tue, 24 Oct 2017 15:53:23 GMT

@alexal

Thank you for your reply. I am fairly (really) new to SAS. I have not yet had the opportunity to use loggers...so, this is new to me. Can you give me some details on where this goes and where the info will be logged to? Also, once this is added to the correct location, does anything need to be restarted or will it automatically pick these setting up?

Thanks,

Ricky

Re: CLI error connecting to SQL Server

rgreen33 — Tue, 24 Oct 2017 15:54:54 GMT

@nhvdwalt

Thank you for the reply. No, they are very short running jobs...some loading less than 1,000 rows of data.

Thanks,

Ricky

Re: CLI error connecting to SQL Server

nhvdwalt — Tue, 24 Oct 2017 16:05:57 GMT

Can the SQL DBAs maybe check in the SQL logs when the error occurs ?

Re: CLI error connecting to SQL Server

nhvdwalt — Tue, 24 Oct 2017 16:26:57 GMT

Is there maybe an opportunity to temporarily disable SSL for testing ? Just trying to eliminate components.

Re: CLI error connecting to SQL Server

alexal — Tue, 24 Oct 2017 16:33:37 GMT

@rgreen33,

If you do not know how to set LOGCONFIGLOC, then just set them in your SAS program:

%log4sas();
%log4sas_logger("App.tk.eam.ssl","level=trace");
%log4sas_logger("App.tk.eam.ssl.Buffer","level=trace");

Re: CLI error connecting to SQL Server

rgreen33 — Tue, 24 Oct 2017 18:58:17 GMT

@alexal

Thanks again for your reply. I added this code to my job, but it failed...messages stated that the levels could not be set. So, going back to your original suggestion...

Looking at my server, I see that I have the LOGCONFIGLOC set in the following folders: WorkspaceServer; StoredProcessServer; PooledWorkspaceServer; ConnectServer; and BatchServer. I have enabled ACM and APM on my server, thus the sasv9_usermods.cfg file is pointing me to the logconfig.apm.xml file for the logconfigloc (within each folder). Since these are scheduled jobs that are failing, I am assuming that I will need to add these loggers to the logconfig.apm.xml file within the BatchServer folder. Is this correct? If so, then I am also assuming that I will need to restart the BatchServer to get this change to take affect. Is this correct? If so, the only other question that I would have is where would this logging information show up? Would it show up in the /BatchServer/PerfLogs folder, as this is indicated in the logconfig.apm.xml file?

Thanks,

Ricky

Re: CLI error connecting to SQL Server

alexal — Wed, 25 Oct 2017 10:59:15 GMT

@rgreen33,

Since these are scheduled jobs that are failing, I am assuming that I will need to add these loggers to the logconfig.apm.xml file within the BatchServer folder. Is this correct?
If so, then I am also assuming that I will need to restart the BatchServer to get this change to take affect. Is this correct?
Would it show up in the /BatchServer/PerfLogs folder, as this is indicated in the logconfig.apm.xml file?

Those are correct assumptions.

Re: CLI error connecting to SQL Server

jltz83 — Fri, 03 Nov 2017 12:37:07 GMT

I am also having this problem. It just seems to randomly occur, although it happens more when I submit multiple proc and/or data steps or try to run an entire project. When the error occurs, the only way I have found to move on without it reoccurring in the same place in the log is to submit a quit statement a couple of times and then step through the code one procedure at a time.

I look forward to solving this.

I did find this note: http://support.sas.com/kb/58/054.html . Could it be related?

Re: CLI error connecting to SQL Server

chawkins — Wed, 15 Nov 2017 16:23:03 GMT

@rgreen33, you can count my shop as another one experiencing this same issue. It's spectacularly frustrating. Any luck on your end?

Just to make sure it's not related to the query details, I ran a libname statement to SQL Server in a loop and I get an SSL handshake error about 2 or 3 times out of 100.

@alexal, I added the loggers you mentioned above to the logconfig.trace.xml file for the workspace server and made that the default. Not quite sure what I'm looking for though as it appears the same to me other than the CLI SSL handshake error that's returned.

Chris

Re: CLI error connecting to SQL Server

SASKiwi — Wed, 15 Nov 2017 18:37:03 GMT

What SAS product are you using to connect to SQL Server? Is it SAS/ACCESS to ODBC or some other product? I'm curious because we use SAS/ACCESS to ODBC with the Microsoft ODBC drivers SAS SQL Server or SQL Server Native Client 11.0 and have pretty much 100% reliability. If there are connection problems they are not intermittent type described.

Re: CLI error connecting to SQL Server

chawkins — Wed, 15 Nov 2017 18:55:19 GMT

It's SAS/ACCESS to Microsoft SQL Server on Linux through the packaged Data Direct Driver (7.1.5). It wouldn't surprise me if it has something to do with the SSL library included, but we've yet to walk it down. I'm meeting with one of our server admins this afternoon, so hopefully we can narrow down the possibilities.

Chris

Re: CLI error connecting to SQL Server

alexal — Fri, 17 Nov 2017 13:04:49 GMT

@chawkins,

How can I see the log?

Re: CLI error connecting to SQL Server

chawkins — Fri, 17 Nov 2017 17:35:19 GMT

@alexal I've attached the workspace server log, but let me tell you what we found first, as it might be more useful.

I'm swimming in waters that are a bit above my head, but here are two things we discovered:

1) Despite this being our odbc stanza, with cryptoprotocolversion set to start at TLS 1.2:

Description=Test Server for SSL issue
Driver=/usr/local/sas/SASHome/AccessClients/9.4/SQLServer/lib/S0sqls27.so
Server=rocky1.tnc.org,1633
Database=test
Schema=dbo
Trusted_Connection=Yes
AuthenticationMethod=10
EnableBulkLoad=1
BulkLoadBatchSize=3000
EnableQuotedIdentifiers=1
EncryptionMethod=1
ValidateServerCertificate=0
CryptoProtocolVersion=TLSv1.2,TLSv1.1,TLSv1

The driver is reaching out using TLS 1.0. I don't know if that's because 7.1.5 doesn't support 1.1 or 1.2 or because there's a configuration problem somewhere.

2) There are cyphers missing on the driver side that exist on the SQL Server side, specifically the elliptical curve cyphers. When we removed these cyphers from the windows side the problem disappeared I ran a loop that ran the libname statement 10,000 times and we got no SSL handshake errors.

Our server admins don't want to continue to have those cyphers disabled, because those are the stronger cyphers. And what they'd really like is to disable TLS 1.0 all together and have 1.1 be the lowest standard, but obviously that would be a problem if the packaged driver we have can't handle 1.1. I tried isolating the cryptoprotocol version to 1.2 and 1.1 and we get errors across the board.

Any advice?

Chris

Re: CLI error connecting to SQL Server

alexal — Fri, 17 Nov 2017 18:00:42 GMT

@chawkins,

Thanks for the log but I do not see any output from App.tk.eam.ssl. Anyway, when the SSL handshake is performed, a lack of entropy might exist, causing the handshake to be delayed. That delay, in turn, causes that error. Please try setting this in the odbc.ini:

SeedBeforeConnect=1

Also, based on our previous experience, you can try to add these settings:

ConnectionRetryCount=2
ConnectionRetryDelay=2
QueryTimeout=-1

Re: CLI error connecting to SQL Server

chawkins — Fri, 17 Nov 2017 21:01:05 GMT

@alexal - it's very possible that I turned on the loggers but didn't actually tell it to send the output anywhere because I update those .xml files once every never and I know next to nothing about the syntax.

The SeedBeforeConnect option isn't a factor with our issue. Nor is the QueryTimeout because you never get far enough to submit a query. But that ConnectionRetryCount =2 does solve the immediate problem, so thank you very much. I've run my libname loop 100,000 times without a SSL Handshake error.

Am I correct that the 7.1.5 driver for SQL Server doesn't support higher than TLS 1.0? And if so, do we know yet when a Data Direct driver that does will be packaged as the standard with SAS?

Chris

Re: CLI error connecting to SQL Server

alexal — Sat, 18 Nov 2017 02:58:24 GMT

@chawkins,

You are welcome! I'm glad that the problem has been resolved.

Am I correct that the 7.1.5 driver for SQL Server doesn't support higher than TLS 1.0?

ODBC driver supports that out from the box, but not SQL. I suggest opening a track with our SAS/ACCESS team to see if there is a version of SQL driver that supports newer versions of TLS.

Re: CLI error connecting to SQL Server

jltz83 — Mon, 27 Nov 2017 13:57:18 GMT

@alexal,

Thank you! Changing these settings in our odbc.ini file seems to have solved this for us, too.