8

I've been reading up on application roles in Microsoft SQL server. It seems an application role is like any other role in that permissions on securables can be granted to an application role, however, the difference being that it has to be "called" by an application using the stored procedure sp_setapprole.

The only reason I can see for using this is for a DBA to allow an application (and its developers) to connect to the database and not having to supply a server login or a database user and therefore the developers cannot connect directly to SQL Server (via SSMS for example) even then, I can't really see a benefit to this as surely the account provided to the application / developers would be granted the minimum required permissions anyway?

Am I missing something here?

Improve this question
1

1 Answer 1

Reset to default
10
+100

Application roles do not eliminate the need to supply initial connection credentials. One must first successfully authenticate to the server/database using a login (SQL or Windows account/group), or contained database user) before an application role can be activated.

Application roles mostly differ from regular database roles in that they:

  • have no members
  • are enabled using sp_setapprole with the app role password after successful authentication
  • are unset using sp_unsetapprole with the cookie returned by sp_setapprole (required for connection pooling)

There is no value in using an application role when a service account (with the permissions needed by the application) is used for application database access.

A use case for application roles is when users connect to SQL Server from the application using their own security credentials (e.g. Windows authentication via Windows group membership) but do not have the permissions the application requires. Activating an application role in this scenario allows auditing of individual user activity (e.g. using ORIGINAL_LOGIN()) without granting permissions beyond CONNECT, although users could be granted permissions for needs outside the application, such as those needed for a reporting tool. This would allow read-only access for reporting while limiting writes via the application.

Improve this answer
5
  • As an implementation / benefit of the Application role imagine this scenario: Every user of our SW (ERP system) is login on SQL Server (or member of NT Group). Basically every user is db_owner in the database. If this is undesirable due to the need for higher security, we are using Application role. We downgrade rights for users to public role and the Applicaton role is the only having db_owner rights. This will ensure average user can't use other application to access the database.
    – jericzech
    Sep 16, 2018 at 18:03
  • 1
    "A use case for application roles is when users connect to SQL Server from the application using their own security credentials (e.g. Windows authentication via Windows group membership) but do not have the permissions the application requires" What I don't understand is, why not just give the relevant permissions to the underlying account (the windows group in the case of your example)
    – SE1986
    Sep 17, 2018 at 14:45
  • 2
    @SEarle1986, consider the case where the app code inserts, updates, and deletes but end users only need read-only access from outside the application. App roles would allow the writes from the app but not ad-hoc by end users.
    – Dan Guzman
    Sep 18, 2018 at 1:57
  • @DanGuzman that makes a lot more sense now. To implement that then, the user account would be given read only access but would need to use the (higher privileged) app role in the app? In which case, the application would need to be written to execute EXEC sp_setapprole?
    – SE1986
    Sep 18, 2018 at 8:01
  • @SEarle1986, yes, the app code would execute sp_setapprole (and perhaps sp_unsetapprole before closing connections) to elevate permissions. This is transparent to users. Like other secrets (e.g. login passwords), the application role password should be stored securely.
    – Dan Guzman
    Sep 18, 2018 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.