7 essential SQL Server security tips

How to protect your database from SQL injection, data theft, rogue users, and well-meaning meddlers without tying your environment in knots

Data Security

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.

To continue reading this article register now

7 inconvenient truths about the hybrid work trend
Shop Tech Products at Amazon