LIBNAME Statement for the OLE DB Engine

[jonburk]

Hello,

I'm unable to create a LIBNAME statement equivalent to a PROC SQL "CONNECT TO" statement that works perfectly.  (I need a LIBNAME in order to use PROC APPEND.)  Both statements use the same OLE DB driver, database and user.  We are still at SAS version 9.4 M6, if that makes a difference.

Here's my PROC SQL "CONNECT TO" statement:

CONNECT TO oledb (init_string="Provider=MSOLEDBSQL19; Server=MyServer; Database=MyDatabase; UID=MyUsername; PWD=MyPassword; Use Encryption for Data=Optional; Trust Server Certificate=True;");

Here's my best guess at the equivalent LIBNAME statement:

LIBNAME MyLibrary OLEDB PROMPT=NO DATASOURCE=MyServer PROVIDER=MSOLEDBSQL19 SCHEMA=dbo USER=MyUsername PASSWORD=MyPassword PROPERTIES=('Use Encryption for Data'=Optional 'Trust Server Certificate'=True);

Unfortunately, it results in an "Invalid option name 'Use Encryption for Data'" error.

NO MATTER WHAT I TRY, I SIMPLY CANNOT GET IT TO RECOGNIZE THE "Use Encryption for Data" and "Trust Server Certificate" OLE DB OPTIONS.

But if I don't specify those options, I get the following error due to a lack of a certificate:

Error trying to establish connection: Unable to Initialize: Client unable to establish connection. For solutions related to
encryption errors, see https://go.microsoft.com/fwlink/?linkid=2227882.: SSL Provider: The certificate chain was issued by
an authority that is not trusted.

Prior to the upgrade of our database from SQL Server 2016 to SQL Server 2022 (and the upgrade from OLE DB driver SQLNCLI11 to MSOLEDBSQL19), the following LIBNAME statement worked:

LIBNAME MyLibrary OLEDB PROMPT=NO DATASOURCE=MyServer PROVIDER=SQLNCLI11 SCHEMA=dbo USER=MyUsername PASSWORD=MyPassword;

Any suggestions?

Thanks!

[SASKiwi]

What happens if you just run your old working code with the driver updated:

LIBNAME MyLibrary OLEDB PROMPT=NO DATASOURCE=MyServer PROVIDER='MSOLEDBSQL19' SCHEMA=dbo USER=MyUsername PASSWORD=MyPassword;

[jonburk]

Running my old code with the new driver results in this error:

Error trying to establish connection: Unable to Initialize: Client unable to establish connection. For solutions related to
encryption errors, see https://go.microsoft.com/fwlink/?linkid=2227882.: SSL Provider: The certificate chain was issued by
an authority that is not trusted.

I actually don't need the "Trust Server Certificate=True" option.  I just need to be able to pass in the "Use Encryption for Data=Optional" option, but I can't figure out how to do it.

[ChrisNZ]

Have you tried the INIT_STRING= parameter?

Another option is to put all the infomation in an UDL file and use option UDL_FILE=. This way all the syntax is as dictated by Microsoft.

[jonburk]

I haven't tried a UDL file, but I did just try INIT_STRING and got the same error.  I also tried PROMPT=YES to bring up the interactive dialog box, but it couldn't do it either, despite having the encryption and server certificate options that I'm trying to set.  I'm going to wait until we upgrade to SAS 9.4 M9 before pursuing this further.

[ChrisNZ]

Why not use UDL?

[SASKiwi]

Is ODBC an option? We use SAS/ACCESS Interface to ODBC with SQL Server a lot and haven't had any problems upgrading to newer database or database driver versions at all.

[jonburk]

No, we only have SAS/ACCESS Interface to OLE DB

[SASKiwi]

Not sure if this is any help to you but here are some examples of OLE DB connection strings up to SQL Server version 2019. These can be used as is using the LIBNAME NOPROMPT option.

Also have you tried something like this:

libname MyOLEDB oledb noprompt="Provider=MSOLEDBSQL19; Server=MyServer; Database=MyDatabase; UID=MyUsername; PWD=MyPassword; Use Encryption for Data=Optional; Trust Server Certificate=True;";

The connection string in your CONNECT statement should work unchanged in your LIBNAME statement.