Database activity monitoring

[krishna_namilikonda]

hi SAP gurus,

We are implementing database activity monitoring in our landscape to monitor access to critical tables by users accessing from SAP ABAP system. We have two SAP environments one on Oracle database and one on MSSQL database. We are performing DAM by duplicating network traffic on the servers using agents. We are able to capture all the queries but we are having issues associating SAP users to the SQL statements executed by them.

We had success in Oracle environment by using OciAttrSet attribute which collects SAP user information and holds it while the query is being executed at database level.

I need help finding a similar or stable way of associating SAP users with the SQL statements they run on MSSQL database. Request you to help me find a solution to our problem.

Thanks,

Krishna Namilikonda

[krishna_namilikonda]

Thanks Luis. The way it works in Oracle environment is that SAP is populating the Ociattrset(cid_cid, SAPusername) with every SQL connection to SAP. OCI (Oracle call interface) is Oracle's API that is part of Oracle instant client.

I think SAP uses similar API on MSSQL database side like OLEDB or Tabular Data Stream (TDS) to connect from SAP environment to its database. We are wondering if a similar methodology is in place for MSSQL database where it allows us to capture the SAP user's stream and associate it with SAP user with the MSSQL API's.

We are capturing network traffic of SAP users communication with MSSQL database on port 1433  and not running queries to get information, using SAP ABAP program will pose a challenge. We will take that option if an easier method is not available.

Thanks,

Krishna Namilikonda

[krishna_namilikonda]

Thanks Sriram. I checked OSS note 1316740 which talks about setting up connection from dbacockpit. We are able to connect to SQL database and capture all the SQL traffic and so that note was not helpful.

Thanks Luis for the detailed explanation. I think the KB article 1645041 was useful in explaining as to how SAP work process connects to the database. Using ABAP code to log every access would be very complicated because we use a lot of SAP standard code and we have multiple tables which have secure data. Also we have security restrictions on what data can be viewed but we also need to monitor the access as per IRS requirements.

I am still confused as to how SAP while connecting from work process to the database is holding information of which SAP user is running that query. Is it done by holding the user information in the work process and once the result is returned from database it just returns the results to the users that is holding the work process?

Thanks again for taking time to help me.

Regards,

Krishna Namilikonda

[luisdarui]

Hi Krishna,

This happens in application level instead of DB level. The user is not running a query, the user requested to the application the information from the database, then the work process run the query (you can see this in SM66 sometimes) and returns the information to the user.

This is not the full picture, but I believe that you want to do is simply not possible this way, and I don't really know how it does work in Oracle to have a correlation.

Best Regards,

Luis Darui

[luisdarui]

Hi Krishna,

I don't know if this is possible because the way SAP connects to SQL Server. There are specific SQL logins that are used by the ABAP and JAVA stacks to connect to SQL Server, so it is likely that you won't be able to catch such information by external means, not only to say that SAP doesn't support such feature. See the SAP Note 592514 and the SAP KBA 1645041 for more information about them.

I won't say that Microsoft might have such information for you because since you're looking for a way to correlate the user logged into SAP to the table he is accessing, what you might need is some custom code in ABAP or JAVA to do it.

Since the access to tables in SAP NetWeaver systems are done by programs that are coded, I would suggest you to map those tables to the programs that use them and restrict the access or even customize such programs to log every time a user access them and write into those tables, but I can't tell it that it is feasible or help you to quantify the necessary workforce here.

Best Regards,

Luis Darui

[Sriram2009]

Hi Krishna.

Just refer the SAP Note for MS DB monitoring

1316740 - Set up remote monitoring for Microsoft SQL Server databases

Regards

SS