 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Security / May 2008Howto troubleshoot login failure - State 11?
I have a problem where a logon account is denied access to SQL Server 2005. The server log: Login failed for user 'Domain\User' [Client: xx.xx.xx.xx] Error: 18456, Severity: 14, State: 11 How do I troubleshoot this? The account is a member of a Windows group, and it is the group that is granted access to the SQL server. Regards; /jb Jonny, We have a script that will help. In the folder where the SQL error logs go you will find a number of trace files (.trc). You will need to get the name of the latest one then try this:- select StartTime, TextData, NTUserName, HostName from :: fn_trace_gettable('D:\DBMS\MSSQL.1\MSSQL\LOG\log_xxx.trc', default) join sys.trace_events on eventclass = trace_event_id where eventclass = 20; where xxx.trc is the name of the trace file. This is the default trace that should be running unless you set it off using sp_configure. HTH Chris >I have a problem where a logon account is denied access to SQL Server 2005. > [quoted text clipped - 8 lines] > Regards; > /jb Jonny, In our script the trc files are in D:\DBMS\MSSQL.1\MSSQL\LOG. You will change this to where your installation has put the trace file. Chris > Jonny, > [quoted text clipped - 27 lines] >> Regards; >> /jb > select StartTime, TextData, NTUserName, HostName from :: This is what the trace says. 2008-05-27 22:16:21.253 Login failed for user 'DOMAIN\BTS01$'. [CLIENT: 10.10.1.236] BTS01$ BTS01 >> Login failed for user 'Domain\User' [Client: xx.xx.xx.xx] >> Error: 18456, Severity: 14, State: 11 Seems the info in the log is more detailed? What does State 11 indicate? I have not found anything relevant via Google. The strange thing is that this login actually worked for a period of 20-30 minutes, and then suddenly stopped working again. The setup is this: The SQL server is installed on a domain controller. The service accessing the SQL server is installed on a separate machine that is a member of the domain. The service runs under the local Network service, which is why it uses the computer account to logon. The computer account is member of a Windows group that is granted access to the server. Please advice. Regards; /jb Jonny, This outlines many of the states: http://technet.microsoft.com/en-us/library/ms366351.aspx Notice that State 11 means "Login is valid, but server access failed." It could be that the default database the login uses is no longer defined or the connection string is specifying an non-existant db, etc. Probably a few other things could be the case. RLF >> select StartTime, TextData, NTUserName, HostName from :: > [quoted text clipped - 24 lines] > Regards; > /jb > It could be that the default database the login uses is no longer defined > or The event log also shows a Failure audit event for each failed logon. The Data part of the event displays the strings 'SQL01' and 'master', so it is not trying to access anything out of the ordinary. The Windows group used is the 'BizTalk Server Administrators' group that is setup by the BizTalk installation program. Normal users in this group has no problems accessing the server. Also - I added the computer account as a user on the server (CRETE LOGON [DOMAIN\BTS01] FROM WINDOWS). As it only had the public role, it failed to logon, but this time with State 16. The error message reported that it could not open the requested database. This indicates to me that it is not a database access issue, but a problem accessing the server itself. Granting the account the sysadmin role gives access to the server, but this is no solution to my problem. I need the computer account to login through a Windows user group in order to access BizTalk properly. The error "State 11 - Login is valid, but server access failed" leaves me with very few clues as how to continue tracking this problem down. Any further assistance is much appreciated. Btw; I have another computer using a computer account to logon to the SQL server, this computer is member of another group, and it works without any problems. Regards; /jb Hi Jason, From your description, I understand that the real problem here was that your computer account could access your SQL Server instance, however the connection accidentally failed after 20-30 minutes. It belongs to a Windows group which is granted access to your SQL Server. If I have misunderstood, please let me know. I think that this issue might happen when the computer account tried to access some database on which it had no access permission. However for further research, I would like to collect more information from you: 1. What are the database access permissions assigned to your Windows group in your SQL Server? 2. What is the result if you explicitly assign the fixed server role sysadmin to your Windows group? 3. Could you please mail me (changliw_at_microsoft_dot_com) your SQL error logs which are located in the folder %ProgramFiles%\Microsoft SQL Server\MSSQL(.x)\MSSQL\LOG? Look forward to your response. Have a nice day! Best regards, Charles Wang Microsoft Online Community Support =========================================================== Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@microsoft.com. =========================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscriptions/support/default.aspx. ============================================================  SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. ========================================================= I have now tested the exact same scanrio in two other environments and it does not work there either (I have separate environments for developing, staging and beta tests). Please forget anything I wrote about getting it to work, it must have been during the time I testad different approaches. The only way I have been getting this to work is adding the computer account to the logins, and granting it the sysadmin role. The problem is: Logging in with a normal computer account fails. Logging in with a normal user account succeeds. Both is a member of the Windows group that is granted access to the database. All accounts and groups are in the domain. > 1. What are the database access permissions assigned to your Windows group > in your SQL Server? The standard permissions that BizTalk Server assigns; The 'public' role, and the different BTS_* application roles added by BizTalk. The service only accesses the BizTalkMgmtDb database, and the application roles granted there are BAM_CONFIG_READER, BTS_ADMIN_USERS and BTS_HOST_USERS (If you need to know the actual grants of those, You have to ask som BizTalk guy, I don't know). > 2. What is the result if you explicitly assign the fixed server role > sysadmin to your Windows group? It makes no difference. > 3. Could you please mail me (changliw_at_microsoft_dot_com) your SQL error > logs which are located in the folder %ProgramFiles%\Microsoft SQL > Server\MSSQL(.x)\MSSQL\LOG? Sent! Regards; /jb Jonny, Let's see what you know and what questions are left: 1 - The login is fine and is able to log in. 2 - However, only when the login is made a sysadmin can it log in. 3 - If the login is not a sysadmin, it fails with State 11. Therefore, we are facing a rights issue. Making the login sysadmin simply eliminates all rights checking. The question that remains is: What right does my login need that it does not currently have? Possible answers: 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 difference.) If so, you simply have not yet successfully assigned the login the rights it needs in the target database. 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 database? (Check the login properties and see what the default database is.) Remove the problem login from the sysadmins group, then run: exec sp_helplogins 'Mydomain\Mylogin' This will produce a list of rights across databases for the login. Review these and see what is missing. RLF >I have now tested the exact same scanrio in two other environments and it >does not work there either (I have separate environments for developing, [quoted text clipped - 34 lines] > Regards; > /jb > 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 Hi Jonny, Thank you for your email response. From the error log, I found the following error messages: 2008-05-27 21:56:22.37 spid60 The client was unable to reuse a session with SPID 60, which had been reset for connection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message. After further research, I found that this is most likely the known issue documented in this KB article: FIX: You may receive error messages when you try to log in to an instance of SQL Server 2005 and SQL Server handles many concurrent connections http://support.microsoft.com/default.aspx?scid=kb;EN-US;937745 I recommend that you first install the hotfix to see if it helps. If you have any other questions or concerns, please feel free to let me know. Best regards, Charles Wang Microsoft Online Community Support ========================================================= Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@microsoft.com. ========================================================= SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. ========================================================= Are your SQL Server service account and the other service which tries to connect to your SQL Server instance, domain accounts or not? SignatureEkrem Önsoy >> select StartTime, TextData, NTUserName, HostName from :: > [quoted text clipped - 24 lines] > Regards; > /jb They both run under Network Service, which means the authenticate as the computer account which in turn is domain accounts. Regards; /jb > Are your SQL Server service account and the other service which tries to > connect to your SQL Server instance, domain accounts or not? [quoted text clipped - 27 lines] >> Regards; >> /jb |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.