|  | | Moving Sql Server Code | |
| Oct17Written by:RBarryYoung
10/17/2009 11:31 AM 
An interesting question came up on the MSDN SQL Forums yesterday about best practices for applications to access their databases on SQL Server: In what situations will application roles suffice, I have read about how it works, but in applications that I have seen, e.g. an exe which expects a servername as a parameter, a username and a password. How will an application role work here.
In what applications/scenarios have other SQL server experts used application roles.
If SQL server login is to be used, what is the best practice approach of how this should be managed?
In order to answer this question, it is necessary to first understand that a "SQL Server Login"and an "Application Role" are two different things. A SQL Server Login, is a SQL Server-only account (i.e., not a Windows Login/account) that is used by supplying a username and password to SQL Server when connecting. Because such a SQL Server Login is created just for this application's use, and is used only by this application, they are typically called "Application Logins". They are popular with applications because 1) they provide a way to authenticate and authorize just the application, irrespective of the user, so that the user cannot access the database on their own without the application. And 2) they can be externally parametrized and thus managed and modified externally. Its downsides are that gaining access to the application is tantamount to gaining access to the applications data, and even worse, gaining access to the executable image and/or executable environment gives hackers the ability to strip the application login's username and password out and use it on their own (because the Window account using it is never checked by SQL Server). This is all in contrast to a Windows or Domain Login that uses a Trusted connection that needs no validation on connection. Typically in Client-Server architectures, this is the Users domain account that the client application is piggy-backing on to connect to the database. Authorization in the database is implemented by adding the Windows domain account or a Windows group that the application user are in (usually a group created solely for this purpose) as a Windows Login in SQL Server and then as a User in the application database. User/Group authorization of windows logins for applications access to SQL Server database is popular because 1) it takes virtually nothing in the application code to implement it, its all up to Windows and SQL Server, and 2) access to the application, or its image/environment will not give you access to the data because you have to be in the right Windows Group before SQL Server will give you that access. Its disadvantages are that the users can access the database (and therefore usually, the data) all on their own without ever having to use the application (because SQL Server is *only* checking the Windows Login/Group and requires nothing else). An Application Role, on the other hand is a way to "sort of" combine the security advantages of both of these approaches. An Application Role is a database-only principal (that is, non-server level, so it cannot normally cross databases) that is intended to be used by an application AFTER it has successfully connected to the database. The Application Role is invoked by using the command: EXEC sp_setapprole "rolename", "password", which causes SQL Server to drop the connections current security context and assume the Application Role's. The idea here is that the application's users' Windows Logins (or Windows Group) is authorized by SQL Server to connect to the database, but *nothing else*, it has no access to anything else in the database (tables, views, application stored procedures, etc.). This allows the application to use Trusted connections to the database based on the authorization of the user. At that point the application runs sp_setapprole to switch to the Application Role in the database, which is authorized to do everything that the database needs to do. The advantages of this approach are that 1) Authorized users cannot access the application data on their because they do not have the approle's password. And, 2) access to the application, image or environment, does not enable a hacker to gain access to the SQL Server or the application's database, because you must still be in the right Windows Group to connect to the database. The disadvantages are that this approach is complicated and harder to implement (both in code and in the DB) than the other two. Even worse, because it can only be implemented explicitly in code, it cannot be retrofitted in the field onto an existing product, application or other executable through external configuration, the access code has to be rewritten. Copyright ©2009 Barry Young Tags:4 comment(s) so far...
Re: Best Practices for Applications Login to SQL Server? Barry,
Once again great article. This topic recently came up at work again and i wish i had read this article before we went through that. We ended up going with roles and the users were added to the roles via AD.
I can attest to it being a complete pain in the rear retrofitting the old permissions to match the new requirements but it is doable.
Take Care
By dan white on
10/22/2009 8:18 AM
|
Re: Best Practices for Applications Login to SQL Server? Thanks, Dan.
By RBarryYoung on
10/23/2009 1:04 PM
|
Re: Best Practices for Applications Login to SQL Server? What I typically recommend when you have an application connecting on behalf of the user is the use of a domain user account specifically designed for that purpose. Typically we call these "service accounts" but there's no real difference between an end user's domain account and a "service's" domain account until you get to Windows Server 2008 R2 Active Directory.
In any case, it ensures that you can audit the login/logout cleanly, especially failed logins, that you can audit account lockout at the DC (which should already be happening, anyway), and that there's one location for managing security (Active Directory). The latter is key because you can easily disable the account's access to multiple points (such as if there were multiple SQL Servers, file shares, etc. which the application needed to have access to), you can determine all access points by auditing just one system, etc. And you also have the capability to use Kerberos authentication, which permits the application to validate the SQL Server, prevents replay attacks, and permits for delegation scenarios, if that becomes necessary (such as with a linked server connection where a Windows credential is passed on).
By K. Brian Kelley on
11/3/2009 2:33 AM
|
Re: Best Practices for Applications Login to SQL Server? Brian: In this scenario, how would the Domain account be invoked (logged into, etc.)?
By RBarryYoung on
11/10/2009 10:53 AM
|
| |
|
|