topic Re: CLI ERROR - SAS/ACCESS to Amazon Redshift in SAS Procedures

CLI ERROR - SAS/ACCESS to Amazon Redshift

Jlochoa — Tue, 18 Oct 2016 14:14:09 GMT

Dear SAS Community Friends,

I recently installed the SAS/ACCESS to Amazon Redshift driver engine and I'm unsuccessful connecting to the AWS Redshift DB. Below is the code and log snippet (with altered sensitive info).

I was hoping I can get some guidance what the error means and troubleshotting tips.

Thanks in advance for your help.

LIBNAME redshift sasiorst SERVER="myserver.na-west-1.redshift.amazonaws.com"
DB=prod_data USER=lab_readonly PWD="XXXXXXXXXXXX";

ERROR: CLI error trying to establish connection: [SAS][ODBC Redshift Wire Protocol driver]Socket closed. : [SAS][ODBC Redshift Wire Protocol
driver]Socket closed. : [SAS][ODBC Redshift Wire Protocol driver][Redshift]FATAL: no pg_hba.conf entry for host "xx.xx.x.xxx", user
"lab_readonly", database "prod_data", SSL off(File /home/rdsdb/padb/src/pg/src/backend/libpq/auth.c; Line 454; Routine
ClientAuthentication; )

Current version: 9.04.01M3P062415
Operating System:   WX64_WKS.
Product expiration dates:
---Base SAS Software                                                                                    14JAN2017
---SAS/STAT                                                                                             14JAN2017
---SAS/GRAPH                                                                                            14JAN2017
---SAS/ETS                                                                                              14JAN2017
---SAS/OR                                                                                               14JAN2017
---SAS/IML                                                                                              14JAN2017
---SAS/QC                                                                                               14JAN2017
---SAS/Secure 168-bit                                                                                   14JAN2017
---SAS/Secure Windows                                                                                   14JAN2017
---SAS Enterprise Guide                                                                                 14JAN2017
---OR OPT                                                                                               14JAN2017
---OR PRS                                                                                               14JAN2017
---OR IVS                                                                                               14JAN2017
---OR LSO                                                                                               14JAN2017
---SAS/ACCESS Interface to Oracle                                                                       14JAN2017
---SAS/ACCESS Interface to PC Files                                                                     14JAN2017
---SAS/ACCESS Interface to OLE DB                                                                       14JAN2017
---SAS/IML Studio                                                                                       14JAN2017
---SAS Workspace Server for Local Access                                                                14JAN2017
---SAS/ACCESS Reserved Slot 568                                                                         30SEP2016
---High Performance Suite                                                                               14JAN2017

Re: CLI ERROR - SAS/ACCESS to Amazon Redshift

Kurt_Bremser — Tue, 18 Oct 2016 14:18:58 GMT

To me it looks like the problem occurs within the redshift odbc driver. Try to determine what should be in the pg_hba.conf file.

Re: CLI ERROR - SAS/ACCESS to Amazon Redshift

LinusH — Tue, 18 Oct 2016 15:01:04 GMT

Trouble shooting step no 1 for all SAS/ACCESS connection problems: make sure you have a working connection from another client.

Re: CLI ERROR - SAS/ACCESS to Amazon Redshift

Jlochoa — Fri, 28 Oct 2016 17:41:08 GMT

Solution was installing a missing DLL file, which is usually included with SAS/SECURE.

Editor's note: including a few more debugging tips from SAS support. This led to the discovery of the missing DLL.

This typically indicates that SSL is not configured for use outside of SAS. Please note SSL needs to be configured for use outside of SAS per Amazons directions for an ODBC connection. Amazon documentation for this can be seen here: http://docs.aws.amazon.com/redshift/latest/mgmt/connecting-ssl-support.html

Additionally you could try setting the EncryptionMethod to 6. This uses SSL if it is available per the drivers documentation found here: https://documentation.progress.com/output/DataDirect/odbcredshifthelp/index.html#page/odbcredshifthelp/encryption-method.html#wwconnect_header

Re: CLI ERROR - SAS/ACCESS to Amazon Redshift

ankitd — Tue, 05 Sep 2017 16:08:52 GMT

I was facing the same issue.

To be precise the below parameter needs to be added in the LIBNAME statement to resolve the issue

CONOPTS="EncryptionMethod=6;ValidateServerCertificate=0"

Hope this helps in avoiding to raise a SAS track 🙂