-1

I'm looking for a bit of guidance or someone to point me in the right direction.

I have a C# WPF desktop application which accesses a local SQL Server Express database. The application is on a single computer with a generic login and not connected to the internet.

Users enter a username and password to log into the application. I have been giving everyone their own SQL Server Login. I use the SQL Server Logins to manage permissions with the database tables etc. but I intend to use stored procedures in the future. Approximately 30 people use this system.

Is going the SQL Server Login route the 'proper' way to do things given the circumstances? Is my method of managing users and permissions acceptable?

Thanks in advance

Basile Starynkevitch
  • 32,652
  • 1
    Do users access the database directly? – José Amílcar Casimiro Aug 19 '19 at 08:28
  • Users currently access the database through the WPF application. I have set up roles to limit what users can do. Eventually I will be using stored procedures. –  Aug 19 '19 at 09:19
  • As users do not directly access the database, I do not see the need to create a database server login per user. Access to the database is through an account that only the application knows. – José Amílcar Casimiro Aug 23 '19 at 08:57

3 Answers3

1

I would recommend only having an application level log in with enough permission to run the application and manage further security for individuals in the app itself. If the database needs to be accessed outside the app, but through an internal network, then you need a solution to handle user accounts, in which case they are best managed by using active directory groups. This lets you set permissions once and ensure everyone gets the same access. Usernames and passwords for the app shouldn't just be SQL logins, your database shouldn't be your identity/access management. Group based access management should be the goal for how SQL access is handled.

You can specify a username/password for an application level SQL user, and you can log who does what based on the app login name. This does expose access to your database for anyone that looks for it, you can encrypt files to make it a little harder, but depending on the access of the generic account this may not be useful. You could hard code the SQL user into the app, this is generally a bad practice though, but your use case is somewhat exceptional. You could also simply give the generic windows account SQL access, this is an easy solution, but it is probably the most vulnerable to a bad actor. Another possible option would be to force everyone to use their own windows account this would make a lot of security concerns easier to manage and would be easier to scale up to a networked solution if needed.

Ryathal
  • 13,396
1

Users enter a username and password to log into the application.

On Windows, it's more customary to assume that, since the User has managed to log themselves into their own computer to Windows' satisfaction, then they can connect to the application "as themselves", with no further authentication (that they're aware of).

In this case, you'd be looking at a "Trusted" connection into the database, with the user set up at the database end with a Windows account, not a SQLServer one.

The other major downside of SQL Server accounts is that Users will forget their password and, having done so, have locked themselves out of the database and, therefore, cannot use any application-provided "password reset" capability, assuming you've written one.

Another alternative is to bake SQL Server credentials into the application. These are never disclosed to Users and are used to make the connection on their behalf. These credentials confer enough privileges to make the application work. Again, it removes the need for "extra" User authentication and, because you've hidden the credentials away, Users cannot connect to the database using their favourite, ODBC-compliant Reporting program and rummaging around in things that don't concern them.

I use the SQL Server Logins to manage permissions with the database tables

Good start. I'd suggest building on this by looking at using Groups (essentially, Role-Based Access Control).

Set up [small numbers of] Groups with the right levels of permission and then add the right Users to the right Groups.

Don't create permissions for individuals.
Instead, create permissions needed for someone to do their job and then, if that person does [decide to] leave, then their successor can get the exact same permissions, simply by being added to the same Group.

Phill W.
  • 12,181
0

I have a C# WPF desktop application which accesses a local SQL Server Express database.

Your user could provide (somehow, perhaps as a wired-in default, in some environment variable, some program argument, some WinRegistry entry, etc...) a textual file (readable only for that user uid) containing the SQL server credentials (login & password) information, then your program would parse that textual file at startup time.

You might consider (as a replacement for Microsoft SQL Server Express) also using sqlite (see also this) or some open source RDBMS server (like PostGreSQL, which also runs on Windows and is interfaced to C#). Both sqlite and PostGreSQL could be better open-source alternatives (more reliable, consuming less computing resources, multi-thread or multi-process so multi-core friendly, and easily deployable -with some command-line driven package manager and/or build automation software- to a lot of desktops) to Microsoft SQL server (but they both accept a different variant of SQL and provide a different API).

Details are obviously operating system specific (so read this about OSes). So study the WinAPI.

PS. I never used any Windows systems (only Unix and Linux and before that MS-DOS & VAX/VMS), but I am coding since 1974.

Basile Starynkevitch
  • 32,652