I Can See (your database traffic) Clearly Now
We are regularly told that as part of our SharePoint architecture considerations we should be using SSL for end user access to SharePoint sites even for internal users. This does happen fairly frequently these days. Not always but the message does seem to be getting through. What we never see though is securing the communications between our SharePoint server and our database servers. This is a shame given how easy it is to implement and how similar the malicious user entry points are.
To go places and do things that have never
A test was performed using the farm toplogy above (SQL 2014 and SP 2016). A new web application/Content DB and site collection was created.
The associated database for the web application above
A new SharePoint list with a single item was created - note the list item name:
A packet sniffer was used to capture the packets between the endpoints whilst the site was being browsed. Whilst this scenarios may seem artificial it is perfectly possible that a malicious user or code could access network traffic given time, skills and unpatched vulnerabilities. The capture below was performed using Wireshark (https://www.wireshark.org/). Microsoft Netmon could also be used (http://www.microsoft.com/en-gb/download/details.aspx?id=4865). It shows traffic between the SQL server and the SharePoint server using the standard SQL port 1433.
Once the capture had completed a search was executed for the list item text. The text was easily located. The system makes no attempt to obfuscate the data let alone encrypt it. So how do we fix this? How do we configure encryption for SQL server connections? For this lab environment it is pretty straightforward. We need a server authentication certificate that matches the FQDN of the SQL server. For the purposes of this test I generated a self signed certificate in IIS (there are other methods of generating self-signed certificates). In the real world of course you will need to buy a proper certificate rather than use a self signed one. In IIS Manager (for this test you need to do that on the SQL server – so you may need to install the IIS role.) 1. Go to Features View and select ‘Server Certificates’. Click ‘Create self signed certificate’ in the righthand pane. 2. Enter a Friendly name and click OK.
The certificate as listed in IIS. Note it matches the FQDN of the SQL server.
3. In SQL Server Configuration Manager right click ‘Protocols for MSSQLServer’ under ‘SQL Server Network Configuration’.
4. In the Certificate tab select the certificate you created earlier:
5. In the Flags tab select ‘Force Encryption’ = Yes and Click OK. 6. Restart the SQL server service. If the service fails to start (you may see an error in the Services console) then you may need to give the SQL service account permissions to read the private key of the certificate. Proceed as follows. In the Certificates console navigate to the new certificate under the ‘Personal’ store and click ‘Manage Private Keys’ from the All Tasks menu.
Give the SQL service account read permissions to the private keys and click OK.
Restart the SQL server service. SQL server should now be forcing clients to encrypt their connections. To test this try another packet capture. The first thing to notice is that there will be a lot of packets marked as ‘TLS Exchange’.
This is the encrypted traffic between SQL and the SharePoint server. Browsing to the same page as above and repeating the same search for the list item text yields:
The data is no longer visible in the packet stream. This is a simple scenario but it took less than 10 minutes to secure the SharePoint-SQL traffic stream. In more complex environments and production scenarios a robust and comprehensive plan would be required (as always) but the principals are simple and the execution straightforward.