July 31, 2019 at 1:00 pm
MSFT documentation indicates that application roles can be used for both types of authentication. In the past, I've only used it for SQL Authentication. My question is, since you need to supply a password to sp_setapprole, how do you do this with Integrated Security? I don't see any discussion of how to do this in my research - only that it "can be done" with Integrated Security.
July 31, 2019 at 3:47 pm
In the documentation, you see that the Application Role is something completely separate from the authentication of the login. If user Joe Smith logs into an instance as either Windows authenticated domname\jsmith or SQL authenticated jsmith, they would still need to run the stored procedure sp_setapprole in order to use the application level permissions setup there. That built in stored procedure sp_setapprole has a parameter for the password:
The password for sp_setapprole would be different than any user's password, "The application then executes the sp_setapprole stored procedure with a password known only to the application."
July 31, 2019 at 6:21 pm
If I'm following you answer correctly, you're saying that the application password is not using integrated security at all, so this is a separate password that has to be maintained.
I have an additional question. If the application generates output files, where do the permissions come from for writing into specific folders on the network? Does this still come from the user that is running the application, even after the application role has been set?
July 31, 2019 at 7:29 pm
Yes, you are correct that the Application Role always uses a separate password, even if the user is logged into the database via Windows authentication. The Windows authentication allows user domname\jsmith to connect to the database, the Application Role password and stored procedure sp_setapprole would give the user permissions in the database he wouldn't otherwise have if he doesn't know the Application Role password.
Any interaction outside of the database such as network folders would be done as the Windows account domname\jsmith, the Application Role only gives permissions within the database.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply