CLI error connecting to SQL Server

[chawkins]

@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

[alexal · Accepted solution]

@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

[chawkins]

@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

[alexal]

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

[jltz83]

@alexal,

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

[alexal]

@jltz83,

You are welcome!

[rgreen33]

I have been testing these changes in my INI file and it does appear that this has fixed my issue too.

Thanks for your help,

Ricky

[alexal]

@rgreen33,

You are welcome!

[HemantNarkhede]

@alexal we are also getting the below error while running the program in SAS.

when we will run the program 1st time it will throw the below error and if we will run the same program without doing any changes, job is running successfully.. Can you please help me on this.

Log :

 51 libname coll_etl sqlsvr dsn="SHAWPRPT_COLLECT_ETL" schema="dbo" user="NAO\fzx688" password="&WindowsPass" readbuff=5000;
NOTE: Libref COLL_ETL was successfully assigned as follows:
Engine: SQLSVR
Physical Name: SHAWPRPT_COLLECT_ETL
51 !
51 ! run;
52 libname coll_rpt sqlsvr dsn="SHAWPRPT_COLLECT_REPORTS" schema="dbo" user="NAO\fzx688" password="&WindowsPass"
52 ! readbuff=5000; run;
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.
53 libname coll sqlsvr dsn="SHAWPRPT_COLLECT" schema="dbo" user="NAO\fzx688" password="&WindowsPass" readbuff=5000;
NOTE: Libref COLL was successfully assigned as follows:
Engine: SQLSVR
Physical Name: SHAWPRPT_COLLECT

[irak]

Hi, I am new to SAS DI and I got almost same issue.

I got error [SAS][ODBC SQL Server Wire Protocol driver]Socket closed; while  extracting data out of SQLServer and writing result set onto  sas7bdat SAS file on Linux machine.  It is not a long process. It took about 29 seconds and socket is closed.  

I would like to test if adding SeedBeforeConnect option to ODBC work. however, i need to know:

1. Can I just add the option into current ODBC.ini without giving any impact to current process? There is no  'SeedBeforeConnect' option on current ODBC.ini. I just checking if i added this, would it choke up existing connection? 
2. What would 'SeedBeforeConnect' do?

Environment Info:

• Version=4.901
• JRE version=1.7.0_76
• SAS Metadata Server version=9430

Thank you

[ShelleySessoms]

Hi @irak,

Since this topic has an accepted solution and is closed, it is best to start a new message with your question. You will get more views and have a better opportunity for folks to reply this way.

Thanks for using the SAS Support Communities!

Best,

Shelley