SAS 9.4 Admin - SAS OLE DB Provider and TLS 1.2

This particular case occurred when target SQL DB were updated to require TLS 1.2 and higher protocol during a security led upgrade. The following conditions were true:

1. SAS server accessing the SQL DBs was A win X64-2016 server
2. Before change TLS 1.0 and 1.1 were allowed
3. Users predominantly used SAS ACCESS/OLEDB and relevant PROVIDERS

Issue:

Target SQL DB started requiring TLS 1.2 and higher resulting in end user queries failure.

ERROR: Error trying to establish connection: Unable to Initialize: /[DBNETLIB/]/[ConnectionOpen (SECDoClientHanshake())./] SSL Security error.

Troubleshooting:

1. Establish the source of the issue, was it system or SAS related error. MS default providers were used in code the first step would be to test the value separate from SAS. A UDL test is performed to test a PROVIDER independently.

Perform UDL Test

1. On the SAS server, login as a user (admin. privilege is not needed however, better to not run into access/privilege issues).
2. Right click anywhere on desktop and create a new txt document and name it 'test.udl' and enter
3. Double click the test.udl file just created
   
4. Double click test.udl file and the following screen will appear
   

   Click Next

   

   Click on Select DB

   

What does it mean?

This test failure shows that the issue is with the PROVIDER which could not handle the higher TLS requirement at target node.

Resolution:

Due to production environment and critical timing a quick turn around or a fix was needed. We decided to test the MS OLEDB Driver for SQL Server (note the change from 'Provider' to 'Driver'):

Repeating same test step resulted in a 'Successful Test'

This showed that an alternative value could be used. Note that users were using PROVIDER=SQLOLEDB value so what would be the new PROVIDER value in a SAS code?

How to Find PROVIDER value?

1. On SAS server bring up SAS BASE Editor and run the following string which will start

   libname test oledb;
   
   %put %superq(sysdbmsg);

2. "Data Link Properties" shows up
3. This step is exactly the same as you do in UDL test. Once Test Connection is successful, click OK.
4. In SAS Log a successful connection will produce a line like below:

   OLEDB: 'Provider=MSOLEDBSQL.1;Password=xxxxxxxxxxxx;Persist Security Info=True;User ID=sasxxxxxx;Initial Catalog=xxxxxx;DataSource=SQLDBPxxx;Initial File Name="";Server SPN="";Authentication="";Access Token=""';

   Note the PROVIDE value:

5. Copy the line starting from Provider ... ending in Access Token=""'; and place it in your libname or CONNECT TO string, example shown below:
   

Note:

Please note that the default MS Provider for SQL Server started supporting higher TLS with Win 2019 Server. In that case if there is a failure, the same UDL based test could be used to find alternative drivers to use with OLEDB, and generate value for PROVIDER=. In some testing, with TLS 1.2 or higher on WIN 2019 server, PROVIDER = SQLOLEDB.1 was shown to be successful.