6 Ways to Enhance Microsoft SQL Server Security

SQL Server plays a significant role in storing and managing sensitive information. However, with the growing threat of cyberattacks and data breaches, it’s crucial to implement security measures to ensure that your data remains secure.

It seems the news about system outages and data breaches is everywhere. In just the last few weeks, both Boost Mobile and its parent company Dish Network experienced headline-grabbing outages due to a cyber security breach lasting for several weeks. For Dish Network, not only were customers impacted but some employees were unable to do their jobs. While the financial and reputational impact of this breach is still being determined at of the time of this writing, Dish’s share price has already tumbled due in part to this “calamity.” Here is what Dish said about the incident on its website:

On February 27, we became aware that certain data was extracted from our IT systems as part of this incident. It’s possible the investigation will reveal that the extracted data includes personal information. The forensic investigation and assessment of the impact of this incident is ongoing. The security of our customers’ data is important to us, and if we learn that personal information was compromised, we’ll take the appropriate steps and let impacted customers know.

Microsoft SQL Server is one of the most secure platforms available, but companies need to deploy, configure, and implement it correctly – along with implementing its built-in security features – in order to ensure their systems are fully protected. Here, we’ll explore six of the most important security features and how to implement them to enhance your SQL Server security.

Method 1: SQL Server Hardening

Server hardening is the process of securing your SQL Server by reducing its attack surface and minimizing potential vulnerabilities. Here are some key steps you can take to harden your SQL Server:

• Apply security updates: Keep your SQL Server up to date with the latest security patches and updates. Check regularly for new updates and apply them as soon as possible to protect against known vulnerabilities.
• Disable unnecessary services and protocols: Disable any unnecessary services and protocols that could be used to gain unauthorized access to your SQL Server. This could include protocols like SMB, FTP, and Telnet, as well as services like SQL Server Browser and SQL Server Agent.
• Disable unnecessary features: Disable any features and configurations that are not used by the application or database. Example: Disable xp_cmdshell as this provides access through SQL Server to issue commands at the operating system.
• Limit service permissions: Limit SQL Server service permissions to only what is necessary for SQL Server to execute. Leverage a dedicated account for running SQL Server services and apply the service account through the SQL Server Configuration Manager as this will assign the necessary permissions to SQL Server.
• Use encryption: Use encryption to protect sensitive data both at rest and in transit. This can include using Transparent Data Encryption (TDE) to encrypt the entire database, or Transport Layer Security (TLS) to encrypt data in transit

Method 2: SQL Server Firewall

A firewall is an important component of SQL Server security, as it can help protect your database from unauthorized access and potential attacks. Here are some best practices for configuring the firewall for SQL Server:

• Enable the Windows Firewall: The Windows Firewall is a built-in firewall that can help protect your system from unauthorized access. Ensure that the Windows Firewall is enabled and configured to block all inbound connections by default.
• Configure the SQL Server port: By default, SQL Server listens on port 1433 for incoming connections. Consider changing the port number to a non-default value to make it more difficult for attackers to find and target your SQL Server.
• Limit inbound connections: Configure the firewall to only allow inbound connections from trusted IP addresses or networks. This can help protect your SQL Server from attacks originating from the internet.
• Use a network security group: If your SQL Server is hosted in the cloud, consider using a network security group (NSG) to control inbound and outbound traffic. An NSG can help restrict access to your SQL Server based on IP address ranges and protocols.
• Monitor firewall logs: Monitor firewall logs for suspicious activity and unauthorized access attempts. This can help you detect and respond to potential security breaches.

Method 3: SQL Server Backup and Disaster Recovery Strategy

Having a solid backup and disaster recovery strategy is crucial for SQL Server security. Here are some key steps you can take to ensure the security of your SQL Server backups and disaster recovery plans:

• Regular backups: Set up a regular backup schedule for your SQL Server database. This should include full backups, differential backups, and transaction log backups. Store your backups in a secure location, such as a separate server or a cloud-based storage solution.
• Backup verification: Verify your backups regularly to ensure they can be restored successfully. Test your restore process and verify that your backups contain all the necessary data.
• Secure backups: Encrypt your backups to protect them from unauthorized access. Use strong passwords and store them securely. Limit access to backups to authorized personnel only.
• Disaster Recovery (DR) plan: Develop a disaster recovery plan that outlines the steps you need to take in the event of a disaster, such as a server failure or data corruption. Your plan should include a timeline for restoring your data and getting your system back online.
• Testing and updates: Test your disaster recovery plan regularly to ensure it still works as intended. Keep your plan up to date with the latest security patches and updates.
• Off-site backups: Consider storing backups off-site to protect them from physical disasters such as fires or floods. This could include using a cloud-based storage solution or a remote backup site.

Method 4: SQL Server Auditing and Monitoring

SQL Server provides several features for auditing security-related events, including logins, role membership changes, and database object access. These auditing features can help you track activity on your SQL Server instances and identify security breaches or unauthorized access.

Here are some of the SQL Server security auditing features you can use:

• SQL Server Audit: SQL Server auditing is a built-in feature that allows you to create server-level or database-level audit specifications to track specific actions, such as failed logins or successful logins. You can choose which events to audit and where to store the audit data.
• SQL Server Profiler: SQL Server profiler is a tool that allows you to monitor and capture activity on your SQL Server instance, including login attempts, database object access, and other events. You can use this tool to identify potential security breaches or performance issues. If setup a profiler trace on a SQL Server, configure a server side trace versus a client side trace which uses the desktop application as it consumes additional resources.
• Extended Events: This is a lightweight event infrastructure that allows you to capture and store event data from SQL Server instances. You can use this feature to track security-related events, such as logins and user permissions changes.
• SQL Server Management Studio (SSMS) Activity Monitor: This is a graphical tool that allows you to monitor SQL Server instance activity, including active processes, locks, and sessions. You can use this tool to identify performance bottlenecks and potential security threats.
• Third-Party Monitoring Tools: There are several third-party monitoring tools available that provide more advanced security monitoring capabilities, such as real-time monitoring, threat detection, and user behavior analytics. WISdom which is Fortified’s new monitoring solution has many of these features along with real-time security findings which are actions for the DBA to better protect their SQL Server environment.

When monitoring SQL Server security, it is important to establish policies and procedures for reviewing audit data and responding to security incidents. Additionally, it is important to regularly review and update user permissions to ensure that only authorized users have access to sensitive data. Finally, it is recommended to regularly test and evaluate your security monitoring tools and procedures to ensure they are effective in detecting and responding to security threats.

Method 5: SQL Security Model

The SQL Server security model provides a hierarchical structure of security entities that control access to various database resources. The SQL Server security model includes two main types of entities: logins and users.

• Logins: Logins are used to authenticate users to SQL Server. A login can be created for a Windows user or group, a SQL Server user, or an external authentication provider, such as Active Directory or Azure AD. When a user attempts to connect to SQL Server, they provide their login credentials, and SQL Server verifies the credentials to grant or deny access.
• Users: Users are created within a specific database and are associated with a login. A user can be granted specific permissions on database objects, such as tables, views, and stored procedures. A user can also be assigned to a database role, which is a collection of permissions that can be granted to multiple users at once.

In addition to logins and users, SQL Server also supports the following security entities:

• Roles: A role is a collection of permissions that can be granted to multiple users or other roles. SQL Server provides several built-in roles at the server level and the database level, such as sysadmin and db_datareader, as well as the ability to create custom roles.
• Schemas: A schema is a named container for database objects, such as tables, views, and stored procedures. Schemas can be used to organize objects and control access to them. Permissions can be granted to individual users or roles on specific schemas.
• Certificates and keys: SQL Server supports the use of digital certificates and keys to encrypt data and control access to encrypted data. Certificates and keys can be stored within a database or at the operating system level.
• Permissions: SQL Server provides a granular permission system that allows for fine-grained control over database objects. Permissions can be granted at the database level, schema level, or object level, and can be granted to individual users or roles.

Method 6: SQL Server Encryption

SQL Server offers the ability to protect data in transit or at-rest which adds an additional layer of protection if there is an internal breach or an exposed backup file. Below are several options for encrypting data to protect it from unauthorized access, including:

• Transparent Data Encryption (TDE): TDE encrypts the entire database, including data files, log files, and backups. TDE uses a database encryption key (DEK), which is encrypted with a certificate stored on the server. TDE encryption is performed at the page level, and decryption occurs when data is read from disk.
• Cell-Level Encryption: Cell-level encryption allows you to encrypt individual columns or rows within a table. You can choose which columns or rows to encrypt and which encryption algorithm to use. Cell-level encryption requires additional application logic to manage keys and perform encryption and decryption.
• Always Encrypted: Always Encrypted allows you to encrypt specific columns in a table while still allowing search and filtering operations on the encrypted data. Always Encrypted supports two types of encryption: deterministic encryption, which allows for exact matches on encrypted data, and randomized encryption, which provides greater security but does not allow for exact matches.
• Backup Encryption: SQL Server provides the ability to encrypt database backups using a certificate or asymmetric key. Backup encryption can be performed on the backup file or during the backup process.
• Transport-Level Encryption: SQL Server supports encrypting data sent between the server and client using Secret Socket Layer (SSL) or TLS. Transport-level encryption can be configured using a server certificate and can be required or optional.

It is important to carefully backup and manage encryption keys and certificates to ensure that only authorized users have access to encrypted data. Additionally, encryption can impact database performance, so it is important to test and evaluate the impact of encryption on your specific database environment.

Conclusion

Unfortunately, data breaches are a fact of life in today’s technology environment and as administrators of data, we need to elevate our attention to security and its related protocols. But the challenge is that most DBAs are already very busy enabling the business, analyzing the data, or just managing and maintaining the server.

Thankfully, there are other options like leveraging a managed services provider (MSP) that is solely focused on managing, maintaining, and securing your data platform 24/7. By using an MSP, organizations can also leverage their data expertise to help application developers optimize their database applications and enable the business to realize more value.