Application Security Design - Part 1 - Authentication for SQL Server

By: K. Brian Kelley | Updated: 2017-03-03 | Comments (3) | Related: > Security

Problem

I'm part of a new development effort which will use a SQL Server back-end. We want a secure database layer. What is the best way to architect/design this?

Solution

This is a big topic, so let's break this down into multiple tips for easier understanding. First, let's talk about authentication (this tip). How will the application and its users connect to SQL Server? Our design starts here. Let's start with the first decision point:

The application connects on behalf of the end users with its own set of credentials.
The application connects as the end user.
A mix: the application connects with its own credentials, but the users need to connect directly to the database as well.

The Application Connects with Its Own Credentials

If the end users should never have access to the back-end database, this means the application connects on behalf of the users. This is the typical means for web applications when the Windows security context (the Windows account itself) of the user isn't needed. When would you typically see the application passing on the user credentials? SSRS is a good example from the SQL Server suite. Often times, reports run and access data under the context of the user, because this is important to determine what they have access to with respect to the data.

I mentioned web apps, but it is possible to have the application connect with its own credentials even when we're talking about an application installed on a user's computer. The issue is being able to somehow securely store and retrieve the credentials (username/password) to use in a way that the end user can't get access to them. Because of this challenge, it's more typical to see an application connect as the user if the application is installed on the user's system. We'll look at that more in depth in a minute. Let's continue to looking at the application connecting with its own credentials.

Can a Windows Account Be Used?

If the application is running on a computer in a trusted domain to the SQL Server, then the preferred method is to use a Windows domain account. If you're not familiar with the term "trusted domain," it's actually rather simple. If you can configure the Windows account to connect to the SQL Server, whether the account is in the same domain as the SQL Server or not, the account is in a trusted domain.

Why do we prefer Windows domain accounts over SQL Server logins? First, the account is managed in Active Directory. If there's an issue, it can be disabled or deleted in Active Directory and then it doesn't work anywhere. If the account is being used to hack a system, after a certain number of failed logins, it'll lock. With proper auditing, those failed logins will be reported and incident response can begin. It's easier to find the failed logins in the Windows security event log and a lot of organizations pull both the logon successes and failures into a central security system for tracking and analysis. With SQL Server, the events can be written to the application event log, but that often involves additional setup because SQL Server doesn't write the failures to the Security event log, as Windows does. One could parse the SQL Server log, but there's a lot of other information in there, such as when backups ran, that there's the signal-to-noise ratio is poor.

Also, if an application had to connect to multiple SQL Servers, that's not a problem. The account can be added to each one. If we were using SQL Server logins, the logins would have to be created on each server and we'd have to make sure the password was the same. Also, if we had to update the password, like due to a password age policy, we'd need to touch each system. With Active Directory, we can touch it in just one place. From a management perspective, it's easier all around.

What If We Can't Use a Windows Account?

In this case we're forced to use a SQL Server-based login. While the recommendation is for Windows domain user accounts, using a SQL Server login is not uncommon. If you're doing so with your application, you'll want to make sure password policy is enforced. This puts the same password requirements and tracking of failed logins as for Windows domain user accounts. This results in a consistent password and logon policy across systems. Keep in mind that just like a Windows account, you can set the password not to expire while honoring the other parts of the password policy. This is a typical configuration, whether we're talking SQL Server logins or Windows user accounts functioning as "service accounts." We typically set not to expire because the password policy we enforce with respect to how old a password can be is too short to be practical for systems that need to stay up. If that's the case with your application, make note of it and enable password policy but set the password not to expire.

Also, the application will need to store the credentials in a secure fashion. When Windows accounts are used, such as a web application through an application pool or credentials referred to in an ASP.NET application's web.config, the OS is able to handle storage and decrypting the password in order to connect to the database as that Windows account. With a SQL Server login you can't use those processes. Therefore, the credentials will have to be stored securely and the application will first have to retrieve them before attempting to connect to the database.

What If the Application Needs to Know Who the User Is?

While we cover this in more depth in the next tip on the subject, passing who the user is back to the SQL Server isn't an unusual requirement. In this case, though, the application has to handle everything. The data access will have to somehow be tied to the end users identity. Also, the application will be responsible for verifying who the user is. Since SQL Server is only going to see a connection based on the credentials the application uses, it can't validate the user. Keep this in mind as you design the application.

The Application Connects as the User

What if the application connects as the user? This can be true for web applications, especially when Kerberos authentication/delegation is in play, as well as applications installed on the user's computer (or on in an app hosting situation like Terminal Services or a 3rd party product). In this case the user's account (Windows) will need access to connect to the SQL Server. This raises the question: should you use Windows users or Windows groups.

From an ease of management perspective, the preferred method is definitely Windows groups. Windows groups typically are used for more than just one type of access. For instance, if an application is for the Marketing department, you can probably re-use Windows security groups that have already been created for the Marketing department. These groups could already be used to grant access to other applications, file shares, etc. Therefore, when a new person is moved into Marketing (new hire, department transfer), the user is added to the appropriate groups and has access everywhere needed. If someone moves out, then the user account is removed from the groups (or deleted from AD if the user is no longer with the organization) and access is lost. As a result, managing access by groups is easier when you can do it. There are always exceptions, but you should try and use Windows groups.

What about Multiple Windows Groups?

It's entirely possible that you'll have to give access to multiple Windows groups and this is fine. It could even be that different groups tie to different access levels. For instance, if the folks in marketing are dividing into print advertising, social media, web site design, and market analysis, that could be four sets of Windows groups. And for a particular application, all four groups need access but different permissions within the application itself. This is easily done, especially as we build roles within the database, a topic for a later tip.

The bottom line is that you don't have to have just one group with access. Multiple groups work fine.

Multiple Means of Access

If the user shouldn't be able to connect to the database except through the application or should have different rights, then you will likely have to look at application roles and the application will have to support them in order to meet this requirement. We'll discuss this in a later tip.

What about SQL Server Logins?

While this is supported, we tend to stay away from SQL Server logins for individual users. There's the scalability problem for management/administration, if users have to access more than one SQL Server we have to create logins on each one (and delete them when they are no longer needed), and there's a security one. The security one comes into play in the following scenario: for whatever reason, a user's Windows account is deleted or disabled intentionally. If the user could get access to a system which can connect to the SQL Server, the user still can get to the data until a DBA disables the SQL Server login. If there's a delay, the organization is at risk. How might a user get access? Maybe the user slips into the chair of someone who didn't lock their system or who left their password on a sticky note... this still happens. If Windows user accounts are used, as soon as it's disabled the user can't connect to SQL Server any longer.

The Application Connects and the Users Connect, but not Through the Application

There are cases when you'll get a mix: the application uses its own set of credentials to connect but the end users also need to connect as themselves, just not through the application. The typical scenario where we see this is when we do have a case where we want the application to make a connection, but users need access to some data on their own, such as for reporting purposes. We're looking strictly at security here, but be advised that if users are using the same tables to report against as the application does read/writes to, you could have blocking/locking/deadlocking. So while that's a common scenario, approach such a setup with caution and plenty of thought about performance.

In this case the application's credentials are given access and usually it's set up with a particular database role that has all the permissions needed. In a later tip we'll get into how to determine the different roles for an application and how to divvy up security as a result. But if the end users shouldn't have the same access, you'll need to ensure they have different permissions. Again, you still want to use Windows groups for the end users where you can. Basically, you want to follow the practices we've already talked about earlier in this tip as it applies to each scenario.

Next Steps

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2017-03-03

Application Database Security Design - Part 1 - Authentication for SQL Server