|We Speak Linux|
This talk will not cover:
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.
This talk will cover SQL Server versions in two broad swaths:
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.
Do you really need all of the following on your OLTP instance?
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.
Do not install sample databases on production instances
If you don't need a database for production, don't make it available! -- STIG V-40943
If you do not need a service, turn it off. Attackers cannot take advantage of disabled services as an entry point for an attack.
Good examples of services which might not be necessary:
Use Get-Service to see which services are running.
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.
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.
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!
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 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.
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.
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.
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:
Using an EA to generate certificates is outside the scope of this talk.
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.
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
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.
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.
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.
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.
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.
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.
Two encryption methods are allowed: deterministic and randomized.
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.
Note that this is a V1 product, so there are limitations:
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.
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.
Run when setting up a new instance and as part of regular security scans.
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