SQL Server Audit
Learn auditing methods to identify key database security concernsAuditing SQL Server
SQL Server provides robust capabilities to monitor itself, and it can be easy to be overwhelmed with the choices presented through a catalog or dynamic management views, extended events, server-side traces (for older versions of SQL Server), or performance counters. That is true on the security side as well. This whitepaper presents the top five items you should be auditing on all of your SQL Servers, and how to do them. Keeping an eye on these items will help you verify database security and access to your environment. They are:
- Who has administrative access to SQL Server?
- Who has been granted CONTROL and IMPERSONATE permissions?
- Who are the database owners?
- What are the respective database permissions?
- What failed logins are you getting on your SQL Servers?
SQL Server SysAdmin
As a general rule, the system administrator account should never be used. It is just like the Administrator account at the operating system level. The best practice for Windows is to rename and disable the Administrator account. While it is possible to discover the renamed account, renaming the account protects against scripts and attempts which target the old name. As a second step, if we disable system administrator, it cannot be used to connect to SQL Server.
Auditing SysAdmin and SecurityAdmin
The sysadmin role has been on the audit list because a member of that role can do anything within SQL Server. However, securityadmin should be added because a member of that role has the potential to create a login with equivalent rights as a member of the sysadmin role. Before SQL Server 2005, this was not possible. Some guidance still reflects how this role functioned in SQL Server 2000 and older.
Starting with SQL Server 2005 to the latest version of SQL Server 2019, securityadmin gained the ability to grant the CONTROL permission at the server (meaning SQL Server) level, which is equivalent to what a member of the sysadmin server role can do. We will get to auditing for CONTROL permissions next. Concerning auditing for the two server roles, there are two ways to proceed.
sp_helpsrvrolemember
A system stored procedure, sp_helpsrvrolemember, is available that will report the members of a fixed server role. For the fixed server roles, the ones that come with Microsoft SQL Server, this method works fine. However, this system-stored procedure does not report on user-defined server roles, which were available in the latest versions of SQL Server. Therefore, there is a second way to get role membership.
Using Catalog Views:
There are two catalog views we will need to use to get the same information as with sp_helpsrvrolemember. They are:
- sys.server_principals
- sys.server_role_members
SQL Server Control Permission
SQL Server does not just provide the ability to grant administrative rights through roles. It also provides the ability to assign permissions against what are called securables. One of those securables is Server, which corresponds to the SQL Server as a whole. Other securables include traditional objects like tables, views, and stored procedures. SQL Server also considers logins and users as securables. The complete list can be found in the documentation of SQL Server, Books Online. However, for now, we are primarily concerned with four securables in particular: Server, Database, Login, and User.
For the first two securables, Server and Database, we are concerned with when the CONTROL permission has been granted. CONTROL gives complete control over the securables. Some securables are also called scopes. Scopes are simply securables that can contain other securables, which leads to a hierarchy much like a Windows folder can contain subfolders and files. The Server securables is a scope, and it contains Databases and Logins as well as other securables. Databases are also scopes and contain Users, Schemas, and other database-level objects. There is one more scope, the Schema, which is what contains the traditional objects like tables, views, stored procedures, and functions. Schemas themselves are within the Database scope.
What is important to note about scopes is that when permission is granted to a scope, it carries down to everything contained by that scope, following the hierarchy. If a login has CONTROL permission against Server, that means it has CONTROL permission over everything in SQL Server. Having CONTROL against a Database means having CONTROL over everything contained in the database. That is why auditing for CONTROL is important.
SQL Server Impersonate
If a login does not have permission to do something, but if it can impersonate someone who does, then the login effectively has the same permissions as the impersonated login. That is also true at the database level with users. Therefore, auditing for IMPERSONATE is important, especially on logins and users with elevated privileges such as system administrator or database owner. Here is how to do so at the server level with the second name being the login that can be impersonated:
There are legitimate uses for IMPERSONATE, which is why it is provided as permission within SQL Server. Therefore, if you encounter it on any of your servers, verify that the occurrence is valid.
SQL Server db_owner
Ownership implies control, and that is undoubtedly true with SQL Server. With regard to database ownership, we are concerned with two things:
- The actual owner of the database
- Members of the db_owner fixed database role
If a login owns a database, it maps into the database as a database owner (dbo) by default. So database administrator members of the sysadmin role, by the way. The reason this is important is that the database owner user bypasses security checks. Whoever owns a database can do anything he or she wants within it. Therefore, auditing for database owners is essential. The following query may result in the Owner showing as NULL. That can happen if a login owned a database, but the login was subsequently dropped from SQL Server, and the database ownership was never transferred. Any NULL listings should be updated appropriately.
SQL Server Fixed Database Roles
We are also concerned with members of the db_owner role within a database. Members of the db_owner role, unless explicitly blocked by a DENY, also can do anything within the database. That is different from database owner (dbo), for which the DENY will not apply. Although a DENY can block a member of db_owner, that user does have the ability to revoke the DENY, thereby gaining access. As a result, this level of permission should be audited, too. Once again, we have two methods, similar to what we had with the server roles.
sp_helprolemember:
There is a system stored procedure at the database level called sp_helprolemember, and it functions as the server level sp_helpsrvrolemember. Therefore, we just need to specify the fixed database role, db_owner, to audit:
SQL Server catalog views:
The other method is to use catalog views. Again, the view structure at the database level mirrors the server level catalog views.
SQL Server Database Permissions
Applications use databases, and naturally, we are concerned about database permissions. There are many catalog views around database objects, all of which are necessary to get a complete view of permissions within the database. Let us look at the most typical securables.
Database Securables
We have already covered these securables concerning CONTROL permission, but it is important to see all the permissions at the database level. For instance, the CREATE TABLE permission is at the database level. Keep in mind that this permission also grants the ability to ALTER and DROP tables as well. Therefore, here is how to audit everything at the database level:
Schema Securables
Because a schema is what contains tables, views, stored procedures, and more, it is important to query the schema permissions as well because remember that SQL Server treats the securables scopes as a hierarchy. Therefore, if I have SELECT permission on a scope, I have permission for all objects within that hierarchy. That carries into other scopes as well. Therefore, if a user has SELECT at the database level, it has SELECT on every table and view in the database. If it has to EXECUTE at the schema level, it has to EXECUTE on every stored procedure within the schema. Here is how to audit the permissions:
Object Securables
A user can have permissions directly on tables, views, and other objects. I am not accounting for permissions specifically against columns, but the main object. So if a user has permissions against specific columns in a table or view, this query will not show them. You will have to go an additional join deep, joining to sys. columns and use the major_id to identify the object and the minor_id to identify the specific column in your ON syntax.
SQL Server Failed Logins
There are two reasons to audit for failed logins:
- To detect when someone is trying to get into SQL Server improperly.
- To detect when a person or application with a legitimate reason to access SQL Server cannot connect.
It is not usual to find that the second reason is the most prominent one for auditing for failed logins. When an application is not working, the failed login will typically pinpoint what is wrong. For instance, if someone mistyped a password that the application will use to connect if you are auditing for failed logins, you will see the failed login and the reason (for example, a bad password). The lack of a failed login when you are auditing for failed logins is telling as well. For instance, if you do not see a failed login and the application is failing to connect, this could point to the connection string having the wrong server, the firewall on one or both servers interfering, or some network issue preventing the communication from occurring. Therefore, auditing for failed logins is essential not just for security, but as a good operational practice.
Audit Failed Logins
To configure an audit for failed logins, right-click on the server in SQL Server Management Studio (SSMS) and choose Properties from the pop-up menu. Then click on the Security page. Look for this part of the dialog window and ensure that Failed logins only are marked. If you have a reason to do so, you can mark the entry for Both failed and successful logins but realize that this will put many entries into the SQL Server log and the Application event log if you have a busy SQL Server.
SQL Server Failed Login Logs
If you are auditing for failed logins, open up your SQL Server log and look for entries with the Source of Logon. If you are using SSMS, you can even choose to Filter on this source.
SQL Server Management Studio Login Failed for User 18456
When SQL Server is auditing for logins, it will also write events to the OS Application event log. There are often many events in the Application event log, so the best way to look for these events is to filter the log. If you are in the Event Viewer or another tool (such as Computer Management), which gives you access to the event logs, drill down until you see Application under Windows Logs. Right-click on Application and choose Filter Current Log. In the dialog window, enter the event ID, such as 18456, like so, which will filter the Application log for just those events. We could specify a source, but it will be different depending on if you have a named instance or not.
If your organization has a security information and event management tool, and it is taking events from the Application event log, it can pick up the failed logins. That would help correlate a path of an adversary in the event of a real security incident. That is another reason to audit failed logins.
SQL Server Agent Jobs
We have intentionally included the scripts and information you need to audit the top five audit concerns we have listed. While you can collect all of it manually, it is best if you are collecting this information through automation, such as via scheduled tasks or SQL Server Agent jobs. If you can also process the information gathered automatically, that is even better. Keep in mind that there are third-party applications that do the grunt work for you, including reporting and alerting.
Among them are tools of IDERA like SQL Compliance Manager, SQL Secure, and a free tool, SQL Permissions Extractor. They are worth looking into to reduce the amount of time you have to spend auditing the top five items we have presented here.
SQL Compliance Manager
Improve Any SQL Server Audit
- Audit Sensitive Data - see who did what, when, where, and how
- Track and Detect - monitor and alert on suspicious activity
- Satisfy Audits - for PCI, HIPAA, FERPA and SOX requirements
- Generate Reports - 25 built-in reports to validate SQL Server audit trails
- Minimize Overhead - lightweight data collection agent minimizes server impact