Looking for: Best Practice for Development of Explicit Pass-Through SQL

[Patrick]

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

[Tom]

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]

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.

[ErikLund_Jensen]

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]

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]

@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]

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