Common SQL Security Scenarios

Security plans can change depening on whether you're looking to implement in an SMB, multi-server, or enterprise environment..

• By Joshua Jones
• 06/14/2010

Until now, we've just been looking at each of the security layers in SQL Server as individual features. Now, let's take a look at how these features are often combined to protect your systems' data.

SMB Environment
Generally, small and medium businesses that have a SQL Server use them for the storage of data for third-party applications, such as accounting or inventory software. In many cases, the SQL security scheme is dictated by the application. Typically, these applications will use a SQL Server login that has ownership rights to their database. In many cases, the installation software will ask for the 'sa' password, only because the installer needs the rights to create the database and associated logins.

Because of this, administration of security is fairly simple: Someone in the organization will own the password information for the 'sa' account, and there won't be many other logins besides those application accounts. If there are logins, whether or not they are SQL logins or Windows Authenticated logins will depend entirely on whether or not there is even a domain present on the network. Some shops that use a "network suite," i.e. Microsoft Small Business Server, will have a domain setup. In that case, a single Windows user should be designated as the "administrator", and that account should be granted 'sysadmin' membership. Then, the 'sa' account can be reserved for emergency use.

Typically encryption is only used when a specific application calls for it, so most of these systems won't ever have encrypted data. Nonetheless, normal security measure should be taken:

• Each user should have their own login. If any users access the SQL Server directly with a query tool such as SQL Server Management Studio or even Microsoft Excel, they should use their own logins to help keep access secure. Sharing of logins can become very tricky, since some users may have more access than others.
• The 'sa' account should never be used by users or applications. In most cases, users and applications should have their own logins, even if they need sysadmin rights. Inevitably, if the company grows, the applications will too, and you'll get into a situation where many people who manage the application will have system administrator rights on a SQL Server that they shouldn't. This account should be used only by administrators, and only when necessary.
• The server should be physically secured. While this is almost a no-brainer in larger shops, many times small companies will have their SQL Server literally sitting on a shelf in a random office or conference room. Servers should at least be locked in a closet where physical access is restricted to those people with a key. This will help prevent malicious intent as well as any accidental issues (wouldn't want someone crashing the SQL Server that holds the company's financial data when they spill their latte on it).

While all of these will apply to the following categories as well, they should be the bare minimum of security thoughts in a small shop. In terms of usage, the SQL Server will probably only house internally used data; most companies this size outsource things like Web site hosting and customer relationship management software. As such, firewall rules are rarely needed in these situation; the SQL Server should be secure on the network with no outside access.

Multi-Server Environment
Once an SMB has grown to the point of needing more than one SQL Server, this often means at least one dedicated administrator. Even if this person is the entirety of the IT department, this person will be responsible for ensuring that the data is secure. An environment that has grown to this size also tends to have application developers who are responsible for anything from Web sites to payroll software. Additionally, some employees will need to regularly review and report on the data.

In this situation, administrators need to follow the rules above (in terms of user and application access). However, the use of database roles to help separate "business users" from "developers" will help keep permissions tidy. This is also the point where application logins may start to use application roles in databases in order to more tightly control access.

You'll also need to consider whether or not there are "backup" administrators, and be sure to manage those folks with their own logins. If an administrator wants to allow a specific developer to help manage a server, for example, then instead of giving that person the 'sa' password or simply making their login a member of the 'sysadmin' server role, it might be sufficient to grant that login membership to the 'serveradmin', 'securityadmin', and 'dbcreator' roles. This will allow that backup administrator to perform common functions while keeping them from making massive system wide changes.

In terms of data security, this is the point where encryption will likely take place. Application developers are usually well versed in connecting to secure systems; however, it would be wise for the DBA to review the code being written once in a while to make sure connections are secure. Also, this type of environment is a good place to consider encrypting data on disk, as long as it doesn't affect performance. Typically, this will be personally identifying information, and data such as credit card numbers, etc.

Finally, it will be necessary to begin configuring firewall rules to allow traffic to the SQL Server from outside of the network, such as from Web servers. Here are some things to keep in mind:

• The default TCP port used by SQL Server is 1433. The UDP port 1434 is also used. While it is perfectly normal to simply configure the firewall to allow traffic inbound and outbound on these two ports between the web server and the SQL Server (never just allow "any" host), these ports are often targeted by viruses and hackers. Consider changing your server to use non-default ports. This is done in the SQL Server Configuration Manager (see Fig. 1).
       Simply expand the SQL Server Network Configuration Node, and then click on the Protocols for <INSTANCENAME> node (in my case, the default instance, so MSSQLSERVER). Then right-click on the TCP/IP entry in the main console pane. Finally, click on the IP Addresses tab, and change the port numbers as needed on your IPs. Save this, and then restart the SQL Server. Now you can configure your firewall to communicate on your custom port. Just remember: This will affect how applications connect to the server internally as well, so consider having an "internal" and "external" IP on the server, to help reduce confusion.
• Consider how to manage your 'sa' accounts and administrative accounts. With multiple servers, you can choose an individual 'sa' password for each, or make the 'sa' password the same across the board. The choice is yours, and neither is really better than the other, depending on your situation. Just be sure to set a policy and stick to it. As far as other administrative accounts (such as backup admins), be sure you take into account whether or not the data on a given server is so sensitive that only specific people should have administrative access. You have to remember that not all data is created equal; the server that houses your Web forum data may not need to be as secure as the server hosting your employee payroll information.

Figure 1. Change ports via the SQL Server Configuration Manager. (Click image to view larger version.)

Large Enterprise
Finally, ,any companies in this category may have dozens, or even hundreds, of SQL Servers. In this case, there is usually a team of DBAs, tasked with monitoring and managing servers based on business unit or functionality. For these shops, all of the rules above apply. Hopefully, by the time a company has reached this size, the rules above were ALREADY followed by a DBA (or two), and security is built in. The only additional piece of advice for this size of a network is that security audits need to become mandatory. Often times developers and DBAs take shortcuts under deadlines from management. Its easy to choose not to encrypt data, or to grant a login too much access in the light of getting the project done. However, the DBAs need to A) know what exceptions have been made and why, and B) make sure nothing unexpected slips through the cracks.

If these very basic principles are followed, the vast majority of systems will be secure. Of course, there are always exceptions to these rules; as long as they are carefully planned out and documented, you'll have plenty of defense against those nasty auditors the next time they come around. Have fun!