BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rgreen33
Pyrite | Level 9

"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

1 ACCEPTED SOLUTION

Accepted Solutions
alexal
SAS Employee

@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

View solution in original post

25 REPLIES 25
alexal
SAS Employee

@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
Pyrite | Level 9

@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
Barite | Level 11
Can the SQL DBAs maybe check in the SQL logs when the error occurs ?
nhvdwalt
Barite | Level 11
Is there maybe an opportunity to temporarily disable SSL for testing ? Just trying to eliminate components.
alexal
SAS Employee

@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
Pyrite | Level 9

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

@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
Barite | Level 11
Are these not maybe long running jobs and then then the firewall closes the connection ?
rgreen33
Pyrite | Level 9

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

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
Obsidian | Level 7

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

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
Obsidian | Level 7

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
SAS Employee

@chawkins,

 

How can I see the log?

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 25 replies
  • 36895 views
  • 7 likes
  • 9 in conversation