Shield SQL Userid and Pwd

[shashikalachiluka]

Hi There,

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

Shashikala Chiluka

[tish]

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.

[shashikalachiluka]

Hi Tish,

    Thanks for the reply. This definitely helps...

Regards,

Shashikala Chiluka

[Pritish]

You can also use Proc Pwencode procedure.

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a003199418.htm

HTH

[Doc_Duke]

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.

Doc Muhlbaier

Duke

[Quentin]

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:

%macro password();

options nomprint;

MySecretPassword

options mprint;

%mend;

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.

[SASKiwi]

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 .