topic Re: Redshift ODBC Connection with PROC PWENCODE in SAS Programming

Redshift ODBC Connection with PROC PWENCODE

tdwilliams1 — Wed, 30 Oct 2019 12:41:35 GMT

To start my question, I would like to be clear that I am bound my systems security settings that are beyond my control <sigh>,

I am using Enterprise Guide 8.1 and connecting to a Redshift Database via a DSN-less ODBC connnection. Such as:

proc sql outobs = 10 ;
connect to odbc (noprompt= "Driver={Amazon Redshift (x64)}; Server=MyServer;Database=MyDb; UID=MyUser; PWD=MyPassword; Port=0000");
select * 
from connection to odbc ( 
select distinct TableName
from pg_tables 
where schemaname = 'public' 
)
;
quit;

This method works, but has the downside of displaying my password in clear text i the log.I have been experimenting with PROC PWENCODE and have successfully connected to ORACLE and Netezza databases using the results from PROC PWENCODE. I have not been able to connect to redshift using the PWENCODE value.

I tried simply replacing my password in the above code with the PWENCODE string, which did not work. I tried enclosing in single quotes which did not work either. I tried a set of double quotes, which did not work either.

I know using PWENCODE is NOT encryption, but our current system does not support integrated authentication, so I'm stuck using passwords. Has anyone been able to use PWENCODE with the Redshift ODBC driver without creating a DSN file? If so, how did you construct your connection string?

Thanks!

Re: Redshift ODBC Connection with PROC PWENCODE

s_lassen — Wed, 30 Oct 2019 13:28:12 GMT

I think the problem is that when you use a NOPROMPT statement, the literal string inside the quotes is passed to ODBC. But I think it is possible to put the user and password outside the noprompt part of the connection, e.g.

connect to odbc(noprompt= "Driver={Amazon Redshift (x64)}; Server=MyServer;Database=MyDb; Port=0000"  user=MyUser password=MyPassword)

and I think that when done like that, SAS will deencrypt an encrypted password.

Re: Redshift ODBC Connection with PROC PWENCODE

tdwilliams1 — Wed, 30 Oct 2019 14:12:50 GMT

s_Lassen,

Thank you for the suggestion! Unfortunately I am still having trouble connecting

Changing to

connect to odbc (
noprompt= "Driver={Amazon Redshift (x64)}; 
Server=MyServer;
Database=MyDb; 
UID=MyUser; 
Port=0000"

PWD=MyPassword

);

Generates the following error:

ERROR: CLI error trying to establish connection: [Amazon][DSI] (20032) Required setting 'PWD' is not present in the connection
settings.

Moving the UID out of the quoted string creates a similar error for the UID.

Re: Redshift ODBC Connection with PROC PWENCODE

tdwilliams1 — Wed, 30 Oct 2019 18:41:28 GMT

I found a discouraging SAS article on this topic.

http://support.sas.com/kb/31/602.html

"...To circumvent this problem, you must use an unencoded password when you specify the COMPLETE=, PROMPT=, REQUIRED=, and NOPROMPT= options..."

Re: Redshift ODBC Connection with PROC PWENCODE

SASKiwi — Wed, 30 Oct 2019 19:13:08 GMT

Unfortunately, without integrated authentication, I think you are stuck. We use the same technique you use - DSN-less ODBC connections - but to SQL Server using integrated authentication and it works a treat. Much better than maintaining DSNs.

In your case though it looks like using DSNs is the only way to avoid passwords showing. You could perhaps define your password in a macro variable but you would still have to set that up somewhere so you are not much better off.

Re: Redshift ODBC Connection with PROC PWENCODE

ChrisNZ — Wed, 30 Oct 2019 21:51:03 GMT

If you don't use the NOPROMPT option, the password shows as Xs in the log.

libname GDW odbc user="&username" password="&password" schema=NW datasrc=SR access=readonly;

Re: Redshift ODBC Connection with PROC PWENCODE

s_lassen — Thu, 31 Oct 2019 10:48:59 GMT

Found another solution that seems to work: Use the connection string as input to PROC PWENCODE, and then use the output value in the noprompt value:

proc PWENCODE in="Driver={Amazon Redshift (x64)}; Server=MyServer;Database=MyDb; UID=MyUser; PWD=MyPassword; Port=0000";
run;
%let constr=NOPROMPT="&_PWENCODE";

You can then use the Constr macro variable for connection:

proc sql;
  Connect to odbc(&constr);

The reason I saved the value in the macro variable CONSTR and did not use the automatic variable _PWENCODE directly is that you may be using PROC PWENCODE for other stuff, which will change the value of _PWENCODE.

Re: Redshift ODBC Connection with PROC PWENCODE

tdwilliams1 — Thu, 31 Oct 2019 14:47:18 GMT

is format works for LIBNAME, but not for Pass through

Re: Redshift ODBC Connection with PROC PWENCODE

tdwilliams1 — Thu, 31 Oct 2019 14:47:50 GMT

That did it! THANKS!

Re: Redshift ODBC Connection with PROC PWENCODE

Tom — Thu, 31 Oct 2019 15:16:33 GMT

@s_lassen

That is great!

Re: Redshift ODBC Connection with PROC PWENCODE

ChrisNZ — Thu, 31 Oct 2019 22:23:26 GMT

> works for LIBNAME, but not for Pass through

Not at all.

libname GDW  odbc user="&username" password="&password" schema=NW datasrc=SR access=readonly;

proc sql;
  connect using GDW;
  select * from connection to GDW (   passthru code   );