Application Roles: Not as Useful as They Could Be

At one time or another, most application programmers have struggled with common security problems. For example, let’s say you want a particular user to have one set of permissions when he accesses the database through a custom application, but a different set of permissions when he accesses the database through an ad hoc query tool such as Microsoft Access. This seems easy to set up, but it isn’t! If you try to use SQL Server’s integrated security, then the user will connect as the same SQL Server user, whether he connects from the custom application or an ad hoc query tool. This makes it difficult, if not impossible, to enforce custom application business rules when users connect from an external query tool. You’ll face equally challenging problems using standard security. You can easily give the user two separate SQL Server login accounts with different levels of permissions in the database. But how do you prevent the user from using the account with permissions to log in to SQL Server outside of the custom application?

SQL Server 7.0 seems to solve this problem with the addition of application roles. SQL Server 7.0 application roles let the database administrator restrict user access to data according to the application that the user is using. Application roles allow the application to assume responsibility of user authentication.

When an application makes a connection to SQL Server 7.0, it executes the sp_setapprole stored procedure, which takes two parameters: username and password. You need to implement password protection to prevent a mischievous user from issuing sp_addrole manually from an ad hoc tool. SQL Server drops the existing permissions assigned to the user, and the user assumes the security context of the application role.

Unfortunately, the addition of application roles creates one fundamental problem: You must run sp_approle for each connection. However, many middleware data-access layers such as ADO abstract connection management for you. What happens if ADO opens a new connection based on the activity you’ve requested? What happens if the application is using ODBC connection pooling? In either case, the application probably won’t run sp_setapprole properly for all the necessary connections and your security model will be shot. Application roles are a good idea, but they have limited value until Microsoft creates ODBC and OLE DB interfaces that have direct knowledge about managing connections that use application roles.

How can I move a database between servers?

You have three basic choices. You can use Data Transformation Services (DTS), backup and restore, or attached databases. You should bone up on these three system stored procs in SQL Server Books Online (BOL): sp_attach_db; sp_attach_single_file_db; and sp_detach_db.

These new commands make the process of moving a database between servers much smoother than it is in SQL Server 6.5, because in SQL Server 7.0 you can detach a database from one server and then attach the database on a remote SQL Server. The best part is that in SQL Server 7.0, the database is nothing more than a normal Windows NT file—or a series of files for you purists—while it’s detached. That means that the source and target servers don’t need to be synchronously connected. You can e-mail, ftp, telnet, or move the files from one location any way you want. Very cool. Of course, you still need to be careful if you’re moving databases between servers that use SQL Server login authentication. In some cases, users might end up being mapped to different login accounts on the new server. Read about sp_change_users_login in SQL Server Books Online (BOL) for information on managing this problem. Windows NT login authentication makes the database transfer process much smoother, as long as you’re moving the database between two SQL Servers in the same Windows NT domain.