topic Re: Proc Pwencode in oledb libname in SAS Procedures

Proc Pwencode in oledb libname

path2success — Thu, 04 May 2017 02:06:12 GMT

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

Re: Proc Pwencode in oledb libname

SASKiwi — Thu, 04 May 2017 08:05:11 GMT

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.

Re: Proc Pwencode in oledb libname

path2success — Thu, 04 May 2017 15:27:05 GMT

Thanks SASKiwi. Unfortunately, neither of those work.

Re: Proc Pwencode in oledb libname

path2success — Thu, 04 May 2017 15:35:45 GMT

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.

Re: Proc Pwencode in oledb libname

ccaulkins9 — Thu, 04 May 2017 17:46:35 GMT

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

Re: Proc Pwencode in oledb libname

path2success — Thu, 04 May 2017 18:11:22 GMT

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!

Re: Proc Pwencode in oledb libname

ccaulkins9 — Thu, 04 May 2017 20:06:31 GMT

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

Re: Proc Pwencode in oledb libname

SASKiwi — Thu, 04 May 2017 23:31:50 GMT

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

Re: Proc Pwencode in oledb libname

path2success — Thu, 04 May 2017 23:51:37 GMT

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.

Re: Proc Pwencode in oledb libname

SASKiwi — Fri, 05 May 2017 00:09:16 GMT

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?

Re: Proc Pwencode in oledb libname

ccaulkins9 — Mon, 25 Mar 2019 14:36:50 GMT

Of course every little bit helps! 😉 (;