CLI ERROR Trying to establish connection is issued when attempting to access a database using a SAS/ACCESS to ODBC library defined in the Mgmt Console


The following error message (or a similar message) might occur when attempting to define a libref using the ODBC engine via the Data Library Manager in the SAS® Management Console. However, similar errors may be issued when using a different ACCESS Engine.

ERROR: CLI error trying to establish connection: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'userid'.
ERROR: Error in the LIBNAME statement.

The same LIBNAME definition can be successfully assigned outside of SAS Management Console using the SAS Program Editor.

What is happening is that the password is encoded in the SAS Management Console. To validate this, from the SAS Management Console, complete the following steps:

  1. Right-click the LIBRARY.
  2. Select display libname.
  3. Copy and paste the exact libname from the SAS Management Console into the SAS Program Editor, and submit it.

The above steps should generate the same error.

The following steps demonstrate how the password in the SAS Management Console gets translated to all asterisks when logged on as SASADM or unrestricted user and show how to use PWENCODE to obtain a valid encoding of the password:

  1. The following LIBNAME statement is displayed in the SAS Management Console and does not work when pasted to the SAS Program Editor:
    LIBNAME dbo ODBC DATASRC=ODS SCHEMA=dbo USER=fruitless
    PASSWORD="{sas001}KioqKioqKio=" ; /* the password above is an encoded version of all asterisks */

  2. Below is an explicit LIBNAME statement that works in the SAS Program Editor:
    LIBNAME dbo ODBC DATASRC=ODS SCHEMA=dbo USER=fruitless
    PASSWORD="venture" ;

  3. To obtain the valid encoding of the password venture, run the following:
    PROC PWENCODE in='venture'; run;

  4. From the SAS Program Editor, submit the LIBNAME statement using the new encoded password, which will now work:
    LIBNAME dbo ODBC DATASRC=ODS SCHEMA=dbo USER=fruitless
    PASSWORD="{sas001}dmVudHVyZQ==" ;

The error and symptoms are caused because the LIBNAMEs are being used as the SASADM user or an unrestricted user and should be used as the SASDEMO user or any other user that is not unrestricted. Unrestricted users should only be used for managing user accounts and servers.

The encoded password of asterisks should be a clear indication that this is the problem. Unrestricted users (SASADM) are not allowed to read/use anybody's password, so even if everything is set up correctly and you are logged in with SASADM, you will still see this encoded password.

To resolve this problem, login as SASDEMO or as a user who is not unrestricted when using the LIBNAME statement.