Securing SharePoint: Harden SQL Server in SharePoint Environments

  • Article

As more and more sensitive data is stored in SharePoint, we find ourselves with a new requirement: how do we secure SQL Server? Permissions and Site access are great, but the content still lives in the database and we need to insure SQL Server's security is a first class citizen in our architecture. While we can't completely eliminate the risk, we can dramatically reduce it.

Hardening SQL Server is done in a 3 phased approach:

  1. Encryption at Rest (Encrypt the data sitting on the hard drives)
  2. Encrypt Connections (Encrypt the data in flight on the network between servers)
  3. Server Isolation (Configure SQL Server's firewall to ignore requests from unauthorized servers)

 

NOTE: These steps are done on a single server instance. This blog can be used as a guide for more complex implementations including other firewall products, clustering, IPSec, VLANs, etc. Additionally, this post assumes you've already implemented Kerberos.

 

Encryption at Rest

Encryption at Rest is accomplished via Transparent Data Encryption (TDE). TDE uses a server level certificate to do page level encryption on the raw database MDF/LDF files. TDE uses a symmetric key stored in the master database in the form of a certificate (or an asymmetric key stored in an EKM module, but that's beyond the scope of this discussion). Data is encrypted in AES or 3DES and the original certificate is required to access the database.

At a high level, TDE protects us in the event the hard drives or backups are stolen/compromised; the offending user will not be able to restore or attach the databases. SQL Server recovery tools will be nullified and only recover gibberish. If users are unfamiliar with the concept, TDE is logically similar to Bitlocker in the OS.

 

How to set it up:

TDE is deployed in 2 phases: Instance and Database Configuration. Instance Configuration is done only once per instance, but Database Configuration will need to be repeated for each database

Instance Configuration:

  1. Create a master key (and Password!) that will reside in the Master Database

    USE
    [master]

    GO

    CREATE
    MASTER
    KEY
    ENCRYPTION
    BY
    PASSWORD='P@$$w0rd'

    GO

     

  2. Create a certificate protected with the Master Key

    USE
    [master]

    CREATE
    CERTIFICATE
    TDECertificate
    WITH
    SUBJECT
    =
    'TDE Certificate'

    GO

     

  3. Done!

     

Database Configuration:

  1. Get the certificate name

    SELECT
    name,pvt_key_encryption_type_desc
    FROM
    sys.certificates

    GO

     

     

  2. Create a Database Encryption Key (I created a DB called "Test") using the certificate name from the previous step

    USE
    [Test]

    GO

    CREATE
    DATABASE
    ENCRYPTION
    KEY
    WITH
    ALGORITHM
    =
    AES_256

    ENCRYPTION
    BY
    SERVER
    CERTIFICATE
    TDECertificate

    GO

     

  3. Set the Database to use Encryption

    USE
    [master]

    ALTER
    DATABASE
    [Test]
    SET
    ENCRYPTION
    ON

     

  4. Done!

Backup the Cert:

  1. You'll need the cert to restore the DB to another server. Back up both files and save the Encryption password somewhere secure!

    BACKUP
    CERTIFICATE
    TDECertificate

    TO
    FILE
    =
    'C:\TDECertificate.cert'

    WITH
    PRIVATE
    KEY ( FILE
    =
    'C:\TDECertPrivateKey.key',
    ENCRYPTION
    BY
    PASSWORD
    = 'P@$$w0rd1234')

    GO

     

  2. Done!

Test:

  1. Backup the database

    BACKUP
    DATABASE
    [Test]

    TO
    DISK='C:\Test.bak'

    GO

  2. File copy it another instance (Instance B) and try to restore it

    RESTORE
    DATABASE
    [Test]

    FROM
    DISK='C:\Test.bak'

    GO

  3. It will fail with a message similar to

    Msg 33111, Level 16, State 3, Line 1
    Cannot find server certificate with thumbprint '…….'.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

  4. Success! Users can not arbitrarily restore the database without the applicable certificates from the server and the encryption password

  5. To properly restore the database, file copy the C:\TDECertPrivateKey.key file to Instance B and restore it

    USE
    master

    CREATE
    CERTIFICATE
    TDECertificate

    FROM
    FILE
    =
    'C:\TDECertificate.cert'

    WITH
    PRIVATE
    KEY (FILE =
    'C:\TDECertPrivateKey.key',

    DECRYPTION
    BY
    PASSWORD
    =
    'P@$$w0rd1234')

    GO

  6. Try to restore the database on Instance B

    RESTORE
    DATABASE
    [Test]

    FROM
    DISK='C:\Test.bak'

    GO

  7. Done!

 

That finalizes our TDE setup and encrypts our data on the disks. We're now protected from individuals grabbing the disks and/or database backups without authorization.

 

Note about Compression: Compression is done by finding patterns in the binary format of the applicable file and converting them to a smaller pattern (i.e. '12345' is represented by 'a'). Encryption removes patterns in the raw data to prevent brute force decryption. Compression and Encryption do not coexist and TDE will effectively eliminate all gains from Compression. Enabling encryption on a compressed database will just cause a lot of useless overhead as SQL decompresses an uncompressed file.

 

Encrypt Connections

Encrypting the Connections is accomplished via Secure Socket Layer (SSL). SSL uses a certificate exchange process to validate the server's authenticity and encrypt the data exchanged between the servers.

SSL protects the environment by encrypting the information transmitted between servers. If a malicious user was attempting reconnaissance via a wire sniffer (Netmon, WireShark or some similar tool) all the data would be encrypted and the user would get gibberish.

 

How to set it up:

Note: This walkthrough assumes you've already deployed an applicable server certificate. My VM uses a self-signed cert; a full production configuration should use a 3rd party trusted authority.

  1. Open Sql Server Configuration Manager

  2. Expand SQL Server Network Configuration

  3. Right click on Protocols for <Instance Name> and select Properties

     

  4. Change Force Encryption to Yes

     

  5. Select the Certificates tab and select your applicable certificate

     

  6. Click OK and restart SQL Server

  7. Done!

 

Test:

  1. Execute the below command and validate Encrypt_Option is set to TRUE

    SELECT
    net_transport, auth_scheme, encrypt_option

    FROM
    sys.dm_exec_connections

    WHERE
    session_id
    =
    @@SPID;

     

  2. Done!

 

Note: Authentication to SQL Server via NTLM/Kerberos is ALWAYS encrypted. But transactions after encryption are clear text

 

 

Server Isolation

Server Isolation can be done several different ways, but the end result is the same: configuring the server to only respond to authorized machines. An "authorized machine" is a list controlled by a governing body (usually security team or network team). The simplest and most cost effective way to isolate SQL is configuring the Windows Firewall with Advanced Security. Other methods exist (VLANs, other Firewall products, etc.) but they are beyond the scope of this document.

By isolating SQL, we reduce the attackable area of SQL server, enforcing an additional layer of security beyond network access.

How to set it up:

Note: This walkthrough configures the local machine's firewall. Windows Firewall is configurable via Group Policy and should be used if you're deploying an actual production environment.

  1. Windows Firewall with Advanced Security from Administrative Tools

  2. Verify Inbound Connections that do not match a rule are blocked

     

  3. Select Inbound Rules and choose New Rule

  4. For Rule Type select Program and click Next

  5. Select your SQL Server EXE and click Next

    1. Default path is C:\Program Files\Microsoft SQL Server\<Instance Name>\MSSQL\Binn\sqlservr.exe

  6. Choose Allow the Connection and click Next

  7. Choose all 3 domain types and click Next

  8. Give the new rule a Friendly Name and click Finish

  9. Select your newly created rule and select Properties

     

  10. In the Test properties window, select Scope tab and change Remote IP Address to These IP Addresses

     

  11. Click Add and add all IP Addresses of the SharePoint Farm

     

  12. Click OK to close the window

  13. Done!

Test:

  1. From an authorized machine (one of the IPs you put in the previous step), open SQL Server Management Studio and try to connect. It should be successful.

  2. From an unauthorized machine (an IP you didn't put in the previous step), open SQL Server Management Studio and try to connect. It should fail

  3. Done!

 

 

 

Summary

SQL Server's security should match the information sensitivity in SharePoint. SharePoint/Windows permissions do not apply to raw content within the database and are insufficient to protect SharePoint's data to a determined user. There is a cost associated with each security implementation, both in server performance and O&M. Your mileage may vary, but my load tests showed +/- 15% performance degradation. However, if security if your primary concern, these performance costs could be mitigated by scaling up SQL Server.

While no amount of security is perfect, implementing this three phased approach will significantly reduce SQL's vulnerability to unintended data access.

 

Further reading with a deeper analysis of these security approaches and additional information for Access Control can be found on the whitepaper SQL Server 2012 Security Best Practices - Operational and Administrative Tasks

 

Non-Default Ports and SQL Client Aliases

Non-Default Ports + SQL Client Aliases are obfuscation and should not be used as a sole defense strategy. With regards to security, a Non-Default Ports implementation is limited when considering the ease and speed of current technology to scan the ~65k possible ports SQL Server could utilize. SharePoint requires SQL Aliases in all Non-default port SQL instances, but SQL Aliases are incompatible with some business intelligence features and Diagnostic Timer Jobs. Returning SharePoint to 100% functionality requires a combination of SQL Client aliases and DNS Aliases. SQL-specific traffic will use the SQL Client alias, while non-SQL Traffic to the database server (i.e. WMI calls) will utilize the DNS Alias.

Consider the limited value of Non-Default port implementations, compared to the operational complexity of SQL Client Aliases and DNS Aliases before implementing. Additionally, there is no security value using SQL Client Aliases in default port implementations.

 

DNS Aliases

DNS Aliases facilitate scale up/scale out/migration scenarios and enhance manageability. Their value is concentrated in maintenance activities and bring little value to a defense strategy. Their use is 100% compatible with all SharePoint functions and features.

Comments

  • Anonymous
    January 01, 2003
    TDE is only available in SQL Server Enterprise edition. (ref: http://msdn.microsoft.com/en-us/library/cc645993.aspx#Enterprise_security)

  • Anonymous
    October 23, 2013
    You mention "SQL Aliases are incompatible with some business intelligence features and Diagnostic Timer Jobs". Can you point back to any specific guidance on TechNet in regards to this?

  • Anonymous
    December 15, 2013
    Pingback from Intranet Collaboration Security in SharePoint ??? Part 3 - A soldier of fortune

  • Anonymous
    December 16, 2014
    This is an excellent write-up! Thanks for posting it! I especially liked your coverage of the practice of non-default ports for obfuscation.

  • Anonymous
    February 03, 2015
    Nice post. TDE only works on the Content databases (I think) and your search indexes also need to be encrypted.

  • Anonymous
    February 03, 2016
    Is it possible to Encrypt a Connection to SharePoint 2013 OnPrem with a Key Certificate to an External Asp.Net app/ or other. I want to Connect to the Secure Store for the Credentials and Also retrieve data from SharePoint lists. But I must do this with key certificates.

    • Anonymous
      September 13, 2017
      This is one of the best article I read on TechNet. @Ryan Campbell Thank you very much for sharing with us. I was on the look for this kind of eye-opening articles on how to improve SECURITY for SharePoint.