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
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/odbcredshifthe...
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.
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/odbcredshifthe...
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 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.