Redshift ODBC Connection with PROC PWENCODE

[tdwilliams1]

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!

[s_lassen]

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.

[tdwilliams1]

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.

[tdwilliams1]

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

[SASKiwi]

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.

[s_lassen · Accepted solution]

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.

[tdwilliams1]

That did it!  THANKS!

[Tom]

@s_lassen 

That is great!

[ChrisNZ]

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;

[tdwilliams1]

is format works for LIBNAME, but not for Pass through

[ChrisNZ]

> 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   );