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.
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"
;
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
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.
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.
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"
;
Try removing the quotes as in this example:
%let mypassword = "passw";
libname redshift odbc datasrc=Redshift user=jdoe password=&mypassword. schema=nyu;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.