BookmarkSubscribeRSS Feed
path2success
Fluorite | Level 6

Hi , 

 

 I want to use PRCO PWENCODE on the libname statement given below-

 


libname testme oledb schema=tstsch init_string="Provider=OraOLEDB.Oracle.1;
                           Password=creatmepsswd;
                           User ID=createmeusr;
                           Data Source=TESTDS;
                           Persist Security Info=True;";

 

I am able to use the PROC PWENCODE and get the encoded password but I insert it in the password statement, I get an error. 

For ex: if the encoded password is- {SAS002}xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx, then when I update my libname statement , I get an error right at the "{" before the "SAS...". 

 


libname testme oledb schema=tstsch init_string="Provider=OraOLEDB.Oracle.1;
                           Password="{SAS002}xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
                           User ID=createmeusr;
                           Data Source=TESTDS;
                           Persist Security Info=True;";

 

Any idea, what I might be doing wrong here or is the syntax wrong? 

Any suggestions are tremendously helpful!

 

Thanks!!

 

10 REPLIES 10
SASKiwi
Opal | Level 21

I'd try removing the interior quotes around the password as these are probably confusing SAS:

 

init_string="Provider=OraOLEDB.Oracle.1;
                           Password={SAS002}xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx; 
                           User ID=createmeusr;
                           Data Source=TESTDS;
                           Persist Security Info=True;";

If that doesn't work, try enclosing the password in single quotes instead.

 

path2success
Fluorite | Level 6

Thanks SASKiwi. Unfortunately, neither of those work.

path2success
Fluorite | Level 6

If it helps, what my intent is - 

 

We use the above libname statement(here I have used TEST case for privacy purposes) very often for pulling our data. However, I want to create a macro that will encode or mask the password and username in this libname statement and the programmer could call it as a one line macro or use it as a macro variable in the libname for the password and username.

 

Using "autoexec" is not a good option b/c the programmer could just go to the location where the libname statement is saved and get the password. 

 

I thought proc pwencode would be  a best option but I am not able to figure it out to work for this libname statement. 

ccaulkins9
Pyrite | Level 9
Hi, path - Your intentions being equally good and somewhat redundant, you could try to pass the encrypted values into global variables in autoexec.sas - thus modifying the metadata of the SAS Intelligence platform you might be using. Let me know if you've used this approach before - Regards, Chad Caulkins
e-SAS regards,

path2success
Fluorite | Level 6

Hi ccaulkin, 

 

Thank you for your reply! But I am not sure I fully understood your response. 

 

Could you please give an example of what you suggested?

 

Thanks again!

ccaulkins9
Pyrite | Level 9
Attribute the password to a global password as defined in autoexec.sas, i.e. %global g_password ; %let g_password='{SAS002}XXXXXXX'...
e-SAS regards,

path2success
Fluorite | Level 6

This is what I did-

 

My autoexec_nu.sas file has this code(as below);

 

%global g_password ; 
%let g_password='{SAS002}3FE1903252C09F4A1054A17D01ED6AE63F2D401C';


 

 

Then in another SAS file , I am calling this g_password in the libname statement and I am gettig this error-

 

 

%INCLUDE "C:\Users\newusr\SAS\autoexec_nu.SAS"; - this is where my autoexec_nu file is saved
libname test oledb schema=tstsh init_string="Provider=OraOLEDB.Oracle.1;
Password="&g_password";
User ID=tstusr;
Data Source=tstds;
Persist Security Info=True;";

LOG:
7 libname test oledb schema=tstsh init_string="Provider=OraOLEDB.Oracle.1;
8 Password="&g_password";
NOTE: Line generated by the macro variable "G_PASSWORD".
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'{SAS002}3FE1903252C09F4A1054A17D01ED6AE63F2D

--------------------------------------------------
22
1 ! 401C'
ERROR 22-7: Invalid option name '{SAS002}3FE1903252C09F4A1054A17D01ED6AE63F2D401C'.

9 User ID=tstusr;
10 Data Source=tstds;
11 Persist Security Info=True;";
ERROR: Libref test is not assigned.
ERROR: Error in the LIBNAME statement.

 

SASKiwi
Opal | Level 21

Is it the same error if you remove the both the double and single quotes around the password? The OLEDB string doesn't appear to be formatted correctly. I'm assuming the one that works doesn't wrap the unencrypted password in quotes? 

ccaulkins9
Pyrite | Level 9
Of course every little bit helps! 😉 (;
e-SAS regards,

SASKiwi
Opal | Level 21

Please post what you tried and the error from your SAS log.

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1817 views
  • 8 likes
  • 3 in conversation