Application Roles are useful when you want to restrict direct access to the database for a user or group of users, but you want them to use a specific software application to do that. The minimum user login permission that is required is only a possibility to connect to a database. The application connects to a database with a user credentials and after that informs Sql Server about its application role credentials. Sql Server then forgets the user context and takes the permissions of application role.

But it is always good to see it in action, so lets open a Microsoft Sql Server Management Studio and do some queries.
For the purpose of this example I assume we already have an existing database AppRolesTest , and a Products table in it.
In SSMS under our database Security section you will see a node called Application Roles. We could create one using built in wizard, but instead I will show you how to do that as TQSL query. So let’s create new query in SSMS and connect to our database with system administrator (or the database owner).

   1: CREATE APPLICATION ROLE ClientAppRole 

   2: WITH PASSWORD = 'start', DEFAULT_SCHEMA = [dbo]; 

   3:            

   4: GRANT CONTROL ON DATABASE::AppRolesTest TO ClientAppRole;

In line 1 and 2 the application role called ClientAppRole is created. Then in last line we grant a control on database to the application role we just created, so the application that connects with this role have full control over the database.

Now lets see how the application role works. Lets create a new Sql Server user login user2 and add a user mapping to database AppRoleTest with public role. We can click that out in SSMS from the main server Security/Logins node. Then lets create new query and connect to our database with user2. Since user2 has practically no permissions on our database running this

   1: SELECT * FROM Products;

will result with error

The SELECT permission was denied on the object 'Products', database 'AppRolesTest', schema 'dbo'.

That’s good. User can’t query our database which was absolutely intended. Now we will enable application role on the connection the current query holds. We can imagine that a query tab is kind of separate application. In fact a application role is scoped within a connection to the database and must be called per each connection once. Then since each query tab has its own connection we can use an application role in it.
To activate a permission associated with an application role we must use a stored system procedure called sp_setapprole

   1: EXEC sp_setapprole 'ClientAppRole', 'start';

After calling that the application role permissions overwrites the user permissions, so now our user2 has full control over database.

Despite obvious benefits of application role, there are also some downsides. One is fact that the application itself needs to store credentials to application role. Obviously the more credentials to handle the more possibilities to compromise it.
Another downside is, what I mentioned before, fact that application role stays active within a opened connection to the database. That basically means, from one side, that application must activate the application role just after each connection to database is opened. If our application uses many connections it must sends the application role credentials after opening each of those. And here we comes to the serious problem using application role. It does not work with connection pools, well at least I couldn’t find any working example that uses application roles with application that uses connection pools. Because of that we cannot simply use application rules with Entity Framework. Of course there is a possibility to disable connection pooling in the connection string

Data Source=TEST-PC\SQLEXPRESS;User Id=user2;Password=start;Initial Catalog=AppRolesTest;

Pooling=false

However besides fact that disabling connection pooling has serious performance impact in for instance ASP.NET web applications, the EF seems to handle the opening and closing connection itself internally anyway which causes such runtime errors

A severe error occurred on the current command. The results, if any, should be discarded.

when using application roles.
The only way we can safely use Application roles with EF is when we can manage the connection absolutely by ourselves. Thankfully EF can be instantiated with a externally owned already opened connection, so we can simply do something like this

   1: var connString = @"Data Source=TEST-PC\SQLEXPRESS;User Id=user2;Password=start;Initial Catalog=AppRolesTest";

   2: using (var conn = new SqlConnection(connString))

   3: {

   4:     conn.Open();

   5:     SetApplicationRole(conn);

   6:  

   7:     db = new AppContext(conn, false);

   8:     var products = db.Products.ToList();

   9: }

where

   1: public static void SetApplicationRole(SqlConnection connection)

   2: {

   3:     var query = @"sp_setapprole 'ClientAppRole', 'start'";

   4:     var sqlCommand = new SqlCommand(query, connection);

   5:     sqlCommand.ExecuteNonQuery();

   6: }

We simply create a sql connection, open it, set the application role and serve it to the EF context telling that we owns the connection. This approach has quite rare usage though. Avoiding application pooling brings a noticeable performance hit caused by expensive database connection creation.

Application roles has a potential as a perfect way to manage the access to the database for specific application only, not for specific sql users. Unfortunately both biggest ORM framework which is Entity Framework and NHibernate does not natively supports this feature, which is bad and is probably a main cause that application roles are not widely known and used.

Additional read: SQL Server Central, SQL Mag