topic Re: Macro Variable for password in Libname in SAS Programming

Macro Variable for password in Libname

tedway — Thu, 15 Mar 2018 15:51:54 GMT

How do I use a macro variable for my password in a libname statement as in the example below:

%let mypwd = "{SAS002}9A8C3F4E20629B7F34D2C88A5569E5995AE23782";


libname bigdata odbc noprompt="Driver={Amazon Redshift (x86)};
Server=server.domain.edu;
Database=prod; UID=mylogin; PWD=&mypwd; Port=5439" schema=nyu;
run

I believe the fact that the connection info is already inside double quotes is causing the issue. I've confirmed that I have the connection info correct and that it works if I enter the password directly without encoding it and using a variable.

Re: Macro Variable for password in Libname

Reeza — Thu, 15 Mar 2018 15:54:50 GMT

Try adding some more quotes.

libname bigdata odbc noprompt="Driver={Amazon Redshift (x86)};
Server=server.domain.edu;
Database=prod; UID=mylogin; PWD="&mypwd"; Port=5439" schema=nyu;
run

Re: Macro Variable for password in Libname

tedway — Thu, 15 Mar 2018 16:02:34 GMT

I did try adding quotes around the macro variable (e.g.

PWD="&mypwd";

But SAS returns a "FATAL: password authentication failed for user" error.

ERROR: Error in the LIBNAME statement.

Re: Macro Variable for password in Libname

Kurt_Bremser — Thu, 15 Mar 2018 16:18:04 GMT

Your target won't understand the SAS password encoding. See http://support.sas.com/kb/31/602.html

It only seems to work with the basic password= option.

Re: Macro Variable for password in Libname

Tom — Thu, 15 Mar 2018 16:27:36 GMT

I was not able to get SAS to recognize encrypted passwords when using NOPROMPT to connect Redshift via ODBC.

Check with your admins and see if they can get the Server, Port and Database mapped to a DNS name so that you can just use the DNS= option instead of NOPROMPT. Then you should be able to get it to recognize the encrypted password.

So if they defined rs_server as the DSN name then your code might look like this.

%let username=rs_user ;
%let password=raw_password ;
%let epassword={SASENC}6FE4B62604378F370E21797F12E892A9 ;

libname test odbc dsn=rs_server schema=my_schema
    user="&username" password="&epassword"
;

Re: Macro Variable for password in Libname

Jamwat — Fri, 01 Jun 2018 13:09:15 GMT

Sorry I got ERROR: Invalid option name {.

when I ran that code.

Re: Macro Variable for password in Libname

Jamwat — Fri, 01 Jun 2018 13:09:51 GMT

I got that error

ERROR: Invalid option name {.

Re: Macro Variable for password in Libname

tedway — Fri, 01 Jun 2018 13:35:17 GMT

Try removing the quotes as in this example:

%let mypassword = "passw";
libname redshift odbc datasrc=Redshift user=jdoe  password=&mypassword. schema=nyu;