topic Re: SAS/ACCESS to Amazon Redshift - [SAS][ODBC Redshift Wire Protocol driver]Socket closed. in Administration and Deployment

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

durgagprasad_gmail_com — Thu, 21 May 2020 17:59:42 GMT

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

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

AnandVyas — Fri, 22 May 2020 15:08:53 GMT

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

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

SASKiwi — Fri, 22 May 2020 21:34:35 GMT

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.

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

durgagprasad_gmail_com — Wed, 27 May 2020 16:24:04 GMT

I tried this option and getting the same error.

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

durgagprasad_gmail_com — Wed, 27 May 2020 16:29:14 GMT

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

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

SASKiwi — Wed, 27 May 2020 20:45:22 GMT

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

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

JenniStephens — Thu, 28 May 2020 15:28:02 GMT

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?

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

durgagprasad_gmail_com — Fri, 26 Jun 2020 02:22:23 GMT

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.

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

durgagprasad_gmail_com — Mon, 13 Jul 2020 22:20:42 GMT

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