What's the best way to use SAS to access a password protected database and keep the password private. I want to avoid having the password and user name as part of the SAS code.
Any suggestions?
Use AUTHDOMAIN= Libname option
http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a003175613.htm
If you specify AUTHDOMAIN=, you must specify SERVER=. However, the authentication domain references credentials so that you do not need to explicitly specify USER= and PASSWORD=. An example is authdomain=MyServerAuth.
An administrator creates authentication domain definitions while creating a user definition with the User Manager in SAS Management Console. The authentication domain is associated with one or more login metadata objects that provide access to the server and is resolved by the DBMS engine calling the SAS Metadata Server and returning the authentication credentials.
The authentication domain and the associated login definition must be stored in a metadata repository and the metadata server must be running in order to resolve the metadata object specification.
For complete information about creating and using authentication domains, see the credential management topic in SAS Intelligence Platform: Security Administration Guide.
It does require SAS Metadata Server is licensed
You could also use the Metadata Server to create the libraries themselves and then assign users priviledge to access instead of AUTHDOMAIN=
Have a look at the function pwencode:
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002595988.htm
Basically you use that procedure to create a text string which is that password encrypted. In your programs you can then use the encrypted string rather than the password.
I looked at this method a while back, but I had some concerns.
If I use an encrypted string rather than the password would that allow other users to extract data from the database if they run my code?
Could other users access the database using the encrypted string?
Yes, of course. If anyone gets your logon details, then they will be able to login to the database. It matters not what storage you have them as. The idea with this system is to prevent passwords appearing in logs and output where the information could be extracted from. Your details you have to keep yourself. Now if your writing code for generic use, then what you would do is have your program point to a file with the encrypted passowrd in it. When you release your code to production you wouldn't copy your login file with it, that is personal to you. Any further users who want to run the code, would have to supply their own encrypted string in a file, so your code could look like:
%macro ConnectToDB (password_file=);
Then if you run it you would run it and put your file in, other users would add theirs. Its of course, dependant on the purpose of the code, the setup at your company, and the method in which this would be used. Maybe your users have thier own working area, then the code can just look for the password file in local area, and that file would be different for each user.
To repeat, the pwencode doesn't prevent other people using your details, it just prevents others seeing it from logs and such like.
I see no reason not to use pwencode if you only have one SAS user and it would preclude anyone using any other tool from using the password.
That being said, it's not overkill to use the SAS Metadata Servers natural roles to maintain libraries and credentials, but if you are not familiar with the tool and neither is anyone at your company, it would be the more difficult way to go about it...
Chris H has a nice blog post with a quick review of different options:
If only there was a place safe enough for our data managers here. 🙂
Store your SAS codes in a directory only YOU have read and execute permissions on. Then only the superuser can read the codes (and the superuser can do anything, so don't worry).
proc pwencode is not really safe, as anybody else can use the encoded password, and the encoding process is of course reversible.
The best option would be to use public/private keypairs like you can do in SSH, if possible.
Which database is it? Which version of SAS are you running and on which platform? If the combination supports Integrated Windows Authentication (IWA) you may be able to abandon the use of a password in favour of IWA.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.