BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tdwilliams1
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

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.

 

View solution in original post

10 REPLIES 10
s_lassen
Meteorite | Level 14

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
Calcite | Level 5

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
Calcite | Level 5

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
PROC Star

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
Meteorite | Level 14

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
Calcite | Level 5

That did it!  THANKS!

Tom
Super User Tom
Super User

@s_lassen 

That is great!

ChrisNZ
Tourmaline | Level 20

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
Calcite | Level 5

is format works for LIBNAME, but not for Pass through

ChrisNZ
Tourmaline | Level 20

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

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 2016 views
  • 4 likes
  • 5 in conversation