DBPROMPT for SAS Studio

[KalebF]

Hello,

My organization mostly uses the old school SAS Windowing Environment for running programs. A lot of these programs that connect to ORACLE DB have a prompt window that allows the user to type in the name and password and then after successfully entered, the program runs.

Is there a solution for SAS Studio to display a window prompt so a user can type in their username / pw to connect to Oracle?

DBPrompt=yes;  works for SAS Windowing Environment.

DBPrompt=yes; doesn't work for SAS Studio.

Is there a solution for SAS Studio to also display a window prompt for username and password?

Thanks for any input!

Kaleb

[sbxkoenk]

Hello,

Are you using SAS Studio to submit code on a SAS 9.4 server?
Or are you using SAS Studio to submit code on a SAS Viya server?

The DBPROMPT= LIBNAME Statement Option
is not applicable to SAS Viya.

Koen

[KalebF]

We are using SAS Studio 9.4 server.

[Patrick]

The way I've seen that done normally:

• DB access is via a functional user
• The credentials are stored in a SAS Authdomain (SAS Metadata)
• Users use the authdomain instead of the userid/pw in the libname (if not pre-assigned)
• The SAS metadata security model needs to be such that only users in defined groups get read access to the authdomain

Normally DBA's prefer this approach as it removes the need for user management/sync with SAS on the DB side. 

If you really need individual user credentials with a Studio version where prompts are not available then one option would be for users to create environment variables with their credentials in their .profile.
In SAS code you then could use these environment variables instead of the SAS macro variables from the prompt. Or you could amend the autoexec and look for such environment variables (which will exist and have values set depending on the user who initiates the SAS session) and populates the macro variables as previously the prompt did.

....just giving you some ideas.

[KalebF]

Thank you for your post.
The macro variable idea works and what i'm using now. It seems like SAS studio 9.4 isn't able to prompt a user like SAS windowing environment 9.4. Rather, they'd have to enter it into a macro statement. I was hoping for a prompt approach so that when saving programs someone doesn't inadvertently save their passwords in the program.

[Patrick]

The most common approach is using an authdomain instead of explicit user credentials.

Using an authdomain in SAS 9.4 will also allow for an uncomplicated migration path to SAS Viya and Cloud.
https://blogs.sas.com/content/sgf/2017/08/17/sas-viya-sharing-credentials-for-database-access/ 

[Tom · Accepted solution]

You should be able to make a TASK in SAS/Studio that allows entry of information.  Just be careful as I am not sure if there are ways to make the password secure.

Here is a picture of an example:

[sbxkoenk]

Good idea from @Tom .

Here's how to do that:

• SAS® Studio 5.2: Developer’s Guide to Writing Custom Tasks
  • https://go.documentation.sas.com/api/docsets/webeditordg/5.2/content/webeditordg.pdf
  • https://go.documentation.sas.com/doc/en/webeditorcdc/5.2/webeditordg/titlepage.htm
• Paper CT13 (from phuse-connect)
  Next generation collaboration: using tasks and snippets in SAS Studio
  Fatima Kassim, OCS Life Sciences, ’s-Hertogenbosch, The Netherlands
  https://www.lexjansen.com/phuse/2019/ct/CT13.pdf

Tasks are based on the common task model (CTM), which consists of XML (metalanguage that allows for custom mark-up language) and Velocity Template Language (VTL or Apache Velocity language). Each task, SAS-supplied or custom, is based on such a CTM file.

By the way ... I also don't know about the security.

Something I tried out of curiosity --> the macro windowing environment (%window and %display).

Test Result was negative ... these do not work in SAS Studio!

Koen

[KalebF]

This is probably about as close to a solution to what I had in mind. But due to security issues, I most likely won't be able to implement it in good conscience. But thank you Tom and to all others who posted. I got some ideas.. and learned the limitations. Thanks!

[Nigel_Pain]

I don't know how well it would work but there's this from the SAS Github repository:

https://sassoftware.github.io/sas-studio-tasks/prompts/ 

I can't try it myself as our servers are blocked from going through our firewalls and proxies.