The second article of the series covered infrastructure stuffs, basic network security such as building a reliable perimeter that minimizes threats to your SharePoint farm. It also provided you fundamental steps in Windows Server hardening that can eliminate potential vulnerabilities.
- Intranet Collaboration Security in SharePoint – Part 1
- Intranet Collaboration Security in SharePoint – Part 2
- Intranet Collaboration Security in SharePoint – Part 3 (you are here)
In this article, I consider looking at SQL Server that contains SharePoint databases, especially content database that may store your shared sensitive documents, list information or so on.
I’m not going to go through deeply in securing SQL Server using T-SQL script or so on because I’m not really someone who wears DBA hat. Moreover, Microsoft strongly suggests not too much to touch to SharePoint databases. In this article, what I would like to cover:
- SQL Server security
- User authentication
- Encrypting data with TDE (Transparent Data Encryption)
SQL Server security
The first lesson to learn is to select the right account for running SQL Server. I’ve seen lots of people that don’t have plan for service account. They often use domain administrator to run all of services in SQL Server. This way could lead to many problems, especially when someone had such a highest-privileges account in your domain controller. When you install SQL Server, it asks you the account to run SQL Service. You can change it later in SQL Server Configuration Manager. If you are using Windows Server 2008 R2, you shouldn’t miss the new feature called managed service account. This new feature allows you to manage automatic password when it needs to be changed. Let’s say you have the password policy that forces all passwords of the accounts in your Active Directory to be changed pursuant to the corporate policy. When the password of the SQL Service account is expired then you have to change it manually then restart SQL Service unless it is stopped. Using managed service account; the password of the SQL service account is automatically changed by Active Directory by definable schedule without stopping the service.
Another type of account you may need to consider using is virtual account that is also a new feature introduced the first time in Windows Server 2008 R2. This type simplifies service administration, especially no password management is required.
For more about managed service account and virtual account, read here: http://technet.microsoft.com/en-us/library/dd548356(v=ws.10).aspx
During communication, SQL Server talks with SharePoint by sending request and query with network packets. Such packet can be sniffed using a common tool Wireshark. In this case, you may need to secure the session between SQL Server and SharePoint. Fortunately, SQL Server allows you to use encrypt the communication using SSL. Open SQL Server Configuration Manager, under SQL Server Network Configuration, open the property windows of Protocols for MSSQLSERVER and set the value of Force Encryption to Yes. In the Certificate tab, import your certificate from whether self-signed certificate, internal certificate authority or a trusted provider such as VeriSign, Thawte…
By default, SQL Server uses 1433 port to communicate. If you aren’t using a 3rd firewall hardware, you can use the built-in Windows Firewall with Advanced Security in order to allow this port to be able to work over the network.
In terms of authentication, there are two basic authentication mechanism you can use for SQL Server: NTLM and Kerberos. Each of the two has pros and cons. Kerberos is more secure than NTLM. Kerberos protocols ensure mutual authentication that could prevents man-in-the-middle attack. Moreover, using Kerberos helps reduce load on domain controller. It, however, requires you to configure SPN before using it. Your choice is dependent on your security requirement.
When it comes to user authentication in SQL Server, you have not only built-in Windows authentication supported by Active Directory but also authentication inside SQL Server. There are lots of discussions and references on differences between Windows authentication and SQL authentication. One of the trusted resources can be founded here. Microsoft strongly recommends using Windows authentication to maximize use of powerful features in Active Directory. This way helps centralize account administration as well.
Every time you want to assign permission to an account to a SharePoint database (for example assigning dbcreator and securityadmin before SharePoint installation), you must create login first. The login can be a user in Active Directory or a custom user in SQL Server authentication. If you are to create a new account named “sa”, make sure you are done some penetration tests unless this account could be compromised to attacker by brute-force attack. I’ve seen many administrators who use “sa” as a high-privilege account in SQL Server. Don’t use “sa” is a recommended best practice to secure your SQL Server. After creating a login, you may need to configure server role for your login, make sure you have clearly understanding of every server-level role and database-level role in SQL Server.
SharePoint content database encryption
Microsoft introduced Transparent Data Encryption (TDE) at the first time in the edition of SQL Server 2008. According to Microsoft, TDE performs real-time I/O encryption and decryption of the data and log files.
In SharePoint environment, only content database can be encrypted by TDE. Although TDE affects the performance of content database, it’s still a recommendation to secure your SharePoint content. I remember Michael Noel – an internationally recognized SharePoint infrastructure guru talked about TDE for SharePoint dozen of times through many international conferences. You can refer to his presentation here and his recording of the topic published in MSDN Channel 9.
This article is a very good start of configuring TDE to encrypt SharePoint content database: http://blogs.technet.com/b/rycampbe/archive/2013/10/14/securing-sharepoint-harden-sql-server-in-sharepoint-environments.aspx
This article gives you basic principle of SQL Server security. As a SharePoint administrator, you need to incorporate with the DBA in your company in order to make a security plan for SharePoint databases.