June 21, 2005 at 5:25 am
Hi,
My company has recently invested in a SQL Server setup and decided (!) that I am DBA, VB coder and by the way can we have this new project by the end of June =(
The project needs individual logins (users will be able to log in as themselves from any machine regardless of who is logged in to the windows terminal, I know, I know, but you try telling them!)
Therefore I would like some clarification on my options. As far as I can tell there is SQL Logins (that have no inbuilt expiry, sensible restrictions on length or complexity for passwords) or having a table in the DB that conatins a list of user names with sensible passwords and use my application to verify their existence and restrict password choices.
Am I missing a super third option?
If not, surely using SQL logins means that I (as DBA) would have to be responsible for creating users, resetting password etc, but using the DB table option I lose the use of triggers to record audit trails.
Is this correct?
Any comments gratefully received.
Colin
June 22, 2005 at 1:14 am
if your users don't need to connect to the sql server directly then you have both options open.
if you store the password in a table you may need to consider encrypting them depending on the sensitivity of the contents protected by logins. I prefer this way becasue you have the total control and you can create triggers to maintain a audit trail as well. But eaither way yo have to write code to maintain the logins so that thye support password hints, reset password, password policies etc.
If you use the other option, which is sql login, then you can add them to SQL roles and give different permissions and monitor their activities on the profiler etc.
But still you have to maintain set of tables to keep the password hints, password policy information, probably along with the sql username. this way you will have all the features plus sql login advantages.
But with this method they will have option to connect to the sql server directly.
this is just a quick thought came to my mind, others may have better ideas.
June 22, 2005 at 1:39 am
The best option is to persuade the powers that be that the user logged into windows is the one that connects to the app so that you can use windows authentication giving you no extra work.....but i guess youve already tried that route!
June 22, 2005 at 1:54 am
I think that we'd all agree with andy that Integrated Security would be the preferred option if available/possible. Depending on your development tools/expertise however it is possible to change the user context of the current connection using the LogonUser api call (i've managed to do it under asp.net) within your application therefore allowing a trusted connection "under the hood" regardless of whoever is logged on through windows on the host machine.
Don't know if that's of any use but it's another option anyway.
June 22, 2005 at 7:10 am
I lean towards creating my own tables for storing user IDs, passwords, permissions, etc. However, depending on what you're writing your app in you may be able to leverage SQL-DMO objects for working with user accounts. You could probably use a combination of SQL-DMO and your own tables for tying SQL logins with permissions settings, etc. However, off the top of my head I don't recall a way to create a function in your app using SQL-DMO for users to change their passwords...
June 22, 2005 at 7:34 am
If you have to implement your own login, which is how anonymous authentication works for web apps, we normally:
1. Login to the database with a single SQL login (dont use sa) but make sure that the app looks up the user name and password from a login screen on startup.
2. Encrypt the passwords so that somebody browsing the database cant see the contents and/or at the very least restrict select access to the confidential tables.
This means you can control the password expiry etc through your app without any clever SQL eg store the expiry date in the userlogin table that holds passwords. Your startup code can check the date etc.
June 24, 2005 at 2:36 am
Thanks to everyone for your contributions. At least I understood the problem, even if I couldn't find a way around it!
I am currently looking at Noggin's suggestion of the LogonUser API. Although it only works on XP (I develop on a W2K machine) it looks like that will provide the best security. No user maintenance for me, user accounts are maintained by a separate group! A single domain group account can be given access with certain users as Admin level.
Thanks again, I hope every stupid question I ask is answered as quickly and with such politeness.
Colin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply