Macro Variable for password in Libname

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Macro Variable for password in Libname

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.


Accepted Solutions
Solution
‎03-15-2018 12:41 PM
Super User
Super User
Posts: 7,763

Re: Macro Variable for password in Libname

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

View solution in original post


All Replies
Super User
Posts: 22,581

Re: Macro Variable for password in Libname

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
Occasional Contributor
Posts: 5

Re: Macro Variable for password in Libname

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.

 

Super User
Posts: 9,369

Re: Macro Variable for password in Libname

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎03-15-2018 12:41 PM
Super User
Super User
Posts: 7,763

Re: Macro Variable for password in Libname

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"
;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 106 views
  • 0 likes
  • 4 in conversation