User accounts in C# and sql server 2000
Hello everybody
I have a rather open ended question concerning application design. Specifically security.
I am currently writing my first (serious) windows forms application in C#, which runs against an sql2000 database.
Up until now I've only ever written web based applications. Almost all of these apps have connected to their databases with one SQL Server login, one password. The login name and password were located in the code on the web server somewhere, and the same details are always used to connect to the database, regardless of who's using the app. Once connected, I've always controlled access to the app with my own set of user details in my own table.
And so far this has always worked fine.
Now that I'm writing my first client app I'm starting to wonder about the best ways to handle user accounts.
At first I began writing the app with the intention of creating one sql server login for each of my users. When the user enters his/her user name and password into my app, these would be the details used to connect to the database. I also planned to still maintain my own user table, but to keep the user name and passwords in sync with the actual sql server logins.
However, I'm finding it difficult to maintain the sql server logins from within my app. If you're logged in as a normal user you can't change any details because you don't have access to the master database. I could create some sort of "super user" account within the app that does log in with more access, but even then when it comes to changing details I'm stuck with dropping logins and re-creating them each time a change is required. I'm sure it's not impossible but it's just messy.
I could of course go back to using just one login for the database and then controlling access within my app with my own table of users, as with my previous web apps. However I don't like the idea of this as it means distributing the main database password along with the application.
I could hard code it into the application, which would then get compiled, making it difficult for people to read it. But this would make changing the password very difficult, since I'd have to re-compile the app and redistribute it each time we wanted to change it.
Holding it in a text file (or similar) that was distributed with the code would make it easier to change at a later date, but this is not very secure for obvious reasons.
A third option of course would be to use windows authentication. This is the option I like the least. I have to be able to manage the user accounts from withing my application. As far as I can see, using windows authentication makes this very difficult.
I'm simply wondering if there are any better ways to do this that I am overlooking, or if anybody has any useful thoughts on the subject.
Apologies for the length of my question. I hope I have made myself clear.
Thanks in advance for any advice.