BookmarkSubscribeRSS Feed
Patrick
Opal | Level 21

Hi all,

 

I'm working in a project where I need to implement explicit pass-through SQL to take advantage of database functionality (like creation of a table with an identity column).

 

There is a SAS library defined in SAS metadata that provides all the required access to the database (full CRUD permissions).

 

To efficiently implement what I need, I would like to first use a DB client for development (=take SAS out of the picture) and only once I have fully working code integrate with SAS (=copy the DB SQL into a SAS SQL pass-through block).

 

I consider such a development approach as "best practice" and "how it is done".

 

To use a DB client I need the clear text password of the functional user (the one used in the SAS Metadata library definition), or then a named user with identical privileges.

 

There is some policy for the site I'm working on to not share credentials of functional users with developers.

 

I have a really hard time to explain to the SAS admin management why sharing the credentials "doesn't change a thing" in regards of security and doesn't provide me with any DB access I don't have already by using the SAS Metadata library definition.

 

I have my ways to get access to the required credentials so I can do my job but it puts me into a grey zone and it's also nothing I can share with other developers.

 

Do you know of any published SAS article, SAS press publication, global forum paper, etc. that could help me to make my case?

 

Thanks,
Patrick

6 REPLIES 6
Tom
Super User Tom
Super User

Let me try to translate to see if I understand what you are asking.

 

You want to use a non-SAS tool to create/test your SQL code.  The only way you can currently connect to the database in question is via a connection defined by the SAS/Database administrators in SAS.

 

What they security team is worried about not your access, but access by others to the password if you have a copy of it.

 

A normal work around is to setup a development or scratch environment that mimics the structure of the production environment and give you access to that environment to develop/test your code.  Then if the password is found out there is no risk to the production environment.

 

If they cannot do that then have them evaluate the tool you are using to access the database outside of SAS and figure out whether its password security measures meet their concerns.

 

Quentin
Super User

Agree with your approach, but also agree with Tom.  Basically your admin is saying "You are only allowed to connect to this database from SAS because I know how to manage your access via the SAS security model"  And you are saying "Please let me connect to the database from [some other SQL client]."   But the admin doesn't have a way they like to give you access from other SQL client without telling you the password.

 

I would share your frustration if my job was to access MS SQL server data from SAS and I was prohibited from using SQL Server Management Studio to look at the database and develop queries.  Can it really be the case that 'none' of the developers are allowed to use any SQL clients other than SAS?

 

I don't think a SGF paper would help you convince the admin.  It's not really a SAS question/issue.  If there is a data base admin separate from the SAS admin, they might be more helpful.

BASUG is hosting free webinars Next up: Mike Raithel presenting on validating data files on Wednesday July 17. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
ErikLund_Jensen
Rhodochrosite | Level 12

Hi @Patrick 

 

In our installation libnames with access to external databases has the password "hidden" in an AuthDomain", and we NEVER give anybody the user/password. It seems things are done in a similar way at your place.

 

We have some developers doing what you want, but the SAS admin management is not involved. If a developer needs access to a data base, he must negotiate with the administrator/owner of the database to get a personal access with the necessary privileges. He will not get the user/password used by SAS from the DB admin either.

 

Also, we in the SAS admin team are not involved in the installation and setup of a DB client for the user. There are supported clients for the autorized database systems at our site ready to install.

Patrick
Opal | Level 21

Thank you for your answers.

@Tom Yes, it's about getting direct DB access via a SQL client. And this is a Dev environment with masked data.

@Quentin What I would like is to find some paper that describes the two staged development approach.

@ErikLund_Jensen Authdomain or not one can always generate the libname statement with user credentials (SAS2 encoded pw) via SMC. Fair point that I would need to talk to a DBA. In my case it's the same department that support SAS and the DB.

 

 

SASKiwi
PROC Star

@Patrick - I'm lucky to work in a company where pretty much all database access is controlled via IWA (Integrated Windows Authentication). The benefit of this is there is no need for store database credentials anywhere so the problem goes away. Not an option where you are I assume?

Patrick
Opal | Level 21

@SASKiwi Nope, not an option. I have to live with what's there. 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 6 replies
  • 576 views
  • 0 likes
  • 5 in conversation