Detect, Prevent and Protect: How to Do an SQL Server Audit

Microsoft SQL Server is a widely used and powerful database management system that enables organizations to efficiently store and manage data. However, with the increasing amount of sensitive and confidential data stored in the database, ensuring its security, integrity, and compliance is of utmost importance. While SQL Server has built-in auditing features, conducting regular manual audits is crucial to supplement the built-in capabilities and ensure that the database remains secure and compliant with the best industry standards.

The purpose of SQL Server Audit

An efficient Microsoft SQL Server audit involves a comprehensive review of the database configuration, data access, and user permissions to identify potential vulnerabilities, security risks, and compliance issues. The database audit can also help identify any unauthorized changes to the database, unusual activity, or performance issues. It is a critical aspect of maintaining the security, integrity, and compliance of the entire database system, enabling organizations to identify potential vulnerabilities and security risks that might otherwise go unnoticed.

SQL Server Audit Components

Basically SQL Server auditing is based on three pillars: security audit, server audit, and database performance audit. This article contains examples from a specific SQL Server audit to demonstrate the real-life pain points and options to correct them. So let’s dive deep into the topic.

Security Audit of Microsoft SQL Server

Databases are the backbone of any organization that are used to store large quantities of data. The variety of data that are stored in databases depend on the area a company operates in. Anyway, regardless of the company’s business scope, any database is a storage of sensitive and personal data. Whether they contain information about customers, financial transactions or production processes, it is imperative that organizations take the necessary actions to safeguard these valuable assets and prevent unauthorized access to their contents. A data breach could have serious consequences, including identity theft, financial fraud, and other threats. In addition, it can cause significant damage to the company's reputation, as well as lead to legal trouble and fines for violating data protection legislation.

Regular vulnerability and threat SQL Server audits can help ensure high database resistance to security breaches. Proper database configuration, monitoring of security events, and database-level permissions are essential for this purpose. Let's take a look at each point in more detail.

1. Analyze SQL Server security settings. First, verify that the SQL Server is running the latest security patches and updates. They are crucial for maintaining database security because they address newly discovered vulnerabilities and weaknesses in the software. Cybercriminals are continuously seeking for new ways to take advantage of these flaws so they can access databases without authorization and steal private data. By regularly updating and patching the database software, organizations can stay ahead of these threats and protect their information from potential breaches. Second, make sure that your company adheres to a strong and secure password policy. Since a password is the first line of protection against unwanted access, it must be strong to ensure database security. Despite all the recommendations to generate complex passwords, many employees still create them easy to guess, putting the database at risk of a breach. Database administrators should definitely enforce strong password policy and require users to create strong passwords that meet certain complexity requirements. Using this method can help prevent the use of weak passwords and reduce the risk of unauthorized access to the database. In addition to using a strong password, it is important to follow good password hygiene practices, such as never reusing passwords across different accounts, changing passwords regularly, and using two-factor authentication wherever possible. Final step for security settings analysis is disabling or removing any unnecessary logins, users, or roles to prevent unauthorized access to sensitive files.

2. Review the server and database-level permissions. To begin with, analyze all the permissions to verify that they are correct. This includes granting appropriate permissions to users, roles, and groups, and ensuring that no user has unnecessary permissions. Also, check for redundant permissions and remove them to reduce the risk of third-party access. Verify that any sensitive information is protected with appropriate security measures, such as encryption or data masking.

3. Enable SQL Server auditing and monitoring. Enabling auditing and monitoring is essential to track critical security events, such as successful and failed login attempts, changes to server and database objects, modification of user permissions, and access to sensitive data. Tracking of these events can help to detect suspicious activity, identify potential vulnerabilities, and prevent unauthorized access. Furthermore, administrators should create alerts when critical events happen, such as a user accessing sensitive database objects.
In addition, many regulations and standards, such as HIPAA, PCI DSS, and GDPR, require enterprises to track and monitor access to sensitive files and report any unauthorized access or data breaches. That is why besides detecting security threats, event monitoring can also help organizations meet regulatory requirements and best industry standards.

We often discover a plethora of problems when analyzing DB user privileges. In our example database, we found that almost all users had the rights of db_owner. Such an approach does not comply with any generally accepted database security policies. Only one user should have db_owner rights. This is critically important since any of these users has the right to unlimited access to the database, up to its removal.

Besides, we found that some of the users with db_owner privileges also had personal privileges. It is not recommended to give individual users personal privileges since it reduces the protection of the database from unauthorized access and modification of data and schema.

Server Audit

Audits are essential for ensuring that servers are secure, optimized, and configured according to the best industry practices. However, before we embark on the actions required to inspect the database server, we need to figure out what a server audit is.

In order to audit a server you should thoroughly examine the policy, procedures, and resources allocated to a server. This review encompasses all activities on the server level, such as user logins, system changes, file access auditing, and other important events. The main goal of a server audit is to ensure that the server is secure, compliant, and performing at its best.

To achieve this, system administrators analyze the server audit logs to identify any security vulnerabilities, troubleshoot problems, and ensure that the server adheres to industry-specific compliance requirements. By thoroughly reviewing the server audit logs, database administrators can detect any unauthorized access attempts or other suspicious activities, and take steps to remedy them.

By conducting a server audit, you can identify any potential issues and address them before they cause significant problems. A server audit involves a comprehensive review of server configurations, network review, and resource allocation.

1. Server Configuration Review. The primary objective of server configuration review is to verify that the server has the latest security patches and updates installed. This helps to ensure that the server is better equipped to mitigate attacks and is less vulnerable to security breaches. Moreover, configuring SSL encryption is essential to ensure the information is encrypted when transmitted. Enabling strong password policies helps to ensure that passwords are complex and changed regularly. Lastly, disabling any unnecessary services or features reduces the attack surface, making the server more secure.

2. Network review. In an ever-evolving threat landscape, it is imperative to audit the network segment that provides users with access to the server. Cybercriminals are constantly developing new attack methods to circumvent traditional security measures, and it's essential that organizations stay ahead of the curve by conducting regular network audits.
Thorough network audit includes the analysis of the security measures that are in place to protect the network, including firewalls, intrusion detection systems, access controls, and other security protocols. It is no less important to check the wireless network security, such as encryption and authentication methods, to ensure they are strong enough to fend off any unauthorized access to the database information or critical files.
Alongside the security measures, reviewing network policy and procedures is a crucial aspect of a network audit. This involves checking the access controls, password policies, and network segmentation to ensure they are effective and being followed.
In fact, poorly optimized networks can also impact the performance and stability of the database, leading to slow query execution times, increased downtime, and reduced productivity. These issues can have a direct impact on the bottom line of the company, resulting in lost revenue, increased operational costs, and decreased customer satisfaction.
To analyze the network, the first step is to identify all the network components that are connected to the server. All network devices, including switches, routers, firewalls, load balancers, and others, fall into this category. Next, review the network topology to understand how data flows through the network and identify potential vulnerabilities or bottlenecks.

3. Resource Allocation Review. Efficient server operation is usually based on the right resource allocation. In this regard, we highly recommend monitoring server performance to identify any bottlenecks or performance issues. Monitoring disk I/O, memory usage, and CPU utilization ensures that there is no resource contention. It is also extremely important to analyze the server hardware specifications to make sure that they match the workload.
Here is an example of the RAM load level that was measured during SQL Server audit.

As shown on the screenshot, the RAM load is at an acceptable level (about 80 percent) during the entire observation period. However, during the server overloads operational memory can be in short supply. To eliminate possible trouble, we recommend increasing the amount of RAM by 40-50%. This will provide a 50% RAM reserve, which is a generally accepted recommendation for the smooth database operation.

It is worth paying attention to the amount of free space on the hard disk. The amount of free space on your hard disk can have implications for the server overall stability and reliability. During SQL Server auditing, we observed that the hard disk in question has very little space left, which can be a cause for concern under heavy load and when performing multiple write operations. It is highly recommended to add free space on the disc where the database is stored.

Database Audit

Database audit is a critical process that helps organizations ensure that their databases are operating optimally. It involves reviewing performance of a database to identify any issues or areas where improvements can be made.

One key reason why database audit should be at the center of your attention is optimization. When a database is optimized, it performs faster and more efficiently. This not only improves the user experience but also enables organizations to handle larger volumes of information and scale their operations more effectively.

Database performance analysis can also help identify issues with database design or configuration that may be affecting performance. Microsoft SQL Server design optimization involves reviewing the schema, tables, indexes, and other database objects to ensure that they are optimized for performance and scalability.

Using indexes allows a database to quickly locate data and speed up query execution. Therefore, it is essential to ensure that indexes are optimized for the workload and are not causing performance issues.

The query execution is another significant issue that can impact database performance. It involves analyzing the execution plans of database queries to identify performance issues such as slow-running queries, missing indexes, or inefficient queries. By identifying these problems, you can optimize the query execution speed and bring the overall database performance to a new level.

Let's refer to our example database. The graph below shows the statistics of accesses to tables (all queries, including the execution of business logic objects)

The chart contains the top candidate tables for memory optimization based on the access patterns of the database workload. The horizontal axis represents decreasing effort of memory optimization, while the vertical axis represents increasing benefits of memory optimization in a company’s workload. The tables at the top right corner should be prioritized first for memory optimization.

Based on the graph, it is clear that optimizing the menuclassitemstable and domain_values tables is necessary. Updating and reorganizing the indexes is imperative. Additionally, adding partitions to speed up work would make sense. It may also be worth looking at other tables in the graph. Reorganizing these tables will have the greatest impact on improving performance, as most of the calls are made to these tables.

Conclusion

So, if you want to maintain your database health, monitoring database performance, identifying vulnerabilities, and maintaining regular SQL Server audits should be your number one priority. Database audits are essential for maintaining a full record of all actions conducted, and they should be performed accurately and thoroughly across all platforms and systems. You can audit SQL Server databases yourself or refer to our team for professional database audit service. Our extensive experience ensures top-notch auditing services and high quality in-depth analysis of your database.