In November, I resumed the “it’s all about the data” series, laying a foundation for helping SAS administrators understand how SAS stores and manages data for use in business intelligence and analytic applications.

For this article, I culled our internal Thotwave knowledge base and queried our consultants who get questions every day on third-party database engines, especially as they relate to SAS metadata. We picked a few of the most common questions as a way to frame this discussion. Topics include how you can register your third-party database tables in SAS metadata so that users can consume them with SAS’  metadata-aware clients. Finally, there’s a brief discussion of security and various approaches for managing database credentials to these external databases.

Let’s get started!

For our database connections, my organization uses a shared account for users, what is best practice for storing these credentials?
If you have the SAS Metadata Server and use it to manage your environment, you’ve chosen the best way to go. Since database passwords can and do change, you definitely do not want them encoded in your SAS programs. Starting with SAS 9.2, you have the option of using the AUTHDOMAIN option when connecting to databases.  

What if we don’t use shared accounts, does every user have to set up an authorization domain?
If your organization requires each user to have their own individual userid and password to connect to sensitive data stored in third-party databases, you can either provide them directly in your program (libname or pass-thru connection) or store them in metadata. I touched on this in a "How to connect to SAS libraries when the client application isn't metadata-aware” and would definitely say that Chris Hemedinger’s article "Five strategies to eliminate passwords from your SAS programs" is a must-read. 

In the case of the metadata solution, you can simply have each user add them to their SAS profile. If the credentials are different than their SAS credentials, you can have each user set up an additional connection profile using either SAS Management Console or SAS Personal Login Manager.

How do I register database tables in SAS metadata?
Once a library has been defined in SAS metadata, you can register tables using the SAS Management Console or SAS Data Integration Studio. For some background on what it means to assign a library, take a look at one of my previous articles "Seeing SAS data through metadata" or the section on assigning libraries in the SAS documentation.

To register table metadata using SAS Management Console, take a look at this explanation of registering tables..

If I register my tables in SAS metadata, do I have to re-register them when the data changes?
This is a common question and the short answer is “no”. When you register data in the SAS Metadata Server, you are simply extracting the table- and column-level metadata from the RDBMS and making that available to SAS for consumption in clients like SAS Data Integration Studio. 

The underlying database tables have changed, how can I synchronize the SAS Metadata?
You have the option of “updating” the metadata using SAS management Console or you can do this programmatically using SAS code. The SAS Data Administration Guide has a good explanation of how this works. 

For those new SAS administrators, are there other questions that you have about accessing data from third-party relational database management systems?

Remember--Happy Data, Happy Users!

--greg