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\u2019re trying to protect against. After all, how can you know if your security measures are working if you haven\u2019t 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\u2019t solely about preventing data theft. Sure, that\u2019s a big part, but that\u2019s 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 \u201cimprove\u201d things.With these various goals and caveats in mind, I\u2019ll 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\u2019ll have to think carefully about exactly where to apply which security measures.SQL Server tip No. 1: Don\u2019t give users view definition permissionsThis 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\u2019t return errorsThis is another safeguard for Web applications. Error messages can reveal important information about the underlying structure of your database. Again, you don\u2019t want to do the hacker\u2019s job for him. To protect against SQL injection attacks, don\u2019t 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\u2019t give them that chance. Handle your errors.SQL Server tip No. 3: Use strongly typed input variables in your stored proceduresThis 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\u2019s 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\u2019re vastly increasing your chances of falling victim to a SQL injection attack. As a simple example, let\u2019s say your application allows a search on last name. If we type Martinez into the search box, here\u2019s what the query might look like when it\u2019s sent to the back end:\u2018Select customerId, firstname, address from Customers where lastname = \u2019 + \u2018\u2019\u2019Martinez\u2019\u2019\u2019Notice how the application built the string based on your input. Here\u2019s 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:\u2018 OR 1 = 1With this new form entry, let\u2019s build our query string:\u2018Select customerId, firstname, address from Customers where lastname = \u2019\u2019\u2019 + \u2018 OR 1 = 1\u2019Notice that where we were selecting a specific customer by entering Martinez, now we\u2019re 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\u2019t happen. That\u2019s 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 = @lastnameThe SQL injection version translates thusly:Select customerId, firstname, address from Customers where lastname = \u2018\u2019\u2019 OR 1 = 1\u2019In this case, unless there\u2019s a customer whose name is \u2018\u2019 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\u2019t try to do too much dynamic SQL inside the stored procedure itself. Otherwise, you\u2019ll be asking for trouble.SQL Server tip No. 4: Don\u2019t run all SQL Server instances on the same service accountThis 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\u2019s 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 \u201cmake things better\u201d or \u201clook around.\u201d 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\u2019d recommend doing it more often. It\u2019s 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 SQLHere you\u2019re 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\u2019ve 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\u2019t straightforward. SQL Server itself doesn\u2019t do anything for you in this area. You\u2019re 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\u2019t endorse any of them here, I can suggest that you search for a \u201cdatabase firewall.\u201dAs for handwritten code, there\u2019s 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\u2019re not physically preventing access, but you can make sure users know they\u2019re being audited and impose actions on them if they don\u2019t 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\u2019re not preempting the access, but rogue users won\u2019t get much work done because every 60 seconds or so their query will be killed. Hopefully they\u2019ll get the point and give up.SQL Server tip No. 6: Don\u2019t panic over xp_cmdshellThis one isn\u2019t a lockdown method, but I wanted to take this time to dispel a myth. Despite what you might have heard, xp_cmdshell isn\u2019t evil. Nevertheless, there are shops that disallow the use of xp_cmdshell on every SQL instance they have. That\u2019s 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\u2019ve 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\u2019t work on xp_cmdshell -- like xp_cmdshell has some sort of hidden code that ignores permissions.Of course that\u2019s nonsense. Xp_cmdshell is a piece of code like anything else, and with the right permissions it\u2019s a valuable tool. Lock it down, definitely, because it\u2019s very powerful and can do a lot of damage in the wrong hands. But don\u2019t cut it out completely or you\u2019ll miss out on excellent functionality.SQL Server tip No. 7: Don\u2019t take security measures too farThat 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\u2019d use for a database exposed directly to the Internet might be completely different from those I\u2019d use for a database sitting behind multiple layers of company security. But I\u2019ve 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\u2019t 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\u2019t 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\u2019ve 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\u2019t necessary in most shops, and even when they are, they\u2019re 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\u2019d take these steps. The most important thing to remember is that you should never blindly accept a best practice. Ask yourself why you\u2019re doing what you\u2019re doing. Above all, create a real list of requirements and know what you\u2019re protecting yourself against. Then test your solution against those requirements -- or your security effort will fail when you need it the most.