SQL Server Application Roles

[Jonathan Allen]

Is this the correct way to use application roles?

Public Function GetDBConnection() As SqlConnection
Dim oCon As New SqlConnection(myConnectionString)
oCon.Open()
Using oCmd As SqlCommand = oCon.CreateCommand
oCmd.CommandText = "IF NOT User_NAME() = 'app_name' Exec
sp_setapprole 'app_name' ,'password"
oCmd.ExecuteNonQuery()
End Using
Return oCon

What is a good way to embed/encrypt the application role's password in the
program?

 

[Jonathan Allen]

Allow me to restate my question.

What the hell is the right way to use application roles from VB/C#? When I
try the below code, I get an exception the second time I call
GetDBConnection.

 

[luxspes]

Applications roles work the same way normal users works... IMHO you
shouldnt be trying to create them from you application... you should
define them when configuring your database for deployment (or
development) ... the main (and only?) difference between app roles and
users... is that app roles are "saved" inside your database (while users
are saved in the database server) so if you backup and restore you
database in multiple sqlserver you app roles will continue to work...
while you user might have to be reatached (because their internal id
keys could be different in different dabase servers)...
I hope that helps .

 

[Dan Guzman]

Applications roles work the same way normal users works....

Application roles might appear similar to normal users in some areas but
these are actually very different.

A login is first authenticated at the SQL Server level. If valid, SQL
Server checks to see if the login has access to the requested database by
looking for a database user mapped to that login. If present, access to the
database is allowed to the database under the security context of the
database user. An database application role be activated only after SQL
Server allows database access.

As you indicated, a login/user mismatch can occur when a database is
attached or restored to another server. This problem can be addressed by
either recreating the problem database users or executing
sp_change_users_login to correct the problem. It is naive to assume that
application roles will circumvent the mismatched user problem because not
only is database access not guaranteed, the database could be accessed under
the wrong security context before the app role is activated.

 

[Dan Guzman]

I assume you are using SQL 2000. Application roles have been deprecated in
SQL Server 2005.

You need a single-quote after the password literal. Also, when you use
application roles, consider disabling connection pooling.

 

[Chuck Hawkins]

Au contraire.

Application roles have certainly not been deprecated in SQL 2005. They are
very much alive and well and actually improved. In SQL 2005 you can actually
unset the application role and return back to the user's original context.
This is conveninent in allowing you to move in and out of different parts of
an application with different security contexts. One application role might
have broad rights in one part of a schema while another might have broad
rights in another part. You might have a master control table specifying
which users can walk into which application roles.

Chuck Hawkins