ADMIN monitor SAS Library Connection strings.

[gboggs]

 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.

[SASKiwi]

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.

[gboggs]

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.  

[SASKiwi]

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.

[gboggs]

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.  

[AnandVyas]

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.

[gboggs]

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.

[SASKiwi]

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

[JuanS_OCS]

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&docset...

https://documentation.sas.com/?docsetId=acreldb&docsetTarget=p1f9ovbl1ifskpn1e82nky8v5bbb.htm&docset...

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.

[gboggs]

That looks promising thanks for the tip.

[JuanS_OCS]

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