7 essential SQL Server security tips

Like so much of IT, database security requirements largely depend on the situation and environment. Needs may be completely different from one shop to another, even among different servers in the same shop. This is the problem I have with best practices. They give advice without any context, and people follow that advice sometimes to their detriment.

The first thing you need to do when securing a database is to define what it is you’re trying to protect against. After all, how can you know if your security measures are working if you haven’t defined their parameters? This is probably the biggest mistake that gets made in security. I get asked to secure SQL Server boxes all the time, but when I ask these clients what they want to protect against, they typically have no idea. All they know is that their database needs security. Every DBA needs to do a little hand-holding to get stakeholders to list their criteria.

Keep in mind that security isn’t solely about preventing data theft. Sure, that’s a big part, but that’s not the only reason to secure a system. Threats can originate inside or outside the organization, and they can be intentional or unintentional. Your system might need to be secured against theft, or it might simply need protection against having all of its resources eaten up by a rogue query. Or it might need protection from well-meaning developers or Windows admins who think they should make a change to “improve” things.

With these various goals and caveats in mind, I’ll give you a few basic pointers for locking down your SQL Server environment. Some tips will help protect you from breaches, while others will help guard against other abuses. In some cases, you’ll have to think carefully about exactly where to apply which security measures.

SQL Server tip No. 1: Don’t give users view definition permissions

This is critically important if your application is on the Web. A common method used by hackers is to try different entries in Web forms and use the results to piece together facts about your environment. The view definition permission allows users to see the definitions of tables, stored procedures, and views. If an attacker can get that information, then a good deal of his job is done. Never allow this permission on a user account for a Web-facing application.

SQL Server tip No. 2: Don’t return errors

This is another safeguard for Web applications. Error messages can reveal important information about the underlying structure of your database. Again, you don’t want to do the hacker’s job for him. To protect against SQL injection attacks, don’t simply grab the error from SQL and present it to the application. Rather, handle all of your errors and display generic error messages. You would be surprised what someone can learn about your database by forcing error messages for two or three hours. Don’t give them that chance. Handle your errors.

SQL Server tip No. 3: Use strongly typed input variables in your stored procedures

This is another must for Web applications. The biggest threat to Internet-exposed databases is ad hoc queries. Ad hoc queries are built through string concatenation on the front end, then sent to the database. It’s in string building where things go wrong. This is where the injection attacks happen. Hackers can be extremely clever in the techniques they employ to manipulate these strings.

If the variables that you pass into stored procedures are not strongly typed, you’re vastly increasing your chances of falling victim to a SQL injection attack. As a simple example, let’s say your application allows a search on last name. If we type Martinez into the search box, here’s what the query might look like when it’s sent to the back end:

‘Select customerId, firstname, address from Customers where lastname = ’ + ‘’’Martinez’’’

Notice how the application built the string based on your input. Here’s where that can go wrong. A SQL injection attacker will manipulate the field entries and might put in the last name field on the Web form:

‘ OR 1 = 1

With this new form entry, let’s build our query string:

‘Select customerId, firstname, address from Customers where lastname = ’’’ + ‘ OR 1 = 1’

Notice that where we were selecting a specific customer by entering Martinez, now we’re pulling back an entire list of customers because 1 always equals 1. Now our attacker has an entire list of customers to play with.

However, if the parameters we pass into the stored procedure are strongly typed, then this can’t happen. That’s because that parameter is evaluated on its own without further parsing. Using strongly typed parameters, the query that gets run in the stored procedure will be the following:

Select customerId, firstname, address from Customers where lastname = @lastname

The SQL injection version translates thusly:

Select customerId, firstname, address from Customers where lastname = ‘’’ OR 1 = 1’

In this case, unless there’s a customer whose name is ‘’ OR 1 = 1 then nothing will be returned.

This was a basic example to introduce the concept. As you can imagine, these queries and attacks can get very complex. Whenever possible, use strongly typed parameters in your stored procedures and don’t try to do too much dynamic SQL inside the stored procedure itself. Otherwise, you’ll be asking for trouble.

SQL Server tip No. 4: Don’t run all SQL Server instances on the same service account

This one is pragmatic all the way around. If someone were to gain access to the password for your service account, then they would have the keys to the entire kingdom. Here it’s not only outside hackers you need to worry about. Companies are full of well-meaning developers, admins, project leads, and managers who would like nothing better than to get on a system and “make things better” or “look around.” If all of your SQL boxes are running under different accounts, then you can minimize the damage.

On the other side of this is regularly changing the passwords. You should rotate your service account passwords at least every quarter, and I’d recommend doing it more often. It’s a lot easier to coordinate with a single application team on the best time to change a password than it is with 40 application teams.

SQL Server tip No. 5: Restrict the apps that can hit SQL

Here you’re protecting against both internal and external threats. Mainly, by allowing only certain applications to query your database, you will prevent nearly anyone from being able to freely query the data and possibly steal it. But performance is also a big concern. I’ve seen more than my share of systems that were hijacked by someone who wrote a bad query that brought the system to a crawl.

Now, limiting the access of your users to specific applications isn’t straightforward. SQL Server itself doesn’t do anything for you in this area. You’re basically left with two choices. You can use a third-party application that will filter access by application, or you can write something to handle the matter yourself.

The third-party apps are few and far between, and while I can’t endorse any of them here, I can suggest that you search for a “database firewall.”

As for handwritten code, there’s more than one way to go about it. You could write a server-level trigger that checks for the application the user is working with and deny the login. Or you could simply audit the logins and make the users stop connecting through the wrong app. This is more of a social engineering approach to the problem. You’re not physically preventing access, but you can make sure users know they’re being audited and impose actions on them if they don’t comply.

Another way to deal with this situation is to regularly query the active processes running on the server and kill anything that comes from an unauthorized application. This is sort of a hybrid approach. You’re not preempting the access, but rogue users won’t get much work done because every 60 seconds or so their query will be killed. Hopefully they’ll get the point and give up.

SQL Server tip No. 6: Don’t panic over xp_cmdshell

This one isn’t a lockdown method, but I wanted to take this time to dispel a myth. Despite what you might have heard, xp_cmdshell isn’t evil. Nevertheless, there are shops that disallow the use of xp_cmdshell on every SQL instance they have. That’s a shame because xp_cmdshell (which allows you to run any Windows command-line command from T-SQL) can be extremely handy. And it runs under the SQL service account credentials. If you trust SQL Server to honor the security you’ve set for your users, then you can trust in xp_cmdshell.

The problem started back even before SQL Server 2000 when xp_cmdshell shipped open to the public. Anyone could run it. Because xp_cmdshell is so powerful, some real damage was done. Even then you could very easily lock it down so that only admins could use it.

Today, xp_cmdshell comes disabled, so you have to enable it and set permissions for anyone to use. Still, some shops continue to shun it. What these shops are saying, basically, is that they think they can lock down users in their database and apply only certain permissions for them, but those permissions somehow don’t work on xp_cmdshell — like xp_cmdshell has some sort of hidden code that ignores permissions.

Of course that’s nonsense. Xp_cmdshell is a piece of code like anything else, and with the right permissions it’s a valuable tool. Lock it down, definitely, because it’s very powerful and can do a lot of damage in the wrong hands. But don’t cut it out completely or you’ll miss out on excellent functionality.

SQL Server tip No. 7: Don’t take security measures too far

That brings me to my final point: There is such a thing as too much security. As I said in the beginning, you need to have a good understanding of the risks in a specific environment and implement your security with specific goals in mind.

For example, the security settings I’d use for a database exposed directly to the Internet might be completely different from those I’d use for a database sitting behind multiple layers of company security. But I’ve seen shops treat all internal SQL boxes like they were in a DMZ exposed to the Internet. They configured every instance to listen on a different port. They closed all router ports except the ones the instances were listening on. They encrypted all SQL traffic. They implemented IPSec on all servers.

For internal servers that are already behind other security measures, this level of security causes more problems than it solves. For starters, the DBAs in this shop spent more time overcoming ridiculous hurdles. They couldn’t use Microsoft log shipping because the security team refused to allow the two servers to communicate. Instead, they had to write their own log shipping routine that used Secure FTP. They not only had to learn how to write a log shipping routine, but they spent lots of time troubleshooting and adding error handling.

Whenever a new server came on board they had to know which other servers it was going to be talking to so they could put the IPSec rules in place. If anything changed, it took a form, a committee, and pleading to get the change made. Buying monitoring apps was such a nightmare it wasn’t worth the trouble. Imagine bringing on a new app in that kind of shop, one that has to touch all of the SQL boxes. All of the SQL boxes are on different ports with IPSec rules.

This is only one example of security overkill I’ve seen. The security team thought they were doing their due diligence, but what they were really doing was causing undue burden on their shop. Another example is the security guy who issued more than 3,000 certificates because he wanted to encrypt every LUN in his entire environment and decided each one needed its own certificate. These extreme measures aren’t necessary in most shops, and even when they are, they’re only necessary on a small number of servers.

Of course, a lot more can be said about SQL security, but this is a good start. My basic goal here was to introduce you to some of the methods for securing your SQL Server systems, and explain the reasons why you’d take these steps. The most important thing to remember is that you should never blindly accept a best practice. Ask yourself why you’re doing what you’re doing. Above all, create a real list of requirements and know what you’re protecting yourself against. Then test your solution against those requirements — or your security effort will fail when you need it the most.