BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jlochoa
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
Jlochoa
Obsidian | Level 7

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

4 REPLIES 4
LinusH
Tourmaline | Level 20
Trouble shooting step no 1 for all SAS/ACCESS connection problems: make sure you have a working connection from another client.
Data never sleeps
Jlochoa
Obsidian | Level 7

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

 

ankitd
Obsidian | Level 7

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 🙂

SAS Innovate 2025: Call for Content

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!

Submit your idea!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 7497 views
  • 5 likes
  • 4 in conversation