Many people mistakenly assume that just because you want to use a SAS program to access a protected resource (such as a database table), you must include the credentials for the resource inside your program.
Few things cause a database administrator to lose more sleep than coming across this within a SAS program:
libname ora10 oracle path=ora10g2 schema=PAYROLL user=HR PASSWORD="SHHH_SECRET"; |
It doesn't have to be this way! SAS 9.2 offers so many security-related features that you should never have to code user IDs and passwords in your SAS programs again. This blog post summarizes my five favorite approaches.
1. Use the AUTHDOMAIN= option to access SAS libraries
The AUTHDOMAIN option allows you to delegate the authentication by allowing SAS to "look up" credentials as needed using the SAS metadata environment. For each domain that has a unique set of credentials, your SAS administrator can create an "auth domain" in metadata and associate it with a database server (or other resource). Every SAS/ACCESS database engine supports AUTHDOMAIN=, in LIBNAME statements as well as in PROC SQL CONNECT statements.
With AUTHDOMAIN, the above LIBNAME example becomes:
libname ora10 oracle path=ora10g2 schema=PAYROLL AUTHDOMAIN="ORACLE" |
The beauty of this solution is that SAS can resolve the database credentials differently for each user or group who runs this program, using the credentials that are defined in metadata for those identities.
Bonus: AUTHDOMAIN works for other resources too, such as FTP connections.
2. Use the META engine or INFOMAPS engine to access data sources
The META engine provides a layer of indirection in front of a SAS library, so that not only do you not need credentials to access it, you don't even need to know the details of how the data tables are stored. A LIBNAME statement can be as simple as:
libname MYLIB META Library="My SAS Library"; |
where "My SAS Library" is an administered library that a SAS administrator defined in SAS Management Console. MYLIB might resolve to a folder with SAS data sets, or it might be a set of tables in a Teradata database. The implementation details are hidden from the program and the programmer.
Sometimes you might see an example that looks like this:
libname MYLIB META Library="My SAS Library" metaserv="host.company.com" /* don't want this */ metaport=8561 /* don't want this */ user="userid" /* don't want this */ pw="mySecret"; /* don't want this */ |
If you run your programs in SAS Enterprise Guide or within a SAS stored process, you don't need (and don't want) the connection/credential information! The SAS session you're connected to already knows who you are, and the extra connection information isn't necessary. (If you make use of the META engine in client applications, you'll want to read up on the nuances described in this tech support paper.)
The INFOMAPS engine provides an administered view of data, further abstracted from the physical structure of the data tables. Check out my previous post for a detailed example of programming with Information Maps.
3. Use SAS Token Authentication
SAS Token Authentication uses your established SAS metadata connection to generate and validate single-use tokens for every other SAS-related resource that you might need access to. To put this another way, once the SAS metadata server knows who you are, it "vouches for you" and facilitates connections to anything else you might need, including SAS workspace servers, OLAP servers, database servers (using AUTHDOMAIN) and more.
A big advantage of SAS Token Authentication is that you don't actually need a host account for all of the resources that you might connect to. This cuts down on the sys admin tasks required to get a group of users up and running. It's a best-practice alternative to using group host accounts on a SAS workspace server; configure the SAS workspace server to use SAS token authentication instead.
4. Use Integrated Windows Authentication
The best way to hide passwords? Don't have them in the first place. That's what many customers do when they implement fingerprint readers or retina scanners in their corporate workstations. The users of these workstations wouldn't know how to supply a password if you asked for one.
Even if your users need a password to log in to your workstation, you can use Integrated Windows Authentication to prevent them from seeing another password challenge as they connect to their SAS environment.
SAS Token Authentication and Integrated Windows Authentication are examples of authentication mechanisms that SAS 9.2 supports. Using these authentication mechanisms can reduce the "authentication friction" that results when your SAS applications must hop among different protected resources that would traditionally require a user ID and password to access.
5. If you must, use PROC PWENCODE to obscure passwords
Sometimes, despite your best efforts, you cannot avoid the odd password in your programs. For example, if you've got to access password-protected SAS data sets, you need to specify the password. But there is no need to have the clear-text password appear in your code. You can use the PWENCODE procedure to encode the password so that prying eyes cannot guess at it. For example:
proc pwencode in="ItzaSecret" method=sas002; run; |
yields this in the log:
18 proc pwencode in=XXXXXXXXXXXX
19 method=sas002;
20 run;
{sas002}A16E11553D7986C1414C840C1B9E412A2C2342FC
NOTE: PROCEDURE PWENCODE used (Total process time):
real time 0.01 seconds
cpu time 0.00 secondsYou can then use the encoded password in your program:
proc means data=mylib.protected (pw="{sas002}A16E11553D7986C1414C840C1B9E412A2C2342FC"); run; |
WARNING: Using an encoded password does not prevent someone else from running your program, encoded-password intact, and accessing the same protected resource. So you still need to protect the content of your program. However, at least someone glancing over your shoulder won't be able to guess your password, and most likely won't be able to memorize the encoded gobbledy-gook that appears in the password field.
21 Comments
Further obfuscation would be to create a macro variable containing the password (in a separate script on the same SAS session) so that anyone trying to recycle your script just has &pwd references that won't resolve to anything.
AUTHDOMAIN= option seems to be working for connections to Teradata from Enterprise Guide 4.2. However the same doesn't work from DI Studio 4.2.
Internally SAS uses proc metadata to retrieve credentials from Metadata logins repository.
EG allows any user, with metadata stored, to extract logins details for that user.
However DI Studio 4.2 reports *Invalid credentials or Access Denied* error messages if an attempt is made to connect to Teradata or Mainframe using the AUTHDOMAIN option in the explicit proc sql.
I guess it's the use of the procedure *proc metadata* that issues the error messages from within DI 4.2
Atul, I hope that you follow up with SAS Technical Support if this is holding you back. I think that there must be a way to get this to work, even in Data Integration Studio.
Pingback: The SAS code formatter: more than a pretty face - The SAS Dummy
The MACRO variable that "anonymous" describes is the only way we recommend. The macro variables are stored with a common name and relative directory location in the user's protected home directory and %included as needed. We like that better than pwencode because the appropriate user shows in the audit trail.
[We don't have a metadata server and our DBAs don't like Integrated Windows Authentication (we mostly have databases on Unix servers).]
Pingback: Using PROC SQL to get the schema of a MySQL database - The SAS Dummy
Pingback: Clueless clients: Connecting them to SAS metadata libraries - SAS Users Groups
Pingback: How to use Gmail to send a message from a SAS program - The SAS Dummy
Another more simple approach to user user/password in code.
Let a SAS macro resolve them with a password-vault file on a well secured OS-layer location
For example the personal user home location. Say the "my documents" of Windows.
Anybody heard complaining that location is not safe?
If you want to truly integrate with the Windows Vault, you could use the CredReadW and CredWriteW APIs (Advapi32.dll). On a Windows version of SAS you could use the MODULEN function to access these. It might be a little tricky to get the API call just right.
SAS Macro variable content can be unveiled using SYMBOLGEN. So you need a strategy to hide your MACRO variable content from other users. Here is a nice explanation: http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.176.554&rep=rep1&type=pdf
Pingback: SAS metadata and third-party databases—an FAQ - SAS Users Groups
Hi Chris,
I use Authdomain when ever I can... but for all the users that's another ball game, we have to change our Oracle password every month. I would like to make a store process to change the Oracle password through a proc sql(this part it work fine), but is there a way to go and programmatically change the password in the authentication domain in the metadata ?
Laurent, have you looked at the setPassword utility? I found it my PC here: C:\Program Files\SASHome\SASPlatformObjectFramework\9.4.
I've just found it a few minutes ago, I also found the documentation in setpassword.html. I'll give it a try Thanks!
Thank you, it work fine. I noticed I have to provide the admin password, I've tried to pwencode it, but setpassword doesn't recognize it. We use IWA, my login has admin privilege, so for it to work for all my users in my stored proc, I should probably make an internal account with admin privilege and never change the password or is there another way ?
Thanks!
I agree, it's probably best to create an internal utility account for something like this. Just be sure to take all appropriate measures to ensure it can't be accessed/abused by those who don't have privileges. You might check with SAS Tech Support for more guidance on the setpassword tool.
Pingback: Using PROC IOMOPERATE to list and stop your SAS sessions - The SAS Dummy
Pingback: One deadly sin SAS programmers should stop committing - SAS Users
I would like to add a CAUTION while using any passwords in a macro with MPRINT option. The replaced text that MPRINT option writes in the log does not x'hash the passwords. Try running is sample code in your SAS Editor:
=============================================
proc pwencode in="MyPassword";
run;
option mprint;
%macro PwEncode;
proc pwencode in="MyPassword";
run;
%mend PwEncode;
%PwEncode;
=============================================
Log output:
=============================================
1 proc pwencode in=XXXXXXXXXXXX;
2 run;
{SAS002}68B2795625183A1B4367CCB10DD05CCA0D02F914
NOTE: PROCEDURE PWENCODE used (Total process time):
real time 0.03 seconds
cpu time 0.00 seconds
3
4 option mprint;
5 %macro PwEncode;
6 proc pwencode in="MyPassword";
7 run;
8 %mend PwEncode;
9 %PwEncode;
MPRINT(PWENCODE): proc pwencode in="MyPassword";
MPRINT(PWENCODE): run;
{SAS002}68B2795625183A1B4367CCB10DD05CCA0D02F914
NOTE: PROCEDURE PWENCODE used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
=============================================
First proc step that is outside a macro successfully hashed the password in log but the second proc step that is inside a macro didn't.
Thanks and regards,
JNP
Good tip! Always protect your passwords, and using PWENCODE in a macro might be an odd thing to do that doesn't promote good security practices.