Architecting, installing and maintaining your SAS environment

CLI error connecting to SQL Server

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 80
Accepted Solution

CLI error connecting to SQL Server

"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


Accepted Solutions
Solution
2 weeks ago
SAS Employee
Posts: 318

Re: CLI error connecting to SQL Server

@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


All Replies
SAS Employee
Posts: 318

Re: CLI error connecting to SQL Server

@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>
Frequent Contributor
Posts: 80

Re: CLI error connecting to SQL Server

@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

Regular Contributor
Posts: 211

Re: CLI error connecting to SQL Server

Can the SQL DBAs maybe check in the SQL logs when the error occurs ?
Regular Contributor
Posts: 211

Re: CLI error connecting to SQL Server

Is there maybe an opportunity to temporarily disable SSL for testing ? Just trying to eliminate components.
SAS Employee
Posts: 318

Re: CLI error connecting to SQL Server

@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");
Frequent Contributor
Posts: 80

Re: CLI error connecting to SQL Server

@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

SAS Employee
Posts: 318

Re: CLI error connecting to SQL Server

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

Regular Contributor
Posts: 211

Re: CLI error connecting to SQL Server

Are these not maybe long running jobs and then then the firewall closes the connection ?
Frequent Contributor
Posts: 80

Re: CLI error connecting to SQL Server

@nhvdwalt

 

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

 

Thanks,

Ricky

Occasional Contributor
Posts: 17

Re: CLI error connecting to SQL Server

[ Edited ]

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?

Occasional Contributor
Posts: 12

Re: CLI error connecting to SQL Server

@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

Super User
Posts: 3,306

Re: CLI error connecting to SQL Server

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.

Occasional Contributor
Posts: 12

Re: CLI error connecting to SQL Server

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

SAS Employee
Posts: 318

Re: CLI error connecting to SQL Server

@chawkins,

 

How can I see the log?

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 22 replies
  • 419 views
  • 6 likes
  • 6 in conversation