How to Do a Thorough SQL Server Audit

Microsoft SQL Server is a popular and strong database system that helps organizations store and handle data effectively. As more sensitive data is stored in the database, it is crucial to prioritize security, integrity, and compliance. Regular manual audits are necessary to supplement SQL Server's auditing features. These audits help ensure the security and compliance of the database in accordance with industry standards.

The purpose of SQL Server Audit

A thorough SQL Server audit examines the database setup, data access, and user permissions. It aims to identify weaknesses, security risks, and compliance problems. The database audit can also help identify any unauthorized changes to the database, unusual activity, or performance issues. It is important to keep the database system secure and compliant. It enables organizations to identify and tackle potential vulnerabilities and security risks that they may overlook.

SQL Server Audit Components

Basically, SQL Server auditing relies on three pillars: security audit, server audit, and database performance audit. This article shows examples from a SQL Server audit to explain common problems and how to fix them. So let’s dive deep into the topic.

Security Audit of Microsoft SQL Server

Databases serve as the backbone of any organization, storing large quantities of data. The variety of data stored in databases depends 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. Organizations must protect important information and prevent unauthorized access to it, whether it's about customers, finances, or production processes. A data breach could have serious consequences, including identity theft, financial fraud, and other threats. It can harm the company's reputation, lead to legal trouble, and result in fines for breaking data protection laws.

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. Hackers always look for new ways to exploit weaknesses and illegally access databases to steal personal information. By updating and patching database software, organizations can protect their information from potential breaches and stay ahead of threats.

Second, make sure that your company adheres to a strong and secure password policy. A strong password is important for protecting against unauthorized access and ensuring database security. Despite advice to use strong passwords, many employees still choose easy ones, risking a breach of the database.

Database admins must enforce strong password rules and make users create complex passwords that meet specific requirements. Using this method can help prevent the use of weak passwords and reduce the risk of unauthorized access to the database. Besides having a strong password, it's crucial to practice good password hygiene. Don't use the same password for different accounts, change passwords often, and use two-factor authentication when 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. Ensure that you protect any sensitive information 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.
Companies must keep track of who accesses important files and report any unauthorized access or data breaches. Many rules and requirements, such as HIPAA, PCI DSS, and GDPR, require this. Event monitoring helps organizations meet regulations and industry standards, as well as detect security threats.

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 holds great importance as all users can access the database without any limits until they delete it.

Besides, we found that some of the users with db_owner privileges also had personal privileges. Don't give users personal privileges. It weakens database protection against unauthorized access and changes to data and structure.

Server Audit

Audits are essential for ensuring that servers are secure, optimized, and configured according to the best industry practices. But first, we must understand what a server audit is before we can begin inspecting the database server.

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

System administrators analyze server audit logs to find security vulnerabilities, fix issues, and ensure server meets industry compliance requirements. By carefully checking server logs, database admins can find unauthorized access attempts or suspicious activities and fix 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 server configuration review aims to ensure the server has the latest security patches and updates. This helps to ensure that the server is better equipped to mitigate attacks and is less vulnerable to security breaches. Moreover, you must configure SSL encryption to ensure that the information encrypts 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. Checking the network segment that lets users access the server is important because of the changing threat landscape. Criminals create new ways to attack online, so organizations must regularly check their networks to stay safe.
A network audit involves examining security measures used to protect the network. These measures include firewalls, intrusion detection systems, access controls, and other security protocols. Checking the wireless network security is crucial. Make sure encryption and authentication methods are strong to prevent unauthorized access to database information or important files.
Alongside the security measures, reviewing network policy and procedures is a crucial aspect of a network audit. To ensure effectiveness and adherence, we check the access controls, password policies, and network segmentation.
Badly optimized networks can affect database performance and stability, causing slow queries, more downtime, and less 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, first find all connected components 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.

The RAM load is okay (around 80%) in the whole observation time, as seen in the screenshot. 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. While auditing SQL Server, we noticed that the hard disk is almost full. This could pose a problem when there is a high level of activity and many write operations are occurring. We strongly suggest that you 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. Optimizing a database makes it perform faster and more efficiently. This improves user experience and helps organizations handle more information and scale operations effectively.

Database performance analysis can also help identify issues with database design or configuration that may be affecting performance. To improve Microsoft SQL Server's design, review its structure, tables, indexes, and other database components. This ensures that they are functioning properly and capable of handling growth.

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 make queries faster and improve the database's overall performance.

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. Rearranging these tables will greatly improve performance because most calls make them.

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.