Using Application Roles

By Bill Graziano on 30 October 2000 | Tags: Security Roles

Carl writes "I'm currently working on securing an ecommerce application. I have to use SQL server authentication and have had to set up a large number of users in two different roles. However, I understand that sp_setapprole will allow me to grant role permission only through a specific application, in my case, I only want users to be able to log in through the website and not directly onto the server. I've played around with sp_setapprole but with no luck. By the way, using SQLSRV7. Could you please give me a hint on how to use it through the web application?? Is it safe to stick the connection and call to the procedure in a session variable? All help appreciated. Carl"

SQL Server contains two kinds of roles: standard roles and applicaton roles. Standard roles function like groups. For standard roles, users are assigned to the roles and then permissions are granted to those roles. Both types of roles can be created using the Enterprise Manager.

Application Roles can also be created programatically. For example,

sp_addapprole @rolename = 'MarketingApp', @password = 'yak'

creates an application role called MarketingApp with the password yak. You can change the password using sp_approlepassword and remove the application role using sp_dropapprole.

You don't add users to an application role. First the application connects to the database using a username and password. This may be the guest account or a trusted Windows NT account. Then the application switches to the application role using the sp_setapprole system stored procedure. The code to assume our application role from above might look like this:

sp_setapprole @rolename = 'MarketingApp', @password = 'yak'

Once this command is issued on a SQL Server connection all security is reset to whatever permissions have been granted to the MarketingApp application role. This command overrides any per user permissions assigned. The only way to disable this and revert back to your user permissions is to close the connection and reopen it.

It's also important to note that application roles are database specific. Assume a particular login account has been granted permissions in two databases. They issue an sp_setapprole in DatabaseA. This will remove any permissions they have been granted in DatabaseB. In effect they are no longer themselves. They are the application role.

You have a couple of options on how to handle the password. The first is to store it in plain text in your application and pass it across the network in plain text. On a secure network this may not be a problem. If you want to encrypt the password as it is transmitted to the server you can use this format of sp_setapprole:

sp_setapprole 'MarketingApp', {Encrypt N 'yak'}, 'odbc'

Remember that parameter names are optional. This actually uses an ODBC encryption function to encypt the password before sending it over the network. It can only encrypt a Unicode character field, thus the N preceding the password. And no, you can't use Encrypt to encrypt data in your database.

Unfortunately the password is still stored in cleartext on your web server or in your application. Getting around this is kind of complicated. Bascially you have to encrypt the password and store it somwhere such as the registry or a file. The application only has the key to decrypt the password. When the application launches, it reads the encrypted password, decrypts it and then uses it in the sp_setapprole statement.

I've never used this in a web application so I can't provide concrete examples of that. If anyone else has, mail me your thoughts and I'll update this article. I do know it works pretty well in a compiled executable. It's not impossible to find the password, just difficult. I'm also going to defer to 4Guys on when to use or not use session variables. I just do the database stuff :)

Update:
An alert reader named Jeffrey notes some things to be wary of using Application Roles. He writes:

You should note that the use of AppRoles with ADO is problematic since the approles is strictly a SQL Server concoction that ADO is apparently not aware of or programmed to.

In some cases ADO will create additional connections behind the scenes (especially when connections are 'busied') and since it doesnt know about approles they are not propogated automatically to the cloned connections. As a result, the new conenctions cannot access the database through the approle and would rely on whatever other access permisions the user had.

-graz