How is the sp_setapprole used?

• New_to_being_DBA

  SSCrazy

  Points: 2064

  More actions

  December 28, 2006 at 9:13 am

  #176220

  I'm trying to create an application role for my application. All the info I read about it states:

  The following steps make up the process by which an application role switches security contexts:

  1. A user executes a client application.
  2. The client application connects to an instance of SQL Server as the user.
  3. The application then executes the sp_setapprole stored procedure with a password known only to the application.
  4. If the application role name and password are valid, the application role is activated.
  5. At this point the connection loses the permissions of the user and assumes the permissions of the application role.

  Now how is the stored procedure executed?  Thru the application? Before the connection string?  Run separately before the application is executed? I'm not really sure how this is done. 

  Do I need to add this to my asp.net and if so, where?

  EXEC sp_setapprole 'role', 'password';GO

  Also, is there a way to encrypt the password since I'm using SqlClient and it does not support the encrpy option?

  Should sp_unsetapprole be executed when closing the application or does it happen on it's own when the application closes?

• January 1, 2007 at 8:00 am

  This was removed by the editor as SPAM

• MohammedU

  SSCoach

  Points: 19644

  More actions

  January 1, 2007 at 1:10 pm

  #680677

  http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/1eddb33e5e116759/cab63c1c579ea15b?lnk=st&q=sp_setapprole+password+encrypt+SqlClient+&rnum=1&hl=en#cab63c1c579ea15b

   

  MohammedU
  Microsoft SQL Server MVP

• Scott Coleman

  One Orange Chip

  Points: 27452

  More actions

  January 3, 2007 at 12:20 pm

  #681020

  The application role is set for the connection that executes sp_setapprole, so the application must start executing and open a connection before it can activate the role.  Think about it.  "sp_setapprole <assume current connection>" makes sense, but "sp_setapprole <some other connection>" would be a security nightmare.  "sp_setapprole <a connection that hasn't been created yet>" wouldn't work, another application would likely open a connection and inherit the role by mistake before your application could do so.

  Since this is an asp.net application, connection pooling is probably being used.  If you activate the application role for the connection, it will affect any other thread that is sharing the connection.  One way to prevent this (without turning off all connection pooling) is to add something unique to the connection string for the application role connections, such as a different Application Name attribute.

  You don't have to unset the application role before disconnecting.

  I don't know a foolproof way to encrypt the password in the stored procedure command, although BOL recommends using the crypto API to encrypt the password if it is persisted in the application and using SLL or IPSec to transmit the command with plaintext password over the network.  You could probably put the 'EXEC sp_setapprole' command in a stored procedure and then at least the password would only exist on the server.