topic Re: Issue with BL_USE_SSL Option in LIBNAME Statement for Redshift in SAS Programming

Issue with BL_USE_SSL Option in LIBNAME Statement for Redshift

guy25_bms — Mon, 21 Oct 2024 03:56:22 GMT

Hi everyone,

I'm using SAS 9.4 and trying to connect to Amazon Redshift using the built-in `redshift` engine in the `LIBNAME` statement. According to the documentation, the `BL_USE_SSL` option should enable SSL, but I'm encountering an error that says the option is not recognized.

Here is the `LIBNAME` statement I'm using:

```SAS
libname myredshift redshift
server='your_redshift_server'
port=5439
user='your_username'
password='your_password'
database='your_database'
schema='public'
bl_use_ssl=1;

Based on SAS 9.4 document, bl_use_ssl is default to YES (value 1). But If I don't include bl_use_ssl=1 LIBNAME option, I got error like " ...

SSL off(File /home/ec2-user/padb/src/pg/src/backend/libpq/auth.c; Line 477; Routine ClientAuthentication;)

Anyone has idea what is going wrong here?

Thanks

Gary Gu

Re: Issue with BL_USE_SSL Option in LIBNAME Statement for Redshift

ballardw — Mon, 21 Oct 2024 15:44:46 GMT

It is best practice on this forum to not separate code from error messages and to copy the text from the LOG with the code and all the messages generated by the step in question. Then on the forum open a text box clicking on the </> icon that appears above the main message box and pasting all the text into the text box. You can edit the pasted text to XXXX over sensitive items like user and password information if needed.

It is not uncommon to find something in the LOG that the entered "code" doesn't show because the forum software reformats pasted text and may remove or insert something that caused problems.

One concern: LIBNAME should be restricted to 8 characters. You may be having one problem by using myredshift which is 10 characters. Which may mask other problems.

Re: Issue with BL_USE_SSL Option in LIBNAME Statement for Redshift

guy25_bms — Tue, 22 Oct 2024 19:07:10 GMT

Sorry for my previous message that code example is not matched with error message.

After awhile, i realized that for redshift engine, BL_USE_SSL=1 is not right. It should be BL_USE_SSL=YES. After i made change. I still get error.

Code:

libname x redshift server="&rs_server" port=5439 user="&rs_user" password="&rs_pwd" database="xxxx" BL_USE_SSL=YES schema="xxxx";

Now i got error:

ERROR: CLI error trying to establish connection: [SAS][ODBC Redshift Wire Protocol driver][Redshift]FATAL: no pg_hba.conf entry for

host "::ffff: xxxx", user "bms_rwrdmuser", database "xxxx", SSL off(File

/home/ec2-user/padb/src/pg/src/backend/libpq/auth.c; Line 477; Routine ClientAuthentication; )

ERROR: Error in the LIBNAME statement.

What does it mean here "no pg_hba.conf entry for host "...ffff: xxxx ..."?

looks like something wrong in ClientAuthentication from AWS SDK?

Thanks for your further explanation and help

Gary

Re: Issue with BL_USE_SSL Option in LIBNAME Statement for Redshift

SASKiwi — Tue, 22 Oct 2024 19:43:39 GMT

Have you had Redshift working at all with other authentication options? The error suggests there is a configuration problem on the SAS side. I suggest you discuss this with your SAS administrator. Have settings for this Redshift server been added to odbc.ini?

Re: Issue with BL_USE_SSL Option in LIBNAME Statement for Redshift

guy25_bms — Wed, 23 Oct 2024 19:31:16 GMT

without turning on SSL on Redshift server, everything works fine for a long time.

Re: Issue with BL_USE_SSL Option in LIBNAME Statement for Redshift

SASKiwi — Wed, 23 Oct 2024 20:52:04 GMT

The Redshift documentation examples suggest you also require other bulk loading options.

Also BL_USE_SSL only works with bulkunload = yes.

Re: Issue with BL_USE_SSL Option in LIBNAME Statement for Redshift

guy25_bms — Wed, 23 Oct 2024 21:06:40 GMT

Ok i think I used a wrong option (BL_USE_SSL). My purpose is to enable SSL for SAS app that connects to Amazon Redshift that its SSL is turned on (require_ssl=true). If you have any guidance / example, it is appreciated very much if you can share them.