Find external DB connections in a SAS environment

[SASWayne]

I have an audit requirement to find external data sources connected to our SAS environment.

How to find the list external DB connections configured with our SAS environment?
I could see some DBs in the engines column in data library manager of SMC, but I'm not sure if that's all the data sources connected to SAS.

[SASKiwi]

So you've found the database connections that are defined automatically when SAS sessions are started. You should also check SAS autoexecs for automatic coded connections.

The problem is database connections can also be defined in code or other SAS apps by users. The only way to track these is to configure SAS to do enhanced logging of all SAS sessions and then trace the database connections in these. Not something I've attempted, but I know its a lot of work to set up and it creates additional overhead on your SAS servers. Experts like @Anand_V and @JuanS_OCS know all about this stuff and can explain it a lot better than me.

Could the cure be worse than the disease?!

[JuanS_OCS]

Hello @SASWayne ,

it is basically as @SASKiwi  correctly described 🙂

This is quite a broad topic, and requires good investigation. You can investigate the topic from different perspectives, and some of them can work with each other, not in an exclusive way. From the top of my mind:

- Enable the right logging in each of your SAS servers (included SAS foundation). But careful, this might fill in quickly your disks and every program might run from little to a lot slower.

- Monitor actively the network communications, as with WireShark.

- Crawl for your SAS Management Console and SAS code (not only autoexecs) for any libname or sql passthrough statement

- Check logs in your databases

- Check firewall logs

- Set up other expensive monitoring tools, such as DynaTrace

All in all, I believe it would be good if you align with your Chief Security Officers, as they can trigger support from other sysadmins for you, and best practices.

[Anand_V]

Just to clarify, Do you wish to find only the different data sources configured in SAS? Or you wish to list all the connections made from SAS to it?

If you wish to know the configured data sources only then the installation document should give you this information. If you don’t have the document you can check in SMC for the servers created for outbound access of data library.

If the requirement is to find the number of connections to your respective data source then I believe it’s easier the other way around wherein you can request this information from the data source admin. As clearly shared by @SASKiwi  and @JuanS_OCS  it’s quite an task at SAS end to enable debug logs etc.

[SASWayne]

@Anand_V I just wanted to list all the datasources configured in SAS. We don't have any installation documents for this.

Could you please help me with the steps to check in SMC for the servers created for outbound access of data library?

Many Thanks!

[JuanS_OCS · Accepted solution]

Hi @SASWayne , 

to list the metadata definitions you can use 

https://communities.sas.com/t5/General-SAS-Programming/How-do-i-list-all-libraries-and-libref-in-SAS...

https://seleritysas.com/blog/2017/08/28/data-step-view-of-libraries-in-sas-metadata/ 

 https://communities.sas.com/t5/Administration-and-Deployment/Listing-Metadata-libraries/td-p/359558

This paper might be interesting for you as well: https://www.lexjansen.com/phuse/2009/tu/TU06.pdf

For the ones defined in the code, I suggest a grep to that will go through all your sas code and generated logs files, capturing, with a string pattern, all the "libname" and "connect" statements, then eliminate duplicate entries.

Of course it will be a bit harder for code in SAS EG projects, unless those EG projects have the code in sas files.