07-27-2012 11:19 AM
Is there a way to shield SQL userid and Pwd from being displayed in the SAS program. I know we could pass in the usrid and pwd from command prompt while executing, Is there any other way to retrieve this info from a specified location.
Thank you all for the inputs..
07-27-2012 11:27 AM
I've done this in the past when passing passwords through to a data warehouse. I set up a macro library file in my personal space on a secure server. That file had one macro, with just the command to send in the password. In my main program I point to the macro library. When I run PROC SQL, I invoke my password macro. So long as MPRINT is not on, the passwords are not displayed.
This also works in the situation where more than one person might run a program, but they need to sign on to the remote server separately. They each have an identically named little macro library with the identically named macro which all issue the specific userid and pwd.
Hope this helps.
07-27-2012 11:54 AM
You can also use Proc Pwencode procedure.
07-27-2012 01:30 PM
I've never thought that PWENCODE had any advantage over Tish's approach (which I also use). With PWENCODE, you still have to protect the file with the encoded PW in it. I use the protected file approach to include the entire libname or connect statement.
07-27-2012 02:06 PM
I think there may be some small benefits to PWENCODE (in addition to Tish's method). In an environment where you share code, people sometimes try to share secret passwords in an encrypted compiled macro like:
Which usually keeps things secret until someone curious tries:
%put %bquote(%password() );
I guess the real benefit of adding PWENCODE is that if source code for the macro is revealed, you don't see the actual password (useful to anyone who might want to misuse it for any application), you see the encoded password (useful only to SAS programmers). I suppose could also help someone meet a strict corporate rule not to store clear-text passwords anywhere.
07-29-2012 04:05 PM
I'd be interested in knowing what database the userid and password needs to be shielded for. If you are lucky enough to be in an entirely Windows environment and your databases can handle Windows authentification, for example SQL Server and SAS data, then you don't need to supply a userid or password at all and rely on the userid/password of the Windows account the SAS job is being run from .