topic Re: ADMIN monitor SAS Library Connection strings. in Administration and Deployment

ADMIN monitor SAS Library Connection strings.

gboggs — Tue, 18 Aug 2020 18:23:42 GMT

I am looking to automate a test of connection strings to external Oracle data bases from SAS 9.4. I then want to interrogate the log to see if a message exist with the 'password expiring soon' note. Recently the DBA's put us on notice that all passwords need to expire. Is there a macro that will pull the server and userid so that I could then do a test connection?

TIAA.

Re: ADMIN monitor SAS Library Connection strings.

SASKiwi — Tue, 18 Aug 2020 21:48:35 GMT

What log are you referring to? Please bear in mind that SAS users' logs are not captured anywhere by default if they are using Enterprise Guide or SAS Studio. SAS batch job logs are collected in designated folders as individual files. Potentially you would have to search through each of these files to find connection strings.

Re: ADMIN monitor SAS Library Connection strings.

gboggs — Wed, 19 Aug 2020 17:46:35 GMT

I am making a couple of leaps of logic. When doing a metadata update to an oracle connection we noticed that the message 'pw about to expire' (or an equivalents) was generated in the log of the metadata update. This made me realize two things. 1. We do not have any method to identify when a PW is about to expire. 2. If this message can be trapped in the log then it may be possible for me to create a job that outputs a log that can then be in interrogated for the proper text. I tried using proc metalib in an enterprise guide program but did not see the connection string in the generated log.

Re: ADMIN monitor SAS Library Connection strings.

SASKiwi — Wed, 19 Aug 2020 20:26:15 GMT

So where do you define your Oracle library connections? In SAS metadata or in coded LIBNAME statements or both? With coded LIBNAME statements I would think it would be the responsibility of the users running it to sort out issues like expired passwords.

Where I work pretty much all database connections use OS authentication which avoids your problem completely.

Re: ADMIN monitor SAS Library Connection strings.

gboggs — Tue, 08 Sep 2020 15:30:44 GMT

It sounds like you and I live in very different environments. In my world the SAS Admin is responsible for all connections and each connection is required by our data security team to have a unique userid. Those userids have (over the years) variable expiration dates varying from 90 days to one year to never. It would be nice to systemically review the expiration dates and then be in front of any potential expiration of the pw.

Re: ADMIN monitor SAS Library Connection strings.

AnandVyas — Tue, 08 Sep 2020 15:36:55 GMT

I think it's easy to get this information from DBA? I think you should work with DBA for a script/code that can fetch this information from the db and share it to SAS Admin.

Re: ADMIN monitor SAS Library Connection strings.

gboggs — Tue, 08 Sep 2020 15:53:18 GMT

Unfortunataly the DBAs do not see that as an option. With over 100 unique oracle connections they see that as my job. I'll keep digging.

Re: ADMIN monitor SAS Library Connection strings.

SASKiwi — Wed, 09 Sep 2020 04:56:37 GMT

@gboggs - Would your DBAs be prepared to allow you to query the appropriate Oracle system table to check for account expiry dates from a list of accounts you can glean from your metadata database connections? This would be a whole lot easier than looking through SAS logs. If they are not prepared to get off their chuffs to help can they at least give you the access to do it yourself?

Re: ADMIN monitor SAS Library Connection strings.

JuanS_OCS — Wed, 09 Sep 2020 09:27:12 GMT

Hello @gboggs,

I never tried to try to capture such information, but I consider it should be possible with STRACE. What I do not know, is if it is possible to restrict to just DB authentication messages. You could give it a try:

https://documentation.sas.com/?docsetId=acreldb&docsetTarget=n0732u1mr57ycrn1urf24gzo38sc.htm&docsetVersion=9.4&locale=en

https://documentation.sas.com/?docsetId=acreldb&docsetTarget=p1f9ovbl1ifskpn1e82nky8v5bbb.htm&docsetVersion=9.4&locale=en

I am wondering: the users to connect to the databases, are they local database users, or are they Active Directory/LDAP users? If they are of the second type, it could be much easier for you to just query AD or LDAP for the user attributes and get your logic in place. And if this is the case, you can script it very easily with your favourite ldap query client. SAS has some LDAP macros but I am not sure if they retrieve the password expiration dates.

Re: ADMIN monitor SAS Library Connection strings.

gboggs — Thu, 17 Sep 2020 17:44:10 GMT

That looks promising thanks for the tip.

Re: ADMIN monitor SAS Library Connection strings.

JuanS_OCS — Fri, 18 Sep 2020 15:44:42 GMT

@gboggs let us know how it goes and the approach you took in the end 🙂 Curious about it