Help using Base SAS procedures

CLI ERROR - SAS/ACCESS to Amazon Redshift

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

CLI ERROR - SAS/ACCESS to Amazon Redshift

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; )

 RedShift.jpg

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

Accepted Solutions
Solution
‎10-28-2016 01:06 PM
Occasional Contributor
Posts: 17

Re: CLI ERROR - SAS/ACCESS to Amazon Redshift

[ Edited ]

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...

 

View solution in original post


All Replies
Super User
Posts: 6,963

Re: CLI ERROR - SAS/ACCESS to Amazon Redshift

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,260

Re: CLI ERROR - SAS/ACCESS to Amazon Redshift

Trouble shooting step no 1 for all SAS/ACCESS connection problems: make sure you have a working connection from another client.
Data never sleeps
Solution
‎10-28-2016 01:06 PM
Occasional Contributor
Posts: 17

Re: CLI ERROR - SAS/ACCESS to Amazon Redshift

[ Edited ]

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...

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 550 views
  • 3 likes
  • 3 in conversation