January 2, 2009 at 6:42 pm
SQL throwing login failure error when an NT user (NT Login) trying to login on SQL. But the same user added explicitly in SQL level he is succeeding to login?
Am i missing something in local policy level? or is this related with Kerberos Authentication issue?
Please suggest...
Happy New Year to all...
Thanks
Jay
http://www.sqldbops.com
January 3, 2009 at 12:23 am
Hello,
Can you post the error message that the user gets when trying to Logon using Windows Authentication?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
January 3, 2009 at 2:31 pm
Is there possibly an explicit deny on a group the user is a member of? And have you ensured that the user logon is enabled, not just added to the login list?
January 4, 2009 at 1:27 am
Is there possibly an explicit deny on a group the user is a member of? And have you ensured that the user logon is enabled, not just added to the login list?
I ensured that the group where the NT user member of is Admin of the box, the same config on this box is followed other boxes as well and working fine except this.
One more thing if I add the user explicitly in SQL level then the user can login to SQL.
Something I'm missing but sure where!!!
The Below error I'm getting when i try to login to SQL
---------------------------------------------------------------------------------------------
TITLE: Connect to Server
------------------------------
Cannot connect to ABCSQLServer
------------------------------
ADDITIONAL INFORMATION:
Login failed for user 'ABC\Krishjay'. (Microsoft SQL Server, Error: 18456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
---------------------------------------------------------------------------------------------
Thanks
Jay
Thanks
Jay
http://www.sqldbops.com
January 4, 2009 at 2:04 am
Hello,
Was there not a “State: NN” part to the Error Message? The value for State is important in determining the exact reason for Logon failure.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
January 4, 2009 at 4:06 am
John Marsh (1/4/2009)
Hello,Was there not a “State: NN” part to the Error Message? The value for State is important in determining the exact reason for Logon failure.
State'll be in the error log, if failed logins are logged. It's not returned to the client, because that's too much information.
Jayakumar: can you look in the SQL error log, see if the login failure's there and if so, what the state is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2009 at 4:35 am
Hello again,
State should also be available “online” via the “Show Technical Details” option. (In the block of text under the Microsoft Link that you provided previously).
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
January 4, 2009 at 12:06 pm
Jayakumar Krishnan (1/4/2009)I ensured that the group where the NT user member of is Admin of the box, the same config on this box is followed other boxes as well and working fine except this.
So, just to be clear - you want this user to be a system administrator on the box as well as a system administrator in SQL Server?
If that is correct (why?) - then you need to review the BUILTIN\Administrators group in SQL Server. If the user cannot connect when added as a local administrator - then this group either no longer exists, or the group has specifically been denied logon rights.
BTW - is this a user of the database? A developer? Another DBA?
If not a DBA - then why would you grant a user full system admin rights in SQL Server? That user would be able to make any changes they want, access any data they want - bypassing all application security and system security. Is this really what you want?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 4, 2009 at 2:17 pm
Answer for Williams Question.
----------------------------------
The NT user(ABC\Krishjay) is one of the DBA, he is member of ABC\DBOps_Team (Domain Admin group) which is member of local administrator group of this box and all other boxes which are under monitoring.
The same NT user can access all SQL servers in all environments except this box.
So somewhere I'm missing config on this Win srvr 2008/SQL 2008 cluster environment, might be new settings in this env???
Answer for GilaMons
------------------
2009-01-04 12:31:24.04 Logon Error: 18456, Severity: 14, State: 11.
2009-01-04 12:31:24.04 Logon Login failed for user 'ABC\Krishjay'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 120.2.7.30]
Answer for Marsh
-------------------
Below details are the technical details.
===================================
Cannot connect to ABCSQLServer
===================================
Login failed for user 'ABC\Krishjay'. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
------------------------------
Server Name: ABCSQLServer
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536
------------------------------
Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectorThread()
Any clues from the above can help to resolve?
Thanks
Jay
http://www.sqldbops.com
January 4, 2009 at 2:24 pm
Jayakumar Krishnan (1/4/2009)
Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 120.2.7.30]
That looks like a Kerberos issue. Are there any other errors in the error log that indicate problems connecting to the domain controller, problems generating SSPI context, problems registering an SPN?
Are there any errors in the windows event log indicating problems connecting to the domain controller?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2009 at 2:33 pm
Did you validate the BUILTIN\Administrators group in SQL Server 2008? I believe the problem you are having is the fact that security has changed on Windows Server 2008 - and just adding a user to the Administrators group no longer automatically allows admin access to SQL Server.
You can review the following article for additional information: http://technet.microsoft.com/en-us/library/cc280562.aspx
What this means is that you can do one of the following:
a) Explicitly grant access to the BUILTIN\Administrators group in SQL Server (not recommended)
b) Explicitly add your domain group (DBA's only?) to SQL Server and grant sysadmin rights to that group.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 4, 2009 at 2:36 pm
BTW - the key here is the combination of Windows Server 2008/SQL Server 2008.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 5, 2009 at 12:45 am
I couldnt see any other specific logs in the Eventlog except the above.
If it is Kerberos issue, how can i enable/disable it in Windows Server 2008 cluster administrator.
I already (same day when i post this topic) posted another topic for finding the Kerberos option in Win 2008 cluster administrator.
http://www.sqlservercentral.com/Forums/Topic629187-391-1.aspx
Thanks.
Thanks
Jay
http://www.sqldbops.com
January 5, 2009 at 9:30 am
It is not a Kerberos issue - please read the document I linked to for further information. On Win2008/SQL2008 you cannot just add a user to the local Administrator group in Windows and get sysadmin access to SQL Server.
You have to enable the BUILTIN\Administrator (not recommended) - or add the group to SQL Server.
Again - review the document I linked to for additional information.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 14, 2010 at 12:04 pm
From my observations .... On Windows 2008 / R2, this behaviour is expected.
To make it work, you have to elevate the process with administrative privilege (UAC)
So you open SQL Mgmt Studio with run as administrator. Then you are part of BUILTIN\administrators and you can successfully log in to SQL Database engine.
Else, you have to explicitly had the user to SQL Logins.
Note that this only applies when login in locally on the SQL server. If you run SQL Mgmt Studio remotely, you don't need to explicitly grant access to user. BUILTIN\administrators will suffice.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply