DATA Step, Macro, Functions and more

Database Passwords - keeping passwords private

Reply
Occasional Contributor
Posts: 5

Database Passwords - keeping passwords private

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?
 
Occasional Contributor
Posts: 5

Database Passwords - keeping passwords private

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?

Trusted Advisor
Posts: 1,300

Re: Database Passwords - keeping passwords private

[ Edited ]

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=

Super User
Super User
Posts: 7,417

Re: Database Passwords - keeping passwords private

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. 

Occasional Contributor
Posts: 5

Re: Database Passwords - keeping passwords private

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?  

Super User
Super User
Posts: 7,417

Re: Database Passwords - keeping passwords private

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.

Super User
Posts: 5,260

Re: Database Passwords - keeping passwords private

The best is to use the SAS Metadata Server. Store the passwords in the meta data and assign the privelege to use it by user/group authorization. The results is password free programs.
Data never sleeps
Occasional Contributor
Posts: 5

Re: Database Passwords - keeping passwords private

Is this method overkill for only one SAS user? The other users for this database are using other tools.
Trusted Advisor
Posts: 1,300

Re: Database Passwords - keeping passwords private

[ Edited ]

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

PROC Star
Posts: 1,236

Re: Database Passwords - keeping passwords private

Chris H has a nice blog post with a quick review of different options:

http://blogs.sas.com/content/sasdummy/2010/11/23/five-strategies-to-eliminate-passwords-from-your-sa...

Super User
Posts: 5,260

Re: Database Passwords - keeping passwords private

One user? For this sole purpose? Yes, just keep your SAS programs in a safe place.
Data never sleeps
Occasional Contributor
Posts: 5

Re: Database Passwords - keeping passwords private

If only there was a place safe enough for our data managers here. :-)

Super User
Posts: 6,964

Re: Database Passwords - keeping passwords private

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 392

Re: Database Passwords - keeping passwords private

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. 

Ask a Question
Discussion stats
  • 13 replies
  • 453 views
  • 2 likes
  • 7 in conversation