CODEWITHSUNDEEP
c#sql-serversql-server-2008desktop-application
Ibrahim Najjar
Ibrahim Najjar

Reputation: 19423

Controlling access to SQL server database file with C# desktop application

I am developing an accounting application using C# with SQL Server Express 2008, the application only accesses the database locally. i want the customer to enter a user name and password when he installs the application, that way he is the only one who can run the application, and my question is:
1. should i store the log in credentials in a local file, or create an sql server user account with those credentials and rely on the database keeping them a secret ?
2. if the second option is doable, how to do it from the installer package ?

Upvotes: 0

Views: 593

Answers (2)

Tony Hopkinson
Tony Hopkinson

Reputation: 20330

There's a lot more to this. If whoever installed sql server selected sql server security that user (sa) will have been defined and will have access to everything.

If windows only then it will be the windows user who installed sql server, and they will have access to everything

If Mixed, both will have access to everything

Then which user created the database on the server, they'll be dbo by default and have full access to their database.

So it all depends on who's doing what.

Does your app intaller call the sql server install? Does it create the database?

Admin and dbo, can both grant access to said database to anyone they like.

Adding the windows user installing the app is simply a matter of getting your installer to execute a sql command or two. At that point you have Single Sign on. No need for password you are adding the windows user and mapping them to a database role/user.

If you want a username and password any user could use if they know it, then perhaps sql only / mixed mode is the way to go. Again a couple of sql commands executed from your chosen installer to caete user fred password ?, job done.

The point to remember is in order to execute the instructions to add logins and users, roles etc, you need to connect as someone else with permission to do that, e.g. windows admin or sa.

Also most application users on managed networks do not have admin permissions, so you could have a bit of fun round that as well.

I suggest you have about of a read about sql server security, there's a few ways to skin this cat, only you have enough info to choose the best option.

You might even want to have a think about application based security, if each install of your application creates it's own database and role....

Upvotes: 1

Kestami
Kestami

Reputation: 2073

You could do something like, store database settings, such as IP, Port, Username and password login attempt credentials locally, perhaps in an XML file, but then store the login credentials for the program in the database.

Note: Obviously i don't know how much you know about security, but here's a few tips:

1)Don't store the password directly in the database. Use a hash and some method of encryption. 2)Check the hash of a given password a user attempts to log in with, with the database stored variable you created for the program, which should also be a hash.

Basically, at no point should the database know what the actual password is, only a given hash that you passed it, and can compare to in the program itself.

With regards to the database access, I would create a user which has access to only the schemas you want it to. From there, get the logged in user in your application / program to log into the database with the credentials in, for example, an XML file. It's also an idea to encrypte these details in the XML file too, so they can't be tampered with locally.

Upvotes: 1

Related Questions