SAS/ACCESS to Amazon Redshift - [SAS][ODBC Redshift Wire Protocol driver]Socket closed.

[durgagprasad_gmail_com]

25 GOPTIONS ACCESSIBLE;
26 libname B1 SASIORST dsn=RedShift_conn user="xxxxxx" password=XXXXXXXXXX schema='shel';
NOTE: Libref B1 was successfully assigned as follows:
Engine: SASIORST
Physical Name: RedShift_conn
27
28
29

30 proc sql;
31 select count(*) from B1.rebate_app;
ERROR: CLI prepare error: [SAS][ODBC Redshift Wire Protocol driver]Socket closed.
SQL statement: SELECT * FROM "shel".REBATE_APP.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
32 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

[Anand_V]

Is this something similar you can try?
https://communities.sas.com/t5/SAS-Procedures/CLI-ERROR-SAS-ACCESS-to-Amazon-Redshift/td-p/305384

[durgagprasad_gmail_com]

I tried this option and getting the same error.

[SASKiwi]

The fact that the LIBNAME assigned OK, but the SQL query got the CLI error suggests to me that connection settings are good but the connection itself is being interrupted for some reason. Try adding this tracing option:

options sastrace = ',,,d' SASTRACELOC = SASLOG;

before re-running your query to see if it helps with figuring what is going wrong.

[durgagprasad_gmail_com]

Tried with the trace option. is there any other tracing option available to get more information on this ERROR?

32 proc sql;
33 select count(*) from RSHIFT.ev_rebate_application;
6 1590596507 no_name 0 SQL (2)
REDSHIFT_2: Prepared: on connection 1 7 1590596507 no_name 0 SQL (2)
SELECT * FROM "shelecv".EV_REBATE_APPLICATION 8 1590596507 no_name 0 SQL (2)
9 1590596507 no_name 0 SQL (2)
ERROR: CLI prepare error: [SAS][ODBC Redshift Wire Protocol driver]Socket closed.
SQL statement: SELECT * FROM "shelecv".EV_REBATE_APPLICATION.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
34 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

[SASKiwi]

@durgagprasad_gmail_com  - I think progressing this with SAS Tech Support is your best option. Could be worth checking with your IT network folks to see if there are any issues with firewall rules blocking ports. Although if that were the case then I would have thought the LIBNAME would fail.

[JenniStephens]

This typically occurs when there is a permissions error on the Redshift DB. Can you run this query as the same user outside of SAS and verify no errors occur? 

[durgagprasad_gmail_com]

I have tried with other client (Razorsql)  from SAS server and able to connect the redshift with SSH option. at the same time connection is not work working without ssh thru Razorsql.

[durgagprasad_gmail_com · Accepted solution]

Issue is with firewall. Firewall team is added the NAT rule. now I can able to connect the DB.