Win/SQL Authentication for a web environment...
Andrew Connell
a web environment... both for a very large and relatively small scale (two
difference scenarios)... both are public web domains (not restricted by NT
authentication. Currently, it seems as the common practice is to use SQL
authentication as the restriction manner.
However, I'm seeing more and more of a trend where people say that SQL
authentication is only in SQL 2000 for backwards compatibility and instead,
you should use NT authentication. I have a few concerns with this:
1. If it's a site that could have thousands or tens of thousands of
concurrent users, wouldn't that place a significant burden on the domain
controllers?
2. You are basically giving up all options of locking down the application
based on the login because you will be forced to use the IUSR (or equiv.)
for all web sessions.
It does seem that NT authentication would be more secure, it just seems that
I'm giving up quite a bit to use it as well as possibly taking on a big
network performance hit. Would the implications of a NT/2K based network
have any implications? What about Active Directory?
Comments?
-AC
<old>MCDBA (SQL7)
Andrew Connell
was thinking about doing the same thing that you mentioned. Sure does seem
a whole lot more secure. If I understand you correctly, you created the web
accounts in your domain, not in the local web server boxes. Makes sense!
Thanks!
-AC
"Ryan LaNeve" <rla...@NOaviincSPAM.com> wrote in message
news:eU0inBpXCHA.2544@tkmsftngp11...
| <inline>
| "Andrew Connell" <sp...@aconnell.com> wrote in message
| news:#Bt4FLmXCHA.1748@tkmsftngp08...
| > <snipped>
| > 1. If it's a site that could have thousands or tens of thousands of
| > concurrent users, wouldn't that place a significant burden on the domain
| > controllers?
|
| I can't really speak to this, but I can't imagine the domain controllers
are
| getting hit on every request (or even every session). I'm certain they're
| not if you use the IUSR_<machinename> account, as that is an account which
| is local to the webserver itself. Since it's not a domain account, why
would
| your domain controller be involved?
|
| > 2. You are basically giving up all options of locking down the
application
| > based on the login because you will be forced to use the IUSR (or
equiv.)
| > for all web sessions.
|
| Why? There's no reason you have to use the same account for each web app,
or
| even the same account for every resource in a single web app.
|
| Let me tell you how we're doing it, as we went through the exact same
switch
| from SQL Security to Windows Authentication across more than 2 dozen sites
| running on 5 different web servers (+ internal sites and custom apps). We
| have a general "anonymous web user" account in our domain. This account is
| used for any public website which we do not wish to restrict in any way.
85%
| of our sites run under this account, and this account is a valid login on
| our database server. We then have a few additional domain-level
"anonymous"
| accounts, each specific to a single site (extranets). These are then added
| as logins to our database server and given the appropriate permissions
| within the related extranet's database. Beyond that, we use Windows
| Authentication on our Intranet sites which allows users from all of our NT
| domains to access those sites and be authenticated within our database
| server under the related "<domain>\Domain Users" group, all of which were
| added to the database server. We then added each of those to a single role
| within the database server to make granting permissions easier for the
| Intranet sites.
|
| Over the course of 9 months, we've moved from 100% SQL Authentication on
our
| database servers to 90% Windows Authentication, with the last 10% being
| converted in the next 6 weeks. There are only 3 SQL Login accounts on our
| database servers after this changeover: the "sa" account, an account for a
| 3rd-party app from a vendor who refuses to alter their code, and one
| additional account which we're removing soon.
|
| In summary - use Windows Authentication. It's more secure and you won't be
| "limited" in any way, though 3rd-party apps may require the use of SQL
| Authentication, thus preventing you from completely disabling access in
that
| manner.
|
| Good luck,
| Ryan LaNeve
|
|
|
Ryan LaNeve
One note - we created the web accounts as domain-level accounts for a couple of
reasons, but if you don't need to do it I would recommend going with a
machine-level account as someone gaining control of the account would have far
fewer possibilities to wreak havoc if the account was not able to access any
machine other than the web server itself.
Some things that are easier to maintain if you use a domain-level account would
be:
- you want to use the same account for multiple machines;
- you want to maintain a "stand-by" server in the event your production
server(s) fail;
- you want to make resources on another machine within the network available
through one or more of the websites;
All of this can be done with separate accounts, but it requires more work than
having a single domain-level account with the appropriate permissions. Just
realize you open yourself up to a greater threat if that single account is
compromised.
Good luck,
Ryan LaNeve
"Andrew Connell" <sp...@aconnell.com> wrote in message
news:#zvuohwXCHA.2832@tkmsftngp10...
Andrew Connell
Currently I have a website that's using the local webserver's IUSR account
(I'm doing all of this on a test box). The dev box has an instance of SQL
Server installed as well. I went in and added the IUSR account that IIS
uses to the list of logins for the server and added it to a database user
role in the desired database. However, when I try to access the database
via the website, I get a login denied error... [Microsoft OLE DB Provider
for SQL Server (0x80040E4D) Login failed for user 'IUSR_WEBDEVGRP'.]. The
only thing I notice is the lack of the domain (computer name), but I didn't
think that would matter because it would look it up there first. My
connection string looks like this:
"Provider=SQLOLEDB; Data Source=WEBDEVGRP; Initial Catalog=Northwind;"
I haven't used NT authentication as a login from a website before, just
desktop/network apps. Any ideas what's wrong?
-AC
"Ryan LaNeve" <rya...@spamlaneve.com> wrote in message
news:u#BIQX5XCHA.1676@tkmsftngp11...
Andrew Connell
-ac
"Andrew Connell" <sp...@aconnell.com> wrote in message
news:ePwxBF#XCHA.2812@tkmsftngp10...