SQLTeam.com | Weblogs | Forums

A request by the Auditors to see list of users that connected

Hi experts,

For our SOX audit, this has been requested:
"we need a listing of all users that gained direct access to the database from the period of 03/01/2022-07/25/2022"
Because the instance has only logged FAILED logins, I don't see a way of providing this list. Am I correct?
Thanks.

"Users that gained direct access to the database"? Do they have direct access to the database?

First we have to know what they mean by "direct" access. I assume they mean any users that are not accessing from the application. Any ideas?
Maybe users that have the db_owner role or sysadmin? But they seem to want me to pull a list of everyone who connected during a certain date range. I need to get more info from the auditors.

You would have to ask them. Direct to me would be via SSMS or something. So you do have db owner users?

Do you have any auditing embedded into your application?

If you are not logging or auditing successful logins - then you cannot identify all users that logged into the system during that timeframe.

But - you can generate a list of all logins and the corresponding user in each database and when the login/user was created. That would provide you a list of all users that 'gained' access to the system in that specified timeframe, but not whether or not they actually accessed the system.

I would start with the list of logins/users for that specific database - that were created within that specified timeframe. If they ask for more than that then you can get more specific.

We had that same issue and so we had to move to using CyberArk so that we could have a method of capturing anyone having direct access to the production database. But you are correct, there is no way to provide what they want unless you use something like CyberArk. The way CyberArk works, is that you set up the database to have access only through a specified SQL account with a password. The only thing is, that CA will then rotate the password every time someone logs in to the database and the only way you can log in to the database is by accessing the CyberArk account, logging in as you (using your Windows credentials and being authenticated against Active Directory). So the website will give you the password for the SQL Account and you log in as it with the given password. CyberArk will then, after the designated time, change the password to that account so that you, or anyone else, would have to do that whole process again. And it has a spot so you have to specify why you are logging in to it when gathering the password. It can be set so you have to provide a specific change request in Service Now, etc. if using that.

1 Like