10-24-2017 11:11 AM
"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.
11-17-2017 01:00 PM
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:
Also, based on our previous experience, you can try to add these settings:
ConnectionRetryCount=2 ConnectionRetryDelay=2 QueryTimeout=-1
10-24-2017 11:14 AM
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>
10-24-2017 11:53 AM
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?
10-24-2017 12:33 PM
10-24-2017 02:58 PM
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?
10-25-2017 06:59 AM
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.
11-03-2017 08:35 AM - edited 11-03-2017 08:37 AM
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?
11-15-2017 11:23 AM
@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.
11-15-2017 01:37 PM
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.
11-15-2017 01:55 PM
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.
Need further help from the community? Please ask a new question.