We are using Windows Authentication, and have created SQL logins for about
40 different groups on our domain. We've given those logins the appropriate
permissions on the databases they're supposed to be able to access. The SQL Server is not a domain controller, but is a member of the domain, and domain logins do work for Windows-login purposes on this box.
The problem is that when users try to connect to the SQL server, they are denied access. An error 18456 is thrown, and logged in the Application event log
stating "Login failed for user OURDOMAIN\theuser" (example values). The
domain user is properly a member of group added as a login to SQL Server, and we've confirmed that there are not conflicting permissions that would deny those
users access via another route. These same groups are working fine on the SQL Server 2000 box.
This is only a problem for domain-based groups. If we create a local group
on the SQL server machine, through Computer Management -> Local Users and
Groups, then make the same domain users a member of THAT group, and finally then follow the same process to add that local group to SQL Server Logins and set
the database privileges, it works!!
Our group memberships change frequently, and are used for a lot more than
just SQL server permissions. So, using local groups and maintaining
membership in both places is not really feasible. Any ideas why a local
machine group containing domain user accounts would work fine, but a domain
group containing the same accounts would not?
Thanks in advance.
Hi,
see if the default database defined for the users / groups ius available and they are granted access to the database. If not they will be denied access to the server and will get the error message posted by you. Did you try to create a single Windows and login with this user ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Single user: Yes. Creating individual logins for domain users works fine. It is only logins for domain groups that are not working.
Default database: The users are connecting via ODBC connections, and the default database is set correctly.
It doesn't seem to be related to any database selection issues at all. Within a single database, if we grant access to the individual user account, or a local machine group that the user is a part of, everything works fine. Grant them access via a domain group, and it denies login. This is not something we've encountered before, and is making me wonder if there is some weird, poorly-documented restriction in the Workgroup Edition...
|||
Please post the error as it is printed in the errorlog file of SQL Server (the ones under MSSQL\...\LOG\ERRORLOG*). Post both the error messages and the error numbers and states - the states are important.
Thanks
Laurentiu
With group OURDOMAIN\SecGroupA created on the domain controller, and user 'kswain' made a member of the group. We create a SQL Server login for OURDOMAIN\SecGroupA and get:
2006-12-27 10:45:02.50 Logon Error: 18456, Severity: 14, State: 11.
2006-12-27 10:45:02.50 Logon Login failed for user 'OURDOMAIN\kswain'. [CLIENT: xxx.xxx.xxx.xxx]
With local group TestLocalGroup created on the SQL Server machine, and user 'OURDOMAIN\kswain' added as a member: We create a SQL Server login for SERVERMACHINE\TestLocalGroup using the same process as previously, and it works fine. We don't even have to log the end-user account out of the application... it works as soon as we authorize the local group.
|||
Hi,
Does this error comes when you access from your Application or via Query Analyzer ? Are you able to login via Query Analyzer?
What application you are running !? A web application ? Ensure IIS Permission is also properly set refer below KB
http://support.microsoft.com/kb/316989/
What about connection string in your application ?
Hemantgiri S. Goswami
|||The login failure occurs both from the application and from Query Analyzer using any non-Administrator account.Our testbed application at the moment is actually just an Access database, using the built-in Windows SQL Server ODBC driver, set up as a Machine DSN. When our main app didn't work, we created the simplest method we could think of to try and connect. We are not using IIS for this at all. We know the DSN itself is set up correctly since it works if the user is explicitly added, or is part of a SQL Server machine-local group.
--
One additional thing we've discovered in the last 24 hours. It still does NOT work if you add a domain group to a local group. So the issue seems to be one of domain group membership resolution. In other words, if we authorize SQLServer\LocalGroupA:
* If OURDOMAIN\userA is a member of SQLServer\LocalGroupA, OURDOMAIN\userA login => OK
* If OURDOMAIN\userA is a member of OURDOMAIN\DomainGroupA, and OURDOMAIN\DomainGroupA is a member of SQLServer\LocalGroupA, OURDOMAIN\userA login => FAIL!
Other thoughts we're having that might be somehow relevant, but we can't seem to get around:
1) The domain is mixed Win2000 and Win2003. The SQL Server 2k5 is running on a Win2003 machine that is not a domain controller, but is a domain member.
2) Our AD domain name has a hyphen in it, both in NetBIOS and FQDN (i.e. OUR-DOMAIN and cleveland.our-domain.local)
3) The non-administrative users are accessing the application server via Terminal Services.
None of the above should make a difference (and don't have any problems with SQL Server 2000), but we're really grasping at straws at this point...
|||
Can you see if LookupAccountName works for the group name? If you have a small application called name2sid, you could use it to check this. Or you could use something similar to my CLR function from http://blogs.msdn.com/lcris/archive/2005/09/26/474202.aspx.
If this function works ok, then I suggest to open a report on the site indicated in the sticky thread at the top of this forum. You can start the server with the -y18456 parameter, to have it generate a memory dump, and you can attach the dump to the report.
Thanks
Laurentiu
I'm not familiar with the -y18456 parameter. I just restarted the SQL Server service with that flag included in the Startup Parameters, but don't see a dump anywhere, or anything especially instructive in the event log. Can you tell me what I should be looking for after restarting with that flag? Once I can find that, I'll open a report on the Connect site. Thanks!
|||Please disregard previous request -- I see that it only outputs dumps when that specific error is thrown. Got it, and attaching it to a Connect bug now. Thanks!
|||Just for closure on this thread, this issue has been moved to a Connect feedback/bug report. ID is 248615 and can be found here:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=248615
|||
Hi,
Did you find a fix for this? I think we're having the same problem. I talked to a consultant who thought it was something with our domain since we were running windows 2000 and didnt have the windows DNS running as part of our domain. But everything was working except sql server so I should have known better. So we upgraded our domain controller and spent all kinds of money and it still doesn't work!! Please let me know if you figured out how to fix this!! None of our AD groups are working as logins. Only users and groups on the sql server machine.
Bob Coleman
|||No fix or workaround has been identified yet. Nobody at Microsoft has tagged the Connect bug referenced above, if it has even been reviewed yet. I'm trying to be patient, figuring that people were on vacation over the holidays.At the moment, we're just maintaining machine-local accounts with the appropriate domain users. Hopefully we'll get some kind of response shortly!
|||
We have received your feedback report. When we'll have an update on this issue, we'll update the report.
I am not familiar with the PsSid tool - could you point me to the link from which you downloaded it?
The -y argument can be used with any error number, to make the server produce a dump whenever the error is raised.
Thanks
Laurentiu
It's part of the SysInternals command-line suite that MS acquired earlier this year. Nifty stuff.
Thanks!
No comments:
Post a Comment