BookmarkSubscribeRSS Feed
AngelaM100
Fluorite | Level 6
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?
 
13 REPLIES 13
AngelaM100
Fluorite | Level 6

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?

FriedEgg
SAS Employee

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=

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

AngelaM100
Fluorite | Level 6

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?  

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

LinusH
Tourmaline | Level 20
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
AngelaM100
Fluorite | Level 6
Is this method overkill for only one SAS user? The other users for this database are using other tools.
FriedEgg
SAS Employee

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

Quentin
Super User

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

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
LinusH
Tourmaline | Level 20
One user? For this sole purpose? Yes, just keep your SAS programs in a safe place.
Data never sleeps
AngelaM100
Fluorite | Level 6

If only there was a place safe enough for our data managers here. 🙂

Kurt_Bremser
Super User

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.

PaulHomes
Rhodochrosite | Level 12

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 2067 views
  • 2 likes
  • 7 in conversation