SQL Extras: SQL Server Security In-Depth: Applications

A tale of two levels of security access.

• By Joshua Jones
• 05/24/2010

Last time, we talked about security from a user's point of view. In this post, let's discuss how applications should authenticate with SQL Server.

Like user logins, application logins need to be closely managed to ensure the minimum amount of permissions are granted while allowing work to continue. So you need to manage two tiers of security for the application; server-level access and database-level access.

For server-level access, you are making the determination of how the application connects to SQL Server. Many third-party applications have already made this choice for you. But if you have any control, make sure you plan accordingly.

Just like users, applications can use SQL Server logins or Windows Authentication to connect. And also like users, the SQL logins will be a simple user name or password, while the Windows Authentication will be a domain account, with the password stored in Active directory instead of the SQL Server.

Typically, if the application will access multiple domain resources, Windows Authentication is best, because it allows for central management of the account. However, Windows Authentication can be very difficult in scenarios where the application server is in a different domain, or in a demilitarized zone (DMZ). In these cases, to reduce the amount of traffic allowed between zones, SQL logins can be used to simplify authentication. This is very typical in Web applications, since the Web server will be in a DMZ and the SQL Server will reside in the back end network. Again, choose appropriately for your environment.

When it comes to determining server level permissions, the vast majority of applications should NOT have ANY server-level permissions. Unless the application makes changes to server-level configuration, or reports on it (such as third-party monitoring tools), an application login shouldn't have any specific rights at the server level. This helps reduce any attack surface from the application, and prevents users from making server changes via the application.

Once the application is authenticated to the server, access to databases must be managed. While user access can often be grouped into functional database roles, application access often must be very granular, to protect data. While there is some logic to using database roles (for flexibility, should a login need to be changed in the future), often times that role will only have one login assigned to it. You can then grant READ, WRITE, and EXEC rights to the role. Typically, in medium to large environments, you may have a few database roles for user access, but many database roles for application access since each application will probably have its own role.

Now there are cases when an application needs to authenticate as a user, but must still have the restricted permissions for that application. In this case, "Application Roles" can be used. Unlike database roles, application roles function like a database user, with its own set of permissions. However, when an application needs to use an application role, the application will connect to SQL Server (as the calling user, usually), and then the application will execute sp_setapprole and pass in a password that only it knows. At that point, the application session now uses the rights assigned to the application role, even though it originally authenticated as the user. In order to revert permissions, application can execute sp_unsetapprole.

Finally, a word about schemas. Properly introduced in SQL Server 2005, schemas are a great way to separate objects within a database by "functional groups", or applications. By using a schema to group objects together, permissions can be granted at the schema level to both users and applications, clearly delineating the lines between application objects. Unless you have many applications all accessing exactly the same objects, consider using custom schemas.

Next time, we'll talk about some advanced methods of securing both connections and data in SQL Server. Until then, have fun!