BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASWayne
Quartz | Level 8
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.
1 ACCEPTED SOLUTION

Accepted Solutions
JuanS_OCS
Amethyst | Level 16

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.

View solution in original post

5 REPLIES 5
SASKiwi
PROC Star

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 @AnandVyas 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
Amethyst | Level 16

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.

 

 

 

 

AnandVyas
Ammonite | Level 13

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
Quartz | Level 8

@AnandVyas 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
Amethyst | Level 16

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.

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

Get Started with SAS Information Catalog in SAS Viya

SAS technical trainer Erin Winters shows you how to explore assets, create new data discovery agents, schedule data discovery agents, and much more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1631 views
  • 12 likes
  • 4 in conversation