Securing SQL Server

Kevin Feasel (@feaselkl)

http://CSmore.info/on/securesql

Who Am I? What Am I Doing Here?

Catallaxy Services
@feaselkl
Curated SQL
We Speak Linux

Expectations

This talk will not cover:

  1. SQL injection
  2. Good SQL or application development practices
  3. Logins, users, roles
  4. Permissions management

These are extremely important topics and I have a link to resources on these topics (and more) at the end. Security is a big topic and I’m focusing mostly on administrative tooling for this talk.

Two Broad Bases

This talk will cover SQL Server versions in two broad swaths:

  1. SQL Server 2008 through SQL Server 2014
  2. SQL Server 2016

If you are using SQL Server 2000 or earlier, you are missing out on major security features. Some of this may apply…maybe… Even SQL Server 2005 gets a raised eyebrow—it’s no longer supported by Microsoft, so it will not get regular security patches and bug fixes.

Agenda

  1. Configuration and Surface Area
  2. Transparent Data Encryption
  3. Backup Encryption
  4. SSL Encryption
  5. Service Accounts
  6. Dynamic Data Masking
  7. Row Level Security
  8. Always Encrypted

Surface Area

The Basic Idea

  1. Do not install unnecessary components
  2. Turn off unnecessary services
  3. Disable unnecessary features

Surface Area

Unnecessary Components

Do you really need all of the following on your OLTP instance?

  1. SQL Server Reporting Services
  2. SQL Server Analysis Services
  3. SQL Server Integration Services
  4. SQL Server client tools (Management Studio)

These are very useful products and tools, but if this instance doesn’t need them, don’t install them! An attacker can’t exploit a product which isn’t installed.

Surface Area

Unnecessary Components

Do not install sample databases on production instances

  • Wide World Importers
  • AdventureWorks
  • Contoso
  • Northwind
  • Pubs (really?)

If you don't need a database for production, don't make it available! -- STIG V-40943

Surface Area

Turn Off Services

If you do not need a service, turn it off. Attackers cannot take advantage of disabled services as an entry point for an attack.

Surface Area

Turn Off Services

Good examples of services which might not be necessary:

  • SQL Server Browser – Needed for named instances with dynamic ports. If you don’t have named instances (or use static ports), you don’t need the SQL Server Browser service enabled.
  • SQL Server VSS Writer – Needed to copy data files while SQL Server is running (e.g., third party file copy tool). If you disable this, you can still take database backups.

Surface Area

Turn Off Services

Use Get-Service to see which services are running.

Surface Area

Disable Features

There are several methods to connect to SQL Server. Two of these methods are TCP/IP and Named Pipes. You should only need one of these and can safely disable the other. Normally this is Named Pipes, which is most useful for apps designed around NetBIOS.

What About...?

Non-Standard Ports?

Some people argue that you should configure SQL Server to listen on non-standard ports (TCP 1433). This is not bad advice, but does not provide much benefit by itself; an attacker can still use other methods to obtain information on the active port, such as running a port scan.

This could be useful in conjunction with honeyports, a topic outside the scope of this talk.

What About...?

Disabling xp_cmdshell?

Some people argue that you should disable xp_cmdshell because attackers can use it to run commands on the underlying Windows server.

Disabling xp_cmdshell is bad advice. By default, xp_cmdshell is available only to sysadmins. Therefore, for an attacker to use xp_cmdshell, that attacker must be a sysadmin. And sysadmins can re-enable xp_cmdshell at will!

Agenda

  1. Configuration and Surface Area
  2. Transparent Data Encryption
  3. Backup Encryption
  4. SSL Encryption
  5. Service Accounts
  6. Dynamic Data Masking
  7. Row Level Security
  8. Always Encrypted

TDE

TDE

Transparent Data Encryption (TDE) was introduced in SQL Server 2008 and is available only in Enterprise Edition.

It encrypts data as it is written to disk, thereby providing protection for “data at rest.” It does not protect data in memory or data being sent over the network (“data in motion”)!

TDE does protect data in backups automatically, and you will need the correct certificate installed on an instance if you want to restore that data.

TDE introduces a small but noticeable (~5%) performance hit.

TDE

Demo Time

TDE

When To Use

TDE can be useful as one part of a security strategy, but you can bypass TDE on its own. It also incurs a non-zero performance penalty, which might be too much for busy transactional systems.

Because TDE also encrypts tempdb, all databases tend to take a performance hit, even databases without TDE configured.

TDE

Agenda

  1. Configuration and Surface Area
  2. Transparent Data Encryption
  3. Backup Encryption
  4. SSL Encryption
  5. Service Accounts
  6. Dynamic Data Masking
  7. Row Level Security
  8. Always Encrypted

Backup Encryption

SQL Server 2014 gave us native backup encryption. This is available in Standard Edition.

Backup encryption uses AES (128, 192, or 256 bit key lengths) and requires a certificate or asymmetric key be available for encryption.

Prior to SQL Server 2014, third party products could encrypt backups, but nothing was available natively.

Backup Encryption

Demo Time

Backup Encryption

When To Use

Always. You can combine encryption with backup compression (2008-2016 Enterprise Edition or 2016 SP1 Standard/Enterprise) and the backup engine is smart enough to compress first.

If you have a third-party data de-duplication engine which stores your database backups, you will likely lose de-dupe benefits.

Backup Encryption

Agenda

  1. Configuration and Surface Area
  2. Transparent Data Encryption
  3. Backup Encryption
  4. SSL Encryption
  5. Service Accounts
  6. Dynamic Data Masking
  7. Row Level Security
  8. Always Encrypted

SSL Encryption

SQL Server offers the ability to use an SSL certificate for encrypting connections. This provides security for data in transit over a network (“data in motion”) and prevents attackers from using packet sniffing tools to read data packets.

There are two viable certificate options available:

  • Self-signed certificate (ONLY FOR TESTING!)
  • Enterprise authority-generated certificates

Using an EA to generate certificates is outside the scope of this talk.

SSL Encryption

Demo Time

SSL Encryption

Agenda

  1. Configuration and Surface Area
  2. Transparent Data Encryption
  3. Backup Encryption
  4. SSL Encryption
  5. Service Accounts
  6. Dynamic Data Masking
  7. Row Level Security
  8. Always Encrypted

Service Accounts

YOUR SERVICE ACCOUNT DOES NOT NEED DOMAIN ADMIN RIGHTS!

If your SQL Server machine gets compromised, the best case scenario is that an attacker has no rights outside of SQL Server.

In practice, SQL Server instances need some rights (especially for features such as Filestream/Filetable) and granting those rights is fine. But there is never a good reason for Domain Admin rights.

The Principle of Least Privilege: give the fewest rights necessary.

Service Accounts

Virtual Accounts

Windows Server 2008 R2 and SQL Server 2012 introduced the concept of virtual accounts: non-domain pseudo-accounts which have permissions on the local machine.

If you need to grant additional rights to virtual accounts, you can do this by changing permissions on the machine account in Active Directory.

Ex: for a server named PRODSQL, modify the PRODSQL$ machine account in AD

Service Accounts

When Not To Use Virtual Accounts

You cannot use a virtual account on a cluster. You do need a valid domain account for clustered services.

You do not need to use virtual accounts to be secure; you can use valid domain accounts. Virtual accounts are useful, however, because they enforce separation of accounts between servers—an attacker only has rights to the exploited SQL Server instance rather than all instances on your domain.

Service Accounts

Agenda

  1. Configuration and Surface Area
  2. Transparent Data Encryption
  3. Backup Encryption
  4. SSL Encryption
  5. Service Accounts
  6. Dynamic Data Masking
  7. Row Level Security
  8. Always Encrypted

Dynamic Data Masking

Dynamic Data Masking does exactly what it sounds like: it masks data. Specifically, it applies functions to results in the SELECT clause of a query and (potentially) obfuscates the results.

Masks include:

  1. E-mail address
  2. “Default” – “xxxx” for strings, 0 for numbers, etc.
  3. Custom strings (# of chars from beginning, pad, # of chars at end)
  4. Random value (for numeric values)

Dynamic Data Masking

Demo Time

Dynamic Data Masking

When To Use

Use Dynamic Data Masking to provide “over the shoulder” protection for data in 2016 Enterprise or any edition starting with 2016 SP1.

Note that DDM is not a particularly strong method of obscuring data and only operates at the level of displaying results.

Dynamic Data Masking

Agenda

  1. Configuration and Surface Area
  2. Transparent Data Encryption
  3. Backup Encryption
  4. SSL Encryption
  5. Service Accounts
  6. Dynamic Data Masking
  7. Row Level Security
  8. Always Encrypted

Row Level Security

SQL Server Analysis Services has had row-level security for several versions, and people have implemented their own versions of row-level security, but this is the first official support in SQL Server for segmenting records in a table based on view permissions.

It is available in 2016 Enterprise or any edition 2016 SP1.

Row Level Security

Demo Time

Row Level Security

Considerations

  1. Insert statements are not protected.
  2. Queries now introduce a table-valued function which runs each time. This can be a significant performance drag.
  3. There are side-channel attacks which users can perform to get an idea of what data is available, even if they can’t see the data itself.

Row Level Security

When To Use

Use row-level security when you need to segment user access within a table and can afford the performance hit of running a table-valued function on queries.

This is not a viable mechanism for security on a very busy transactional system, but could potentially work well in a relational warehouse scenario.

Row Level Security

Agenda

  1. Configuration and Surface Area
  2. Transparent Data Encryption
  3. Backup Encryption
  4. SSL Encryption
  5. Service Accounts
  6. Dynamic Data Masking
  7. Row Level Security
  8. Always Encrypted

Always Encrypted

Always Encrypted allows you to encrypt sensitive columns so that database users, even sysadmins, cannot view certain sensitive columns. Connecting applications would have a copy of the private key, allowing them to decrypt results. When implemented correctly, the only method to view sensitive data is through legitimate application access.

Historically, we have been able to roll our own encryption solutions, encrypting data in applications before storing it in SQL Server. Always Encrypted makes this process transparent to application developers.

Always Encrypted

Two encryption methods are allowed: deterministic and randomized.

  • Deterministic encryption guarantees the same encrypted value for any plain-text value.
  • Randomized encryption randomizes encrypted values, making it impossible to perform reversal attacks based on frequency.

Operation Deterministic Randomized
JOIN Yes NO
DISTINCT Yes NO
GROUP BY Yes NO
WHERE Equality Only NONE

Always Encrypted

Performance

Aaron Bertrand noted an increase in insert time of ~1.5-2X and a slight increase in select time. This was with an early CTP, though, so performance might have changed since.

Always Encrypted

Limitations

Note that this is a V1 product, so there are limitations:

  1. Column type limitations (generally not onerous, but strings must be bin2)
  2. Not for use with memory-optimized tables
  3. Cannot enable on existing columns; must create new columns
  4. No check or default constraints
  5. Limited SSIS support

Always Encrypted

When To Use

Use Always Encrypted to protect sensitive data which even database administrators should not be allowed to view.

It is available in 2016 Enterprise Edition or any edition in 2016 SP1.

Always Encrypted

Bonus: Full Disk Encryption

Bonus: WOxCompliant

Chris Bell has introduced a free tool called WOxCompliant, which checks a SQL Server instance for Department of Defense STIGs compliance.

The tool is under active development and Chris has plans to expand this to include compliance rules for regimes such as HIPAA and PCI. These overlap in large part with STIGs, so even if you are not in a DoD environment, they can be very useful.

Bonus: WOxCompliant

Demo Time

Bonus: WOxCompliant

When To Use

Run when setting up a new instance and as part of regular security scans.

Wrapping Up

This talk has been a starting point in hardening a SQL Server instance. Check out the link below to find more on SQL Server security.

To learn more, go here: http://CSmore.info/on/securesql

And for help, contact me: feasel@catallaxyservices.com | @feaselkl