Can I use SQL Server credentials as my C# application credentials ?

Question

I have the project to create a C# application for multiple users. Users' credentials will have to be stocked in a SQL Server database table.This database will be used to stock further informations in other tables.

At an application user account creation, his level of permissions on the database will be set.

Knowing that the server/database login/users are already stocked in a system table in the database, can I use this table as my application credentials table too ?

In this case, could it allow to have a unique log for the application and the SQL server.

Thank you.

Answer 1

You might be able to do something like this, but I would advise against it. If it were me, I would do something like this (if I understand your scenario correctly, and just generally speaking....this is of course only one way)

1. Figure out the set of database access levels you want users to have
2. Create a sql user per scenario in sql server and add to the appropriate role to get the access required
3. Create an application role table with an ID column and the credentials for the sql users you created in step 2.
4. Create an application user table which stores user credentials and other information, including a role id which is a foreign key to the table created in 3.
5. In your application, when generating your sql connection/s, you can then use the application role details for the logged in user to create your sql connection string dynamically

Answer 2

The short answer is yes but why would you want to allow users to access the SQL Server, that's the job of the C# application. The basic C# application template should be able to take care of the permission levels. These are generally stored in the aspnet database on your SQL server.

The safest approach is to have a single SQL user for each level of access, which can then be determined by the application, once the user has logged in.

You can easily log the who, what, when and why, by creating a simple logging method in C#, which records every SQL transaction.

(I would have put this in the comments but apparently my reputation isn't good enough ;-)