August 10, 2003 at 8:53 am
Is there a way of completely eliminating logons using Windows authentication?
I maintain a small development database on a company intranet. I'm using sql
authentication to restrict access to the database. However, this can be
bypassed if a network client connects using windows authentication. I accept
that this is the default and the expected behavior of SQL 2000 windows
authentication - but in our given scenario it defeats the purpose of using sql
server authentication.
This way someone ** not explicitly given login credentials on the database
server by the DBA ** can get thru if....
A. They're administrator of a local machine
B. Administrator on the windows domain
Point A above came as a real surprise, as administator passwords on local
machines aren't secured in our environment - only domain users accounts are
protected.
Also, if **any** host on the network is compromised, it opens up a backdoor to
the database server, as windows authentication is enabled even if you use
MIXED mode authentication.
So, my question is how do we turn off windows authentication completely once
and for all? (Whatever the reason be)
Or in other words, how do we **prevent** users authenticated on a local
machine or on the domain to bypass sql server authentication?
Has anyone else faced a similar challenge?
All help appreciated....
August 10, 2003 at 2:15 pm
You just have to totally remove all NT users/groups as logins. Domain admins are typically allowed access because they are also members of builtin\admins, which also contains the local administrator account. The only way other local admins could get access is if they have been granted access directly - such as machinename\administrator, or as part of some other group - everyone, authenticated users, managers, etc.
Andy
August 10, 2003 at 10:05 pm
If users have administrator access to your machine, then you are not going to be able to secure SQL Server completely, other than for the casual user who connects via isql or EM. As an administrator I can change the registry to let me in in or I can just copy the DB files or backup files and access them. You need to decide whether you are just stopping the administrator from changing live data, or that you don't want them to access the data at all.
The only way to really secure SQL Server is to disable and not use standard accounts.
August 11, 2003 at 7:00 am
Any administrator on your server machine has complete control over SQL Server, including access to stop the service, uninstall SQL Server, copy edit and/or delete all the database files, or (with a bit of poking around) grant themselves access within SQL Server.
You can't secure a database, or any piece of software, without securing the operating system it's running on.
August 11, 2003 at 8:32 am
Thanks Andy
Removing all Windows Users and User groups helped. Now, admins not ** explicitly** granted access are shut out.
declanm, thanks for your reply too. But, I couldn't find where in the registry SQL Server stores Access Permission Lists. To the best of my knowledge, this info is stored in the system tables and not the registry.
I want admins to neither...
A. Access live data
B. Access the DB files/ backup files etc
I think a rogue admin can still access the data/log files and corrupt them manually.
Are there any more backdoors to be shut that I've missed out?
August 11, 2003 at 8:35 am
Microsoft has stated their intent to remove anything but Windows authentication for SQL server so you may have to rethink this strategy.
August 11, 2003 at 8:58 am
Just wondering... How many of you agree with and think it is a smart idea to marginalize SQL (database) authentication in favour of Windows (Operating System)authentication?
I personally don't agree with the philosophy.
August 11, 2003 at 9:11 am
Hi dovidf,
quote:
Microsoft has stated their intent to remove anything but Windows authentication for SQL server so you may have to rethink this strategy.
do you have any links for this?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 11, 2003 at 12:48 pm
NT authentication sucks. What you really want to do is authorize applications to have access to the db, then the app controls access from there. Very rarely do you want to grant someone access to the db with their tool of choice - which is what NT auth offers.
Andy
August 11, 2003 at 2:00 pm
You really don't have to go through all of this.
Run SQL Server under a Domain account, have it set up NON logon.
Remove Builtin Admins.
Leave yourself via NT auth and make that id (or DBA group ID) SA.
Sorry Andy, I go the other way, I would love to shut off SQL Auth, but too many of our apps rely on it.
If your coming in via a concentrator (MTX), Citrix or a Web server you need SQL Auth. But for internal apps connecting with the NT Id makes a lot more sense to me. Otherwise, I log on at your desk, with an ALL User app and away I go. You can still do authing (let's say function permissions) via the app. It just has to know how to get the ID of the signed in user.
SQL Auth / App Auth
No expiration
Not well encrypted (I know you can crack NT too)
No difficulty restrictions
No reuse restrictions
AND the user has to remember another password.
I don't know about you, but I really would love single signon.
KlK, MCSE
KlK
August 11, 2003 at 5:47 pm
If you were building an ASP application on IIS5 / Win2K with SQL 2000, how would you go about offering secure logins? Would you set IIS to Integrated Windows Authentication and create a login for each user (they all have NT accounts).
I am about to do this but am lost at the point of the login.asp. AND, is this the proper/best method of securely allowing users to access SQL Server via ASP over the public Internet?
I have be thinking of accepting their NT userid/password from the login.asp script and then validate it by trying to log the user onto SQL Server? If there is anything the network people need to do, this is where I am probably lost and they'd have to alter the logins themselves, but as far as the DB goes - I can add the logins myself. But do I need to?
I would need to perform auditing so I will need to know who is inserting/updating/deleting records via the USER() functions in T-SQL, etc..
Its the middle piece of authentication where I am very fogged in with and this thread has brightened it up a bit, but still I am confused.
Any ideas?
Thanks!!
Anthony
August 12, 2003 at 3:56 am
Typically web apps use a single SQL login for db connection, if you need to do auditing you either live with the sql login, or pass in the real user name from the web page login (however you handle that).
Andy
August 12, 2003 at 1:45 pm
OK - will consider that approach. The users are getting authenticated via an NT DC so if I keep IIS Intergrated Windows Auth - then I have their userid in the HTTP header throughout their session. Will have to think about this more
Thanks for the info...
Anthony
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply