Understanding the Three A’s of Security for SQL Server

Do you know what the “Three A’s of Security” are and how they apply to Microsoft SQL Server? Let’s look at them as they are important for managing security properly on a given Microsoft SQL Server.


Authentication is determining who a person/process is.

When a connection is made, it’s important to know what account is making that connection. Authentication is the process of validating who that connection is. There are two types of authentication with respect to users connecting to SQL Server:

  • SQL Server’s build-in logins
  • Windows logins

In the first case, SQL Server has stored logins with passwords and if SQL Server is set to mixed mode authentication, these logins can be used to make a connection to a given SQL Server. More on mixed mode authentication in another post. In the second case, a Windows user can connect to SQL Server. This access can either be granted by to the Windows user directly or via a Windows security group.

Obviously, there are times when SQL Server is able to authenticate a connection but that connection isn’t allowed access to any resources, to include simply logging in to the SQL Server itself. That leads us to the next A.


Authorization is determining what a person/process is allowed to do.

Once SQL Server knows who the connection is, the next question is what should that connection have access to? This is authorization. The initial check is to see if the account in question should even be able to log in to the SQL Server. If it does have that permission (you’ll see it as CONNECT SQL if you look at the permissions), the next question is what other permissions should it have. This can be at the server level and it can be within the individual databases.


Auditing is keeping track of what happens for a person/process.

Auditing is the record keeping involved with tracking various security events. For instance, SQL Server can track both successful and failed logins. Typically, it’s recommended that at least failed logins be tracked. SQL Server can, depending on the edition, audit just about anything you want to track. We’ll cover auditing in more detail in later posts.


Encrypt usernames and passwords stored in files

I was looking at a product recently and came across a rather unpleasant surprise: the install instructions specified that I put the database connection in plaintext in web.config. I’ll explore this particular case and why it’s particularly egregious, but from a security perspective, this shouldn’t happen anymore, regardless of application. We have the tools and the processing power where encrypting data of this sort should be a no-brainer.

The Specific Case

When I say web.config, that should reveal that the application is an ASP.NET web application. It is. And modern ASP.NET apps (anything ASP.NET 2.0+) have the ability to store encrypted keys. The keys are stored encrypted in the registry and the way to get the keys are stored in the web.config file. Therefore, there is no need for anything sensitive to be in plaintext.

The way to do this is with the tool aspnet_regiis.exe. Here are the instructions on MSDN for how to use it in Microsoft.NET Framework 4.0. You can hit the drop down for other versions which will take you back all the way to SQL Server 2005 and 2.0. This feature has been around for about 10 years, as this old document indicates. Since it’s been around that long, there’s no good reason not to do this as a standard course of action for a web application.

But What If the Attacker Gets Admin Rights?

This isn’t to say that you have to use this method. It’s free. It works. However, the point is that credentials should not be left in plaintext. Yes, an attacker who gets administrative access to a server will likely have the resources to be able to decrypt and eventually get the information. However, this is a relatively easy step that makes things harder. It’s a low cost defense. Therefore, it should be implemented.

It Also Means the Username / Password Doesn’t Show Up via Searches

The other thing that encrypting the username and password or even the whole connection string is it means searches of files for this kind of information fail. It’s a trivial thing to run a search and see if you can get some additional nuggets like a username/password combination. Therefore, most smart attackers do these kinds of things. When I pen test a system, I certainly do. Encrypt and the search won’t pick up the information. That’s a good enough reason in and of itself.

Speaking at Midlands PASS Chapter tonight

The Midlands PASS Chapter is an official PASS (Professional Association for SQL Server) chapter located in Columbia, SC. It’s free to attend our meetings, which are typically held the 2nd Thursday of each month.

Once a year we like to do an open forum on SQL Server security. It’s typically held in February, but was postponed due to the inclement weather. Therefore, we’re holding the open forum tonight, March 13, 2014, from 5:30 to 7:00 PM. The first part of every meeting is meet and greet to give folks time to network. Then we settle in for a presentation, or in this case, a forum discussion.

The SQL Server security open forum is as it sounds: folks are free to bring up whatever they want to with regards to SQL Server security and as a community we’ll try to take things apart and come up with the best answer. While I may focus on SQL Server security, I don’t have all the answers. None of us do. That’s why a few years ago we went to this more free form discussion.

If you’re in the area and would like to attend, please drop on by. We meet at Microstaff IT in Cayce, SC at 440 Knox Abbot Drive (tower with Bank of America logo on the top). If you look on Google Maps, the address is marked wrong. Google Maps is pointing at the shopping center right next to the tower, but the parking lots are connected.

A summary of the SQL Server security #datachat is live

Recently I posted about participating in a #datachat about SQL Server security. As it turned out, we didn’t talk about SQL Server security, but data security. It was a good discussion with quite a few knowledgeable folks joining in. A summary of the discussion including some highlighted tweets can be found here:

Four Critical Challenges in Implementing Data Security: A Conversation with SQL Server and Security Experts

The #datachats are community open forum discussions. You just have to know when to show up on Twitter! To see what topics are coming up and when they’re scheduled, check out the #datachat schedule.

Auditing VMware vCenter Actions (on SQL Server)

When you’ve got a SIEM appliance or application, you want actions and events going into it as a central repository. That allows you to see patterns and hopefully track incidents across systems. As a result, if you want to track actions in VMware’s vCenter and you’ve got the database hosted on SQL Server, you need to give access to the following two tables:


A standard tendency is to give the account that the SIEM product is using membership in the db_datareader role. Don’t do this. It’s a violation of the Principle of Least Privilege. It’s far better to create a user-defined role and granting it the appropriate permissions. Then make the account a member of the role. For instance:

USE VirtualCenterDB;

CREATE ROLE [Auditor];


And then you make the user account being used by the SIEM product a member of the Auditor role.

The weakest link in database security

The weakest link in database security is the same as for most all IT security: people.

Because the weakest link is always people, we have adopted a principle called The Principle of Least Privilege to determine how we should assign security. If you’ve never heard of it, it’s a basic concept with some easy to follow rules:

  1. Give enough rights to do the job.
  2. Don’t give any more.
  3. Don’t give any less.

#2 is particularly relevant. It’s easy to violate this, especially when SQL Server is concerned. If you’ve ever heard, “Just give the application db_datareader,” you’ve heard a violation of this principle. The same is true with db_datawriter. Why is this a violation? Let’s ask a few basic questions.

  • Does the application (report / user / etc.) need access to all tables in the database?
  • Does the application (report / user / etc.) need access to all view in the database?
  • Are you sure the application (report / user /etc.) needs access to the catalog views (the system objects) for the database?

Often times, the risk is understated: “Well, so-and-so is completely safe. He would never do anything wrong with the data.” Yes, that particular person may be safe. Then again, life changes may alter that person’s morals and ethics. It’s amazing how much major events like a divorce, like medical bills, like gambling debts, like a new drug habit change those things. But let’s say the person is completely safe. Are you safe? No.

The problem isn’t the person. It’s the person’s user account. If someone were able to run under the context of the user account, he or she can do whatever the account can do. Give the account too many privileges and you’ve got a problem. If someone is willing to compromise an account, they are likely going to have few qualms about swiping important data.

See, it’s relatively hard to directly attack a database server and in a trusted environment. It’s much easier to slip a trojan in to an email or mail a link which leads to the malware being installed on the user’s computer. And then, BAM!, the attacker is in as that user and the attacker now has the user’s access to the database. If you’ve given too much access, that’s bad.

Therefore, given the weakest link in database security, the best practice is to follow The Principle of Least Privilege. Don’t leave it until the end and try to retrofit. You’ll likely be forced to rush and that’s when people cut corners with db_datareader and db_datawriter. Instead, get it attacked at the start of a project / development effort. Get it right from the beginning.


Security #Datachat on Twitter Tonight

Tonight, at 9 PM Eastern, I’ll be participating in a #datachat on SQL Server security. It’s sponsored by Confio (now part of Solarwinds).

You can find more details about the #datachat here.

How can you participate? Simply open up a search for #datachat and participate in the community Q and A. The more, the merrier!

I hope to see you online.

Previous Older Entries


Get every new post delivered to your Inbox.

Join 3,748 other followers