Proc Pwencode in oledb libname

[path2success]

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!!

[SASKiwi]

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]

Thanks SASKiwi. Unfortunately, neither of those work.

[path2success]

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]

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

[path2success]

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]

Attribute the password to a global password as defined in autoexec.sas, i.e. %global g_password ; %let g_password='{SAS002}XXXXXXX'...

[path2success]

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]

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]

Of course every little bit helps! 😉 (;

[SASKiwi]

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