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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

7 REPLIES 7
Reeza
Super User

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
tedway
Obsidian | Level 7

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.

 

Tom
Super User Tom
Super User

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"
;
Jamwat
Obsidian | Level 7
I got that error

ERROR: Invalid option name {.
Jamwat
Obsidian | Level 7
Sorry I got ERROR: Invalid option name {.

when I ran that code.
tedway
Obsidian | Level 7

Try removing the quotes as in this example:

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 7 replies
  • 3911 views
  • 0 likes
  • 5 in conversation