Encrypted Password is displayed in logs. I want to avoid printing encrpyted password in logs.

[rv_sas_1990]

Hi Team,

We are using SAS 9.4 and also encrypting our password using PROC PWENCODE. But the issue is, this encrypted password is printed in logs at explicit SQL Pass through. How can I avoid this as its always a risk even to display the encrypted password in logs.

Thanks.

[jklaverstijn]

The (my) preferred way to keep passwords of any kind, cleartext, hashed or otherwise, from the code and logs is by using authentication domains. This way you can store the password safely in the metadata sever and never see it surface in logs or anywhere else.

Create a metadata group and define the account you use to access the database as one of its account. Assign it to a new authentication domain. Make the appropriate users and groups member of that group. Now you can do something like this:

proc sql;
connect to mysql server=xyz authdomain=sqlauthdomain;
...
quit;

If the account that runs the code is a member of that group then it will pick up the account info including the password and it will never be echoed to the log.

Alternatively, you can define such an account as one of the accounts in your own personal user metadata.

Hope this helps,

- Jan.

[rv_sas_1990]

Authdomain is an effective solution but our SAS COE won't approve to maintain passwords in the metadata server.

[Tom]

---

@rv_sas_1990 wrote:

Authdomain is an effective solution but our SAS COE won't approve to maintain passwords in the metadata server.

---

Ask them it they prefer that they are stored in plain text SAS log files instead?

[SASKiwi]

@rv_sas_1990 - SAS service accounts and their passwords are required to be stored in SAS metadata when installing SAS. You can't avoid this. Why the objection to storing additional service accounts/passwords for database access in SAS metadata?

What databases are you connecting to? Some allow Windows Authentication or / single sign-on like SQL Server from SAS on Windows. That avoids having to supply user credentials completely. 

[Tom]

---

@SASKiwi wrote:

@rv_sas_1990 - SAS service accounts and their passwords are required to be stored in SAS metadata when installing SAS. You can't avoid this. Why the objection to storing additional service accounts/passwords for database access in SAS metadata?

What databases are you connecting to? Some allow Windows Authentication or / single sign-on like SQL Server from SAS on Windows. That avoids having to supply user credentials completely. 

---

I could see an objection to SAS users sharing a common account to access a remote database. The remote database's audit trail does not really know who did what.  I don't use SAS metadata servers, but doesn't it allow a user to store their own account credentials?

[Tom]

What we always did was make sure to turn off the options that print things to the log.

For example you could make a macro like the one described by this header to read the password from a file and never store it into a macro variable.

%macro pwread
/*----------------------------------------------------------------------
Reads external file and returns specified word
----------------------------------------------------------------------*/
(file   /* Fileref or physical filename */
,word   /* Which word to return. (1,2,3, or -1) Default=-1 */
);

/*----------------------------------------------------------------------
The PWREAD macro can be used to retrieve a password and/or username from
a text file.  This will allow you to run batch jobs without storing your
password into the SAS program.

The password file format is one line with 2 or 3 words.
Example:
[/home/username]->more ~/HOSTNAME.txt
ops$username examplepw {SAS002}CF709D134675973F50C3945351CAFB81

To prevent other's from reading your password file set the operating
system file permissions so that only the owner can read or modify it.

Example command to set permissions on Unix:
> chmod 600 ~/HOSTNAME.txt

-----------------------------------------------------------------------
Usage:
%let pwfile=~/HOSTNAME.txt;
proc sql ;
  connect to oracle (path="@hostname" user="%pwread(&pwfile,1)"
    password="%pwread(&pwfile)" );

------------------------------------------------------------------------
Notes:
- To prevent password from appearing in the LOG the MPRINT option
  must be off.  Macro will stop processing if MPRINT option is on.
- To prevent the password from appearing in the LOG do not assign the
  value to a macro variable.  Instead just call the %PWREAD macro
  everywhere that you need to access the password.
- Use word=-1 to get the password.  This will return the encrypted
  password when it exists otherwise it will return the open password.
- You cannot use the encrypted password with SAS versions before 9.1.3
- You can use PROC PWENCODE to create the encrypted version of your
  password.

Adapted from FREAD macro by HOFFMAN CONSULTING 
----------------------------------------------------------------------*/

[AnandVyas]

Interesting topic. Does this help?
https://support.sas.com/resources/papers/proceedings09/013-2009.pdf

[rv_sas_1990]

SYMGET() will not resolve macro variable value in logs. Unfortunately, SYMGET() works only in the DATA step. I am looking for Proc connect.

[jklaverstijn]

Again: authdomain is your friend. Also when doing sas/connect.
You will need to talk to your SAS COE. Short of going down the Kerberos rabbit hole it is the best of all your options. If this does not answer his/her concerns that I wonder what does. It's easy as that. also I would not regard storing credentials in the metadata server as "maintaining passwords". That would happen in your identity management system in concert with Active Directory. I suggest you have that talk and otherwise let that SAS COE suggest a better (=safer) approach.

Regards, Jan.