Chapter 4: Securing Your SQL Server

Securing SQL Server is a crucial task, as SQL Server usually holds very important and sensitive information in your environment. You need to apply many principles in order to properly secure your databases. Fortunately, SQL Server offers many options to help you with securing the data you store on it. Securing an SQL server is quite a complex task; you need to consider that SQL Server is a client application running on the Windows server, which is accessible via a network. In order to fully secure the environment, you need to secure the Windows Operating System (OS) too and put proper security measures on the network as well.

In this chapter, we will be covering the following topics:

• Configuring SQL Server service accounts
• Configuring authentication and authorization
• Encrypting SQL Server data
• Data Discovery and Classification
• SQL Server vulnerability assessment
• Encrypting SQL Server connections

Configuring SQL Server service accounts

An important part of the configuration of your SQL Server environment is the service accounts that are used for running your SQL Server services. Many of these can be configured immediately during the installation of your SQL Server. There are several options for you to select from while configuring an account for SQL Server services, as follows:

• Virtual accounts
• Managed service accounts
• Group managed service accounts
• Built-in system accounts
• Domain user accounts
• Local Windows accounts

Let's now get into each of the accounts in detail.

Virtual accounts

The default choice of any OS higher than Windows Server 2008 R2 is a virtual account. A virtual account is a managed local account for the simple administration of your services. One of the important benefits of virtual accounts is their auto management, so you don't need to worry about regular password updates on...

Configuring authentication and authorization

SQL Server security works in layers. As a first step, SQL Server will perform authentication, whereby SQL Server determines who you are and if you can log in. If you're successfully logged on, then SQL Server will perform authorization, determining if you can do what you're trying to do. In the next part of the chapter, we will see how to configure server authentication, how to work with server objects, and how to assign server-level permissions.

Authentication

SQL Server comes with two authentication modes, as follows:

• SQL Server and Windows Authentication mode (frequently called Mixed mode)
• Windows Authentication mode

As the names of the modes would suggest, you can always log in with some sort of Windows credential. On top of that, SQL Server can be configured to use its own accounting and isolated accounts stored directly on SQL Server.

You can choose the authentication&...

Encrypting SQL Server data

When you are storing sensitive data on your SQL Server, you may need to encrypt the data to protect the data from accidental misuse. Your company may have business and technical requirements to encrypt the data, or even legal requirements to encrypt any sensitive information.

SQL Server has many options on how to protect data with encryption, depending on the need to protect data at rest or in transit. The whole encryption ecosystem in SQL Server is quite complex and offers many options, as can be seen in the following diagram:

Fig. 4.12 – Encryption hierarchy for SQL Server

Transparent Data Encryption

One of the options on how to encrypt the data in the database is Transparent Data Encryption option. This feature has been available since SQL Server 2008 and works at the input/output (I/O) level. Both file types—data and log—are encrypted on the disk, and SQL Server does the encryption...

Data Discovery and Classification

Whenever you need to discover and report any sensitive data in your database, you can use a new feature called Data Discovery and Classification . When you are discovering the data, you must also classify and label any data found that fits your classification needs. There are several use cases for this feature, as follows:

• Meeting data privacy standards (for example, General Data Protection Regulation (GDPR); Payment Card Industry Data Security Standard (PCI DSS); Sarbanes-Oxley (SOX); Health Insurance Portability and Accountability Act (HIPAA))
• Controlling access to highly sensitive data (for example, personally identifiable information (PII))

  Note

  This feature is available for SQL Server 2012 and newer and was introduced with SQL Server Management Studio 17.5; however, it's recommended to use the latest version of SQL Server Management Studio.

While you are connected to the SQL Server, you can run classification on your database...

SQL Server vulnerability assessment

SQL Server Management Studio 17.4 was released with a very handy feature—scanning for vulnerabilities on your databases. It's always better to run with the latest version of SQL Server Management Studio (screenshots in this book are based on the 18.4 and 18.5 versions). This vulnerability assessment is supported on any SQL Server with version 2012 and higher and checks for a predefined set of vulnerabilities.

To create a new scan, you have to do the following:

1. Right-click your database.
2. Choose Tasks.
3. Select Vulnerability Assessment.
4. Click on Scan for Vulnerabilities.

Once the scan is complete, you will be presented with a result view in your SSMS application, with a summary about failed and passed checks.

In the following screenshot, you can see a sample scan with several categories of findings—high, medium, and low risk:

Fig. 4.20 – Vulnerability assessment report

...

Encrypting SQL Server connections

Connection to the SQL Server is by default not encrypted, until you configure your server and client otherwise. When you're connecting to the SQL Server with SQL Server Management Studio, you can choose to encrypt your connection to the server. In the following screenshot, we can see how the server connection is configured and how Management Studio responds to such a connection attempt:

Fig. 4.22 – Encrypted connection to SQL Server

Let's see how SQL Server responds to such a connection request in the following screenshot:

Fig. 4.23 – Failed login due to untrusted certificate

As you can see, the connection was not established due to a server certificate that is not trusted. If the SQL Server is not configured to use any certificate, it will automatically generate a self-signed one during the instance startup, as illustrated in the following screenshot:

Fig...

Summary

Security is a very important part of your SQL Server deployment, and in this chapter, we have seen many options that you can use to secure your SQL Server environment. You are making important choices already during the setup of your environment, whereby you configure the service accounts and authentication. Once you have SQL Server up and running, you have to configure SQL Server logins for your groups and accounts, which provide them proper access to the SQL Server. 

It's important to understand the difference between authentication and authorization. Just because you can log in to the SQL Server does not give you the rights to change configuration, access data, or perform any data changes. There are many configuration items that require sysadmin role membership and there's a frequent push from application teams and application DBAs to be part of this restricted server role, but you should limit the members of the sysadmin role as much as possible. The same...