Setting up App to access SQL database using SQL Application Role

Question

The App in question (C#, WPF, MVVM, EF) uses an SQL database as its Model/Datasource. It will have several users who will run the App from their windows desktop from within the business network. So for each user the App will need to connect and read/write to the SQL database. There is no user login process written in the App. I expected that users could run the app and make use of what I have read regarding a SQL Application Role where I dont have to create the users with individual SQL logins/permissions or individual logins in the App, but that the App will connect using the Application Role which will be recognised by the SQL database and allow connection, provide necessary permission for the CRUD tasks.

I have looked at an old codeproject article on Application Roles that provides an example that

1. Application Role provides a level of security at the application to control access to database objects.
2. Application Role once set lives for the life of the current connection to the database.
3. The system stored procedures sys.sp_setapprole and sys.sp_unsetapprole are provided to enable and disable application role.
4. sys.sp_setapprole returns a cookie that can be used to unset the application role.

So I am looking for some advice on how this situation (App users running App and successfully accessing SQL database through the App) is typically done and type of coding or SQL setup I need to look into to enable

thanks
Scott

Answer 1

Good day,

There is no user login process written in the App...

In order to authenticate a user and "recognize him" there must be one or another way of login procedure. Roles for users can only apply AFTER the user is signed in.

SQL Server supports two authentication methods: (1) using "Windows authentication" or "Active Directory authentication", and (2) using User name and password pairs which are maintained within SQL Server.

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/authentication-in-sql-server

Which one of these options fits you?

In first glance seems like you want to use Active Directory authentication. This way the user just need to connect the domain and all his activities will be done under the Active Directory authentication

Check if this tutorial help you:

https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-ver15

Answer 2

Hi @scott thomson ,

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 enabled.
5. At this point the connection loses the permissions of the user and assumes the permissions of the application role.

The permissions acquired through the application role remain in effect for the duration of the connection.

Please refer to MS document Application Roles and the blog How Application Roles Work in SQL Server to get more information.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.