February 11, 2011 at 3:14 pm
I've seen similar questions in the forum, but none answering the exact question this is asking.
We are switching to using Windows authentication from an IIS server. IIS uses a username of IUSR_<server> for authentication. We have a Windows local group which contains all database users, and all the permissions are assigned to that group. But when I put IUSR in that group, it doesn't give permission to the IIS related processes. The question is, (a) why not, and (b) what's the best way to get around this?
So here are the 3 options I'm looking at:
1. Putting IUSR in the group. Again, this doesn't work.
2. Adding the IUSR name to SQL Server as a separate login, not in a group. This DOES work. But I don't like this; I want all the permissions to key off the group.
3. Changing the IIS properties to use a different username already in the group. This DOES work. I like this a lot better than #2, but it seems #1 would be even better. But then why doesn't #1 work?
So is IUSR special in some way, that it doesn't inherit the permissions of a group it's in? Or do we just maybe need to wait for a reboot or something before #1 starts working? (Not easy since this is a 24x7 server.)
Or, would solution #2 or #3 be considered more proper and a better idea than #1?
Note 1: We'll be upgrading to SQL Server 2008 soon, so this question applies to both it as well as 2000.
Note 2: We don't have Active Directory now but we're in the process of migrating to it.
February 20, 2011 at 1:46 am
We are switching to using Windows authentication from an IIS server.
Do you mean you're switching to Windows Authentication from Anonymous Authentication with respect to the website/directory? If so, IUSR_* isn't used. The IUSR_* account is the default account when a website is configured for Anonymous Authentication. The reason being is that you don't know the user coming in (IIS didn't check because you told it not to), and so it must run processes/check security using a particular account, and IUSR_* is it unless you change it.
If you switched the web site authentication to Windows Authentication, if the IIS and SQL Server are on the same box, you'll see the access as the actual user connecting (so if it's you, you'll see your domain account, if it's Jim you'll see Jim's account). However, if they are on different boxes, then you're in a double hop situation. That means the second connection to SQL Server cannot be established passing the user's credentials on. So the connection to SQL Server will say it's coming from NT AUTHORITY\ANONYMOUS LOGON. Using Kerberos delegation, this can be permitted, but that's a whole different discussion that doesn't need to be gotten into if it's not the problem.
If you're using components that run behind the scenes, it could be connecting using the IWAM_* account. This was especially true back when folks used ASP a lot with ActiveX components. If you're using ASP.NET, then it's probably connecting with the application pool identity, which by default is Network Service. If you're IIS and SQL Server are on the same box, then that's what SQL Server will see coming in. If the boxes are separate, then the SQL Server will see Domain\ComputerName$.
One way to verify what account is being used is to stand up a Profiler trace and make sure you are auditing for failed logins. Then attempt to access the web site in the way that is failing. If the connection to SQL Server is being made, you'll see it, and you'll see what account is being used.
K. Brian Kelley
@kbriankelley
March 21, 2011 at 6:15 pm
It appears I neglected to request mail notifications when anyone answered this post, and I've been burning a great deal of midnight oil since then, so I haven't gotten back to this question.
K. Brian Kelley (2/20/2011)
Do you mean you're switching to Windows Authentication from Anonymous Authentication with respect to the website/directory?
No, I mean we've been using SQL Server Authentication, and we now have to switch to using Windows Authentication to connect with SQL Server. IIS is still taking anonymous access. So we must specify a username in IIS, but the question concerns (a) which username to use, and (b) how to get SQL Server permissions to be assigned on the basis of group membership, not the actual username.
Since I write the original post, it appears the IUSR_... name may in fact be working (i.e., getting its SQL Server permissions from the group I put it in). Maybe there was a delayed effect.
The other consideration is that we're migrating to Active Directory at the same time. This IIS will be talking to two different SQL Server instances, the original one (currently SQL 2000) on the same box and another one (SQL 2008) on a different box. So I'm guessing a new domain user (as opposed to IUSR_...) and a domain group (for the permissions) would be most appropriate here, as opposed to defining IUSR_... as a domain user. Sound right to you?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply