Ramblings of a SharePoint guy

See what happened was.....

Securing SharePoint: Harden SQL Server in SharePoint Environments

Securing SharePoint: Harden SQL Server in SharePoint Environments

  • Comments 6
  • Likes

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]





  2. Create a certificate protected with the Master Key

    USE [master]




  3. Done!


Database Configuration:

  1. Get the certificate name

    SELECT name,pvt_key_encryption_type_desc FROM sys.certificates




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

    USE [Test]






  3. Set the Database to use Encryption

    USE [master]



  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!


    TO FILE = 'C:\TDECertificate.cert'

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



  2. Done!


  1. Backup the database


    TO DISK='C:\Test.bak'


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


    FROM DISK='C:\Test.bak'


  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


    FROM FILE = 'C:\TDECertificate.cert'

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

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


  6. Try to restore the database on Instance B


    FROM DISK='C:\Test.bak'


  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!



  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!


  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!





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.

  • 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?

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

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

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

  • DBDefence can encrypt Sharepoint databases transparently.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment