User should be able to login but cannot 2

[ESquared]

So I have this SQL 2000 server I've granted access to the Windows Group MyDomain\AllUsers.

Security access: Grant access
Default database: ADatabase
Server Roles: <none>
Database access: Permit in ADatabase as public role.

In ADatabase I have a User:
Name: MyApplicationUser
Login: MyDomain\All Users
Database Access: Permit

Now, user MyDomain\AUser attempts to use a tool I wrote that connects via integrated authentication. She is not allowed to connect, and in the event log I see:

18456 :
Login failed for user 'MyDomain\AUser'.

Any ideas how to track this down? The SQL 2005 server log will give you a State value that helps determine the exact problem, but SQL 2000 doesn't have this.

There is no myDomain\AUser user in the database, but this shouldn't be a problem as I want this user to authenticate using the All Users group.

But now that I think about it, perhaps she's authenticating as a different group on the server, and then not being able to get access to the database? How do I find out what group a particular user will authenticate as? (I have searched for this online and found nothing, the terms seem to be too vague as I just keep getting random general security topics.)

Thanks for your help.

 

[SQLBill]

Is MyDomain\AUser a member of the Windows group MyDomain\AllUsers?

Check with your system admin or whomever sets up active directory logins.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[ESquared]

I checked, and MyDomain\AUser is a member of the Windows group "MyDomain\All Users" (with the space, I made a mistake when I said it the first time).

Is there any way for me to figure out what group she will authenticate as on that server?

I compared all the group logins on this busy server to all the groups she is a member of, but there doesn't appear to be any match besides All Users.

I had originally set up each user individually, but doing this was not working because every time a new person wanted to use the tool (which looks up data every time you use it) I had to specifically grant access.

Instead, I created a role that has execute permission on some SPs in the desired database and not much more. Mapping All Users to this role works fine for security.

 

[SQLBill]

It shouldn't matter which group she authenticates with as long as she is a member of the group on SQL Server.

We have several groups and a user can be a member of several groups. Each group may have different access, but the user will have the combined access unless there is a deny.

Has this person been able to log in before with that group? If so, the issue may be they changed their login. We have issues with people who don't log off of their desktop or SQL Server and the connection is kept. Then when they change their password, the connection is continuing to use their old password and they get failure messages even though we don't see any errors in the Error Log. Try having them log off and log back in to see if the issue is resolved.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[ESquared]

Well, when I added the user's account to the server and the database specifically, it worked, so it can't be a password-caching issue.

I am just totally lost. Thanks for your help so far. I'm not sure where to go from here.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[mrdenny]

The procedure xp_logoninfo will give you information about the groups that the user is in.

exec xp_logininfo 'DOMAIN\UserName'

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[ESquared]

I eventually solved the problem.

Denny's SP helped me prove that each individual login was or was not being mapped to a Windows group. After some comparison of group-mapping that was working and this one that was not and some poking through Active Directory to see what might be different, I finally figured out:

The group "MyDomain\All Users" has an Active Directory group type of "Distribution" and not "Security."

I switched to using the group "MyDomain\Domain Users" which has a group type of Security, and now everything works great!