CLI error connecting to SQL Server

[rgreen33]

"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

[alexal]

@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>

[rgreen33]

@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

[nhvdwalt]

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

[nhvdwalt]

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

[alexal]

@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");

[rgreen33]

@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

[alexal]

@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.

[nhvdwalt]

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

[rgreen33]

@nhvdwalt

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

Thanks,

Ricky

[jltz83]

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?

[chawkins]

@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

[SASKiwi]

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.

[chawkins]

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

[alexal]

@chawkins,

How can I see the log?