Re: Howto troubleshoot login failure - State 11?

1 - Could it be that your user's domain login is not actually a member of the domain group? (You said that making the Windows Group a sysadmin made no

Nope. Also, other members of that group can login.

2 - Could it be that the login was defined with a default database to which it has no rights and the login fails before it switches to the intended

All logins have the default value 'master' set as the default database.

exec sp_helplogins 'Mydomain\Mylogin'

That does not show anything special,
DefDBName=master
AUser=yes
ARemote=no
....
DOMAIN\BizTalk Server Administrators | EDIMgmtDb | EDI Administrators | MemberOf
DOMAIN\BizTalk Server Administrators | EDIMgmtDb | DOMAIN\BizTalk Server Administrators | User
....
DOMAIN\BizTalk Server Administrators | master | EDI Administrators | MemberOf
DOMAIN\BizTalk Server Administrators | master | DOMAIN\BizTalk Server Administrators | User
....

If I create a login named DOMAIN\BTS01$ with only the 'public' role, and tries to logon I get this error:

--SQL log---
2008-05-28 20:21:40.50 Logon Error: 18456, Severity: 14, State: 16.
2008-05-28 20:21:40.50 Logon Login failed for user 'DOMAIN\BTS01$'. [CLIENT: 172.30.1.236]
--Client error--
System.Data.SqlClient.SqlException: Cannot open database "EDIMgmtDb" requested by the login. The login failed.
Login failed for user 'DOMAIN\BTS01$'.
------
Here the login succeeded, but the requested database is not allowed (This is the scenario where adding the 'sysadmin' role allow access). The reason seems to be that if I create the actual account in SQL Server, any Windows group memberships are ignored and the account itself must be granted the same rights as the Windows group. That is not a viable action as I have no control of what roles to grant, as future updates to BizTalk may make changes to those.

If I remove the BTS01$ login, and tries to login, I get this error:
--SQL log---
2008-05-28 20:23:17.65 Logon Error: 18456, Severity: 14, State: 11.
2008-05-28 20:23:17.65 Logon Login failed for user 'DOMAIN\BTS01$'. [CLIENT: 172.30.1.236]
--Client error--
System.Data.SqlClient.SqlException: Login failed for user 'DOMAIN\BTS01$'.
-----
Here the login itself fails, no mention of any database name is made on the client.

This indicates to me that the problem is somehow related to SQL Server not being able to detect that the account is indeed a member of the 'BizTalk Administrator Group' domain group.

Regards;
/jb

.

Relevant Pages

  • Re: SQL Local Account Permission - Read Only on System Tables and
    ... Create a Windows group on the domain and have your developer logins added to ... VIEW SERVER STATE to that one login. ... can create your own server-level roles) and grant it VIEW SERVER STATE ...
    (microsoft.public.sqlserver.security)
  • Re: Is_Member problem : Does user belong to custom Group
    ... In active directory: 1 - Created in Active directory a Windows group named ... 'MyDomainName/MyApplReadOnly' as Login Name and the same as User ... We can create a ROLE in the database and add the users. ...
    (microsoft.public.sqlserver.security)
  • Re: Is_Member problem : Does user belong to custom Group
    ... - Added in Security/Logins the windows Group 'MyDomainName/MyApplReadOnly' referred as type 'Windows Group' with Defaultdatabase 'Master' ... Then configured the database Role Membership of this user by setting db_datareader and db_denydatawriter to true. ... for the current user returns 0 although he is referred in active directory as someone belonging to the the windows group Mydomainname\MyapplReadOnly ... There is a login, there is a user that mapped to the Login ...
    (microsoft.public.sqlserver.security)
  • Re: Error 18456: Login failed for user DOMAINuser
    ... I see, the thing is, there is no added Windows groups login in the SQL ... (Microsoft SQL Server, ... > Server through their membership in a windows group. ...
    (microsoft.public.sqlserver.security)