Data breaches often stem from poorly secured database servers. Being a popular enterprise solution, MS SQL Server databases often become a prime target for attackers. They frequently target these databases and exploit common vulnerabilities such as SQL injection, misconfigured permissions, and weak authentication methods. As a database administrator, understanding and implementing essential SQL Server security features is key to protecting your data from these threats. This guide covers six fundamental security practices that will help you fortify your SQL Server environment, protect sensitive information, and maintain compliance with industry regulations.
Here are 6 SQL Server security features that you should know to keep your server data protected.
1. SQL Server Authentication vs. Windows Authentication
Microsoft SQL Server supports two authentication options:
- Windows Authentication relies on Active Directory (AD) to authenticate users before they connect to SQL It is the recommended authentication mode because AD is the best way to manage password policies and user and group access to applications in your organization.
- SQL Server Authentication works by storing usernames and passwords on the database server. It can be used in situations where Active Directory is not available.
You can use SQL Server and Windows Authentication at the same time (mixed-mode), but, whenever possible, use Windows Authentication exclusively.
If you must use SQL Server Authentication, make sure that the default sa account is either disabled or has a strong password that is changed frequently, because this account is often targeted by hackers. SQL Server accounts, including sa (if it’s enabled), can be managed using the SQL Server Management Studio service or the ALTER LOGIN Transact-SQL (T-SQL) command.
2. Server Logins and Roles
Regardless of the authentication method you choose, there are two types of login that you configure to SQL instances: user logins and server logins. I’ll discuss user logins in the next section. Server logins let users establish a connection to a SQL Server instance. Each server login is assigned one or more server roles that enable it to perform specific actions on the instance. By default, server logins are assigned the public server role, which gives basic access to the instance. Other available roles include bulkadmin, sercurityadmin, dbcreator and serveradmin.
Server logins can be created using T-SQL or the SQL Server Management Studio. When creating a server login, you must specify a default database. Server logins are associated with a user login in the default database. It’s worth noting that a server login name and the name of its associated user login don’t need to match. If there is no associated user object in the default database, the server login will be denied access unless the server role assigned to the login has access to all databases. Server logins can be mapped to a user in one or more databases, and you can create users when setting up server logins.
3. Database Users, Schema and Roles
When creating a user login, you need to specify the database it will be associated with, a username and a default schema that will be applied to all objects that the user creates if no other schema is specified. SQL Server schemas are collections of objects, like tables and views, logically separated from other database objects, which makes it easier to manage access and means there is no need to use the schema name when running T-SQL commands against a database.
The default schema for user-defined objects is dbo. The other default schema is sys; it owns all system objects.
In the same way that server logins are assigned server roles, user logins are assigned database roles, which assign rights in databases. Server database roles include public, db accessadmin, db owner and db securityadmin.
4. Securables and Permissions
When server or database roles would give a user too much or too little access, you can assign one or more securables instead. Securables exist at the server, schema and database levels; they are SQL Server resources that can be accessed by server and user logins. For example, using securables, you could give a server login access to a specific table or function only, a level of granularity that isn’t possible by assigning a role to a login.
Permissions are used to grant access to SQL Server securables. You might grant permission to just view data or just to modify data. The GRANT, DENY and REVOKE T-SQL statements are used for configuring permissions.
However, permissions can be complicated. For example, setting DENY permissions on a securable prevents permission inheritance on lower-level objects. But the column-level GRANT permission overrides DENY at the object level, so DENY permission set on a table is overridden by GRANT permission on a column. Because permissions can be complex, it’s always worth checking effective permissions using T-SQL. The following command determines JoeB’s permissions granted on an object, in this case a table called ‘employees’.
SELECT * FROM fn_my_permissions(‘joeb’, employees);
GO
5. Data Encryption
SQL Server supports multiple encryption options to protect data at rest and in transit:
Secure Sockets Layer (SSL)/Transport Layer Security (TLS):
Encrypts traffic as it travels between the server instance and client application, much like internet traffic is secured between a browser and a server. Additionally, the client can validate the server’s identity using the server’s certificate. It is recommended to use the latest version of TLS rather than SSL, as SSL is considered outdated and less secure.
Transparent Data Encryption (TDE):
Encrypts data on disk. Specifically, it encrypts the entire database at the file level, including the data and log files. Client applications do not need to be changed when TDE is enabled because the encryption and decryption processes are handled transparently by the SQL Server. However, TDE does not cover crash dumps or certain system databases (master, model, MSDB), which might still contain sensitive information in plain text. To cover these, consider using file-level encryption tools like BitLocker on Windows.
Disk Encryption (BitLocker):
BitLocker is a full-disk encryption feature included with Windows that can encrypt the entire disk on which SQL Server resides. This adds an additional layer of protection, ensuring that data on the disk is inaccessible without the proper decryption key, which helps protect the data if the physical disk is lost or stolen.
Backup Encryption:
Backup Encryption ensures that SQL Server backups are encrypted. This is similar to TDE but specifically applies to backup files rather than the active data and log files. It provides an extra layer of security for backups stored off-site or in less secure environments.
Column/Cell-Level Encryption:
Column/Cell-Level Encryption encrypts specific data within individual columns or cells in a database. Data remains encrypted even when stored in memory. This method requires changes to client applications to handle encryption and decryption, typically through the use of SQL Server functions like EncryptByKey and DecryptByKey.
Always Encrypted:
Always-On Encryption ensures that data remains encrypted in transit, in memory, and at rest without requiring changes to client applications. Encryption and decryption are handled at the client-side driver level, meaning SQL Server itself never sees the unencrypted data. However, this can lead to limitations, such as some indexing and querying operations not being supported on encrypted columns.
There are two types of Always Encrypted: Deterministic encryption and Randomized encryption.
Deterministic encryption
Deterministic encryption always generates the same encrypted value for any given plain text value, which allows for equality comparisons, joins, and grouping operations. However, this method is susceptible to pattern and frequency analysis attacks, making it less secure in certain scenarios. It also does not support range queries or LIKE queries, limiting its functionality in complex search operations.
Randomized encryption
Randomized encryption generates different encrypted values for the same plain text value each time it is encrypted, providing a higher level of security. The downside is that this type prevents equality comparisons, joins, and grouping operations, and does not allow indexing. This can impact query performance and make it less suitable for operations that require data matching.
6. Row-Level Security
Row-Level Security (RLS) allows organizations to control who can see rows in a database. For example, you could restrict users to seeing only rows that contain information about their clients.
RLS consists of three main parts: a predicate function, a security predicate, and a security policy. The predicate function checks whether the user executing the database query can access a row based on logic. For instance, you could check if the username of the user running the query matches a field in one of the row’s columns. A predicate function and security predicate are defined together in a function to either silently filter the results of a query without raising errors or to block with an error if row access is denied. Finally, a security policy binds the function to a table.
How Netwrix Can Help
Securing your SQL Server environment is crucial, and Netwrix offers powerful solutions to help you achieve this goal effectively.
Netwrix Auditor for SQL Server
Netwrix Auditor for SQL Server provides comprehensive monitoring and auditing capabilities, giving you deep insights into your database activities. It tracks who has access to what, who is logging in, and what critical changes are being made, all in near real-time. This proactive approach helps you quickly identify and respond to any suspicious activities, ensuring that potential threats are addressed before they can cause significant damage. With its ready-to-use reports and dashboards, Netwrix Auditor streamlines compliance efforts, making it easier to demonstrate adherence to security standards and regulatory requirements. This tool not only enhances your security posture but also saves you time and resources in managing and auditing your SQL Server environment.
Netwrix Enterprise Auditor for Databases
Netwrix Enterprise Auditor for Databases takes a broader approach, helping you discover and manage all your database instances across the network. It automates the identification of sensitive data, assesses who has access to it, and provides a detailed audit trail of all database activities, including sensitive data access. This comprehensive visibility helps you uncover and mitigate risks such as excessive permissions and configuration vulnerabilities. By simplifying compliance reporting and ensuring that your database environment adheres to security policies, Netwrix Enterprise Auditor helps fortify your defenses against data breaches and ensures that you meet regulatory and insurance requirements with ease.
Netwrix solutions provide a robust framework for managing and securing your SQL Server databases, giving you the tools and insights needed to protect your vital data and maintain a secure and compliant environment.
