Part 1 | Part 2
SQL Server security is made up of multiple layers: network, operating system, server-level and database. These layers need to be hardened individually because a breach in any one of them could mean failure for an entire solution.
Network and operating system security were discussed in part one of this series. This section explores database and server-level protection.
Server security properties
In the server properties tab, a "server proxy account" can be specified for use by xp_cmdshell. While I strongly advice against it, if you do plan on using xp_cmdshell, I recommend putting in a proxy account with very low privileges.
Specifically, there are two noteworthy settings under server properties: Enable C2 audit tracing and Enable Common Criteria compliance.
Enable C2 audit tracing allows the tracing of object access -- both success/fail -- to the data drive, which -- unless controlled -- grows until the server shuts down.
Enable Common Criteria compliance causes memory to be overwritten before it is reallocated. While this causes a more secure system, memory usage will be slower. Furthermore, the option also changes login auditing and permission sets. As a result, before turning this option on, it is important to be careful with the permissions design in the database as well as the performance requirements.
SQL Server SSL security
SQL Server Configuration Manager controls any SSL usage.
If a certificate is installed, right-click Protocols for ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
S2K8 and select Properties. The following screen will appear:
Figure 1 (click to enlarge)
[IMAGE]
Under the Flags and Certificate tabs, it can be specified whether or not an encryption is forced.
In a highly-secure production system, it is best to force the encryption. I recommend setting the Hide Instance option to Yes for production systems. This is because allowing the SQL Server browser service to locate an instance in production makes it easier to locate and hack a production SQL Server.
Furthermore, in production environments it is best to avoid a self-signed certificate because it could be breached by a man-in-the-middle attack – an attack where active eavesdropping is used by making independent connections with each victim and relaying the messages between them. In Dev/QA environments, this will depend on the security levels needed with the systems being worked on.
SQL Server endpoint security
When an endpoint is created, ensure that the state is only started if necessary, use a non-standard port (notify the network group of the port along with the protocol TCP involved) and allow/require encryption.
Only the necessary ports should be open, and only for the required protocols. Assuming the network is not encrypting traffic, I recommend setting the encryption to "required". In the case below, the other endpoint would have to be set to required (or supported).
CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 8035 )
FOR DATABASE_MIRRORING</font> (
AUTHENTICATION = WINDOWS KERBEROS,
ENCRYPTION = SUPPORTED,
ROLE=ALL);
The next step is to grant the right to connect to the endpoint. I recommend not using basic authentication due to security issues. You should also try to limit the connection access to the fewest logins possible. Preferably, use logins that are either specific to this endpoint or have very narrow usage in the enterprise.
GRANT CONNECT on ENDPOINT::endpoint_mirroring TO [Domain\ConnectingUser];
GO
SQL Server service account security
Every SQL Server service should be installed with a different Active Directory (AD) account. There should never be a reason to give a SQL Server service AD account Domain Admin.
This is typically done because people get lazy and sacrifice security for ease of use. Each SQL Server service should use a different account because each service uses different functional areas requiring different permissions to be properly locked down. In other words, your SQL Server service AD account probably does not need permissions to access the Internet or the ability to login interactively.
In database systems, a breach in any part of the overall system can be exploited to gain access to critical systems. Therefore, it is important to consider how the various layers – network, operating system, server, and database - are interconnected.
ABOUT THE AUTHOR:
[IMAGE]
Matthew Schroeder is a senior software engineer who works on SQL Server database systems ranging in size from 2 GB to 3+ TB, with between 2k and 40+k trans/sec. He specializes in OLTP/OLAP DBMS systems as well as highly scalable processing systems written in .NET. Matthew is a Microsoft certified MCITP, Database Developer, has a master's degree in computer science and more than 12 years of experience in SQL Server/Oracle. He can be reached at cyberstrike@aggressivecoding.com.
