Problem solve Get help with specific problems with your technologies, process and projects.

Database security options to protect data in SQL Server

Encryption and data separation in SQL Server are not easy or cheap methods. But customers want security controls in place to protect their sensitive data. In this tip, contributor Kevin Beaver shares alternative tools and techniques to protect against hacker attacks.

With all the hype stirred up by high-profile database breaches and the dozens of privacy and security regulations, there's a new how-to dilemma for SQL Server developers and DBAs coming on strong from owners. They're asking:

  • How are you protecting sensitive data in the database?
  • What encryption method are you using for your database?
  • How are you separating our data from that of others?
  • We have lots of different clients – can you encrypt each data set? (the worst question of them all)

    As wacky as these questions seem, they're being asked, and you'll need to know how to respond whether you're an independent software developer, work for a large enterprise, or fall somewhere in the middle.

    Many of those requesting database separation and encryption are going into this blindly. They're getting pressure from auditors, managers, salespeople, or customers who don't understand how databases security works. Many believe separating data sets and enabling database encryption are as easy as flipping a switch. They don't realize what DBAs and developers are up against. There are performance requirements, code re-writes, necessary system upgrades, implementation of third-party controls, system maintenance, and so on. Needless to say, all of this comes at a price which is often a hard pill to swallow for the very people demanding it.

    I've never been an alarmist when it comes to database separation and encryption, but there's no denying the facts; The database is where the gold lies and the bad guys are going to go for their highest payoff when attacking your systems. If a database is not adequately protected, so much is at the attacker's disposal.

    You've got a lot of options for database separation and encryption. You can upgrade to SQL Server 2005, write your own homegrown encryption mechanism, or deploy a third-party encryption system. You can also:


    • Create a unique database for each customer (ouch!)
    • Setup a unique application and database server for each customer (can you say $$$!?)
    • Setup (and manage) different user accounts for different databases and tables (yuck!)
    • Establish a unique encryption key for each user so that only they can access their data stored in the database (way easier said than done!)

    But none of these completes the whole picture. There are so many variables involved. The problem with most database security techniques is that if the front-end application or authorized SQL Server account can access and decrypt sensitive data, then so can an attacker who's performing SQL injection or who may have broken in by cracking a weak password. For the most part, this applies regardless of whether you have a single database on one server or multiple databases spread across various systems. Bottom line – if your front-end applications are weak, the sensitive data in your database is still going to be at risk.

    What I'm trying to say is that database security controls such as separation and encryption aren't going to be easy or cheap. One thing's certain though – it's almost guaranteed that if you're storing sensitive and personal data in your database, people are going to start asking what you're doing to protect it. You don't want to get caught off-guard with this.

    Look at what would be involved in implementing these additional database security controls. Explain to them your approach, how you're partitioning each database, encrypting sensitive information and so on. Or, give them the reasons you're not. Follow up with other controls you have in place that reduce the need for database separation and encryption such as input filtering, strong authentication, and even whole disk encryption in the event your entire database server or hard drives are stolen. Also, don't overlook the value of ongoing penetration tests utilizing good tools combined with manual assessments. There are also formal database security audits using tools such as AppDetective and NGSSQuirreL, and even source code analysis tools such as those offered by Compuware, Ounce Labs, Fortify Software, SPI Dynamics, and Klocwork for your applications.

    More on


  • Harden code to prevent SQL injection
  • Ten IIS tips to lock down SQL Server
  • Tool to configure and lock down SQL Server 2005 services

Next Steps

Spectre and Meltdown bugs threaten SQL Server performance

Dig Deeper on SQL Server Security