4

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?

7
+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.

  • 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. – jerik1 Sep 16 '18 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) – SEarle1986 Sep 17 '18 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 '18 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? – SEarle1986 Sep 18 '18 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 '18 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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