03-15-2016 12:00 PM
03-15-2016 11:56 AM
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.
03-15-2016 12:34 PM - edited 03-15-2016 12:36 PM
Use AUTHDOMAIN= Libname option
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=
03-15-2016 12:08 PM
Have a look at the function pwencode:
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.
03-15-2016 12:16 PM
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?
03-15-2016 12:23 PM
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.
03-15-2016 12:29 PM
03-15-2016 12:42 PM - edited 03-15-2016 12:45 PM
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...
03-15-2016 01:19 PM
Chris H has a nice blog post with a quick review of different options:
03-15-2016 06:48 PM
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.
03-15-2016 08:21 PM
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.