Part 1 | Part 2
Many organizations are in danger of a security breach, regardless of the measures they are taking to secure their environment. In database systems, a breach in any part of the overall system can be exploited to gain access to critical information.
To properly secure SQL Server, it is important to consider the following layers that make up SQL Server security:
-
Network security (firewalls, ports, encryption)
- Operating system security (Windows security)
- Server level security (endpoints, server level logins, ports, protocols, and other surface area configurations)
- Database level security (granting rights to logins/roles, encryption options, and determining what rights are appropriate when)
Since a security failure at any one of these layers can mean failure for an entire solution, there really is no point in securing individual object levels if an opponent can merely sniff everything going "over the wire" or if the operating system is breached. Therefore, all the layers need to be secured.
Part one of this series explains network and operating system security in regards to SQL Server. Database and server-level security will be discussed in part two.
Network security
Although network security specialists are primarily responsible for network security, there are certain SQL Server configuration options that interact with this layer.
Network security specialists are mainly concerned with what ports are turned on and the protocols that are used. Discussions should occur between network security specialists and SQL Server administrators about whether each SQL Server should have a different port or if they should share a common port. (I strongly advise against the standard 1433 port because there is a high likelihood of it being hacked.)
<...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
p>
In the SQL Server Configuration Manager (Figure 1), the SQL Server Network Configuration tab contains the controls for the protocols/ports in use for the instances (S2K8 is an instance).
Figure 1 (click to enlarge)
[IMAGE]
This is the default configuration for a developer box. For a SQL Server Enterprise Edition, the only protocol that should be on is TCP/IP. All other protocols should be disabled unless there is a unique application requirement for them on the client side.
There are several important properties in the TCP/IP Properties tab. The Listen All property, shown in Figure 2, determines what ports the SQL Server instance will listen on.
Figure 2 (click to enlarge)
[IMAGE]
If this property is set to Yes and there are multiple IPs, the SQL Server instance will listen on all IPs. If there is only one IP, then the setting could be left alone.
If the server has multiple IPs, however, the Listen All property should be set to No, and the SQL Server instance should be set to listen on the IP needed. The more IPs an instance is listening on, the more potential breaches could exist.
Figure 3 shows how to limit the SQL Server instance down to a specific IP address. If the Listen All property is set to No, then the Enabled property applies. Enabled should be set to Yes only for the IPs that are necessary.
Figure 3 (click to enlarge)
[IMAGE]
The specific port that is being listened on can be set by either the TCP Dynamic Ports or the TCP Port settings on each IP or on IPAll.
In Figure 4, IPAll with dynamic ports is set up. This allows a port number to be changed any time SQL Server starts, depending on what ports are available.
Figure 4 (click to enlarge)
[IMAGE]
In dynamic ports, the SQL Server Browser Service monitors the ports in use and directs incoming connections to the current port for a given instance. Dynamic ports are a default setting for named instances.
Dynamic ports are turned off and static ports are defined for IPA11 in Figure 5.
Figure 5 (click to enlarge)
[IMAGE]
Note that IP ports can also be set per IP address, and individual IP addresses can listen on multiple ports if they are listed comma delimited (1600,1700).
Operating system security
While most of the Windows OS security is handled by Windows itself, many SQL Servers have the BUILTIN\Administrators group with SysAdmin rights by default. This gives any Windows administrators on the physical box SysAdmin capabilities on the SQL Server.
Generally, this isn't a best practice since typically the production DBA teams are separate from the team(s) that maintain the production Windows servers and may even be from different companies. It's a very good idea to remove this default Windows group after you ensure that you know the SysAdmin password or have other accounts with SysAdmin.
Overall, always consider how the various systems in an environment are interconnected and could be used to exploit the entire environment.
Coming soon: Part two on database and server-level security
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.
