Re: error logging on SQL 2008

Thanks for the detailed description. Yes, that was the error I wanted to see. I was, though, hoping for some more recognizable Reason for that error (state 11, " Token-based server access validation failed with an infrastructure error. Check for previous errors."). Do you have any prior errors that seems to be related? If this is a test machine (so far), you might even want to restart the service to get a "clean" errorlog file in case there are some start-up messages that might be related.

Reading any of below two:
http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/03/04/understanding-login-failed-error-18456-errors-in-sql-server-2005.aspx
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

Suggests that state 11 means "Valid login but server access failure", which doesn't really seem to rhyme with your description.

Your description of the installation seem to be very normal.There is one change between 2005 and 2008 setup. 2005 added the BUILTIN\Administrators group as sysadmin. 2008 don't do that, instead the setup dialog ask if there is any account you want to add. Perhaps this can explain what has happened?

I can't say why adding the Administrators group won't work. Im not sure, but I think that if this is a domain constroller that you have no local account database. But I'm too weak on the Windows stuff to be more specific here...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Zoran" <ms.news@xxxxxxxxx> wrote in message news:%23t8uEWm7JHA.2456@xxxxxxxxxxxxxxxxxxxxxxx
Sorry for the confusion - I was trying to open the instance from another sever using SSMS. :o)
I have a similar problem on another machine, but at that machine I can log on as local admin. On this particular machine I cannot log via SSMS, no matter what user accound I use (I checked and double checked, both the local admin and domain admins are in local administrators security group)

Just short: I have an Windows Server 2008, x64, Datacenter, which I joined to a domain, logged on as domain admin, installed the SQL 2008 x64 Enterprise, and did the default installation, and all services were configured to run as a domain SQL service account (normal domain user that I added to local admin group on the server). After the installation I applied the SP1, restarted the machine, logged on as domain admin, tried to open the local instance using SSMS - and here is where I get the error.

Anyhow, I opened the "ERRORLOG" file from :\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log

and found this:
2009-06-16 11:24:26.04 Logon Error: 18456, Severity: 14, State: 11.
2009-06-16 11:24:26.04 Logon Login failed for user 'OurDomain\Administrator'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

Hope that was what you wanted to see?

Regards, Zoran


"Tibor Karaszi" <tibor_please.no.email_karaszi@xxxxxxxxxxxxxxxxxx> skrev i en meddelelse news:uFvrq$l7JHA.1764@xxxxxxxxxxxxxxxxxxxxxxx
I'm confused. You say you check using Log File Viewer, but that is a tool you use from SSMS. How can you login to your SQL Server and use that tool if you can't log into your SQL Server? Perhaps a from-the-beginning explanation of your environment, machines, account types and tools you are using?

Anyhow, check the SQL Server errorlog file, nothing else. In there, you will find an error for the failed login. I'm after that error message. This gives further details about the problem. This is where we need to start.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Zoran" <ms.news@xxxxxxxxx> wrote in message news:ONMzE0l7JHA.240@xxxxxxxxxxxxxxxxxxxxxxx
I checked the Log File Viewer - just a standard error, something about user credentials. I sucessfully logged on the machine, and I am starting the SSMS afterwards, so I am logging on as my current credentials, so username and password are OK.

One more thing that I just discovered - I cannot add the builtin Administrators group as a Login in SQL server! It says that it cannot find the group! (I didn't write the login while creating, I browsed for it)

Here is the error:


--------------------------------------------------------------------------------

TITLE: Microsoft SQL Server Management Studio
------------------------------
Create failed for Login 'MyServer\Administrators'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1045+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
-----------------------------
Windows NT user or group 'MyServer\Administrators' not found. Check the name again. (Microsoft SQL Server, Error: 15401)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=15401&LinkId=20476

--------------------------------------------------------------------------------


Regards,

Zoran
"Zoran" <ms.news@xxxxxxxxx> skrev i en meddelelse news:e2YsZAj7JHA.4100@xxxxxxxxxxxxxxxxxxxxxxx
Hello All,

I did have the same kind of a problem previously, and I now got it again - "cannot connect to localhost".

I made a new installation of SQL 2008 on a Windows Server 2008, installed SP1 for SQL 2008, and after the restart of the machine, I cannot get in using SQL Server Management Studio (I get the error below).

It is funny - I am logged on as local administrator, so I should have all the rights...!

Anyhow, how do I connect to the server now, to check the security/logins...?

Thank you in advance,

regards

Zoran

--------------------------------------------------------------------------------

===================================

Cannot connect to localhost.

===================================

Login failed for user 'OurMachine\Administrator'. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

------------------------------
Server Name: localhost
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.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

--------------------------------------------------------------------------------








.

Relevant Pages

  • Re: SQL2005: Cannot connect error 11001
    ... The famous Windows Firewall (turned on my Server from which I'm trying to ... Exception Details: System.Data.SqlClient.SqlException: Login failed for user ... Try starting the SQL Server ... if you changed the port ...
    (microsoft.public.sqlserver.connect)
  • Re: error logging on SQL 2008
    ... Administrators group as a Login in SQL server! ... owningObject, SqlConnectionString connectionOptions, Int64 timerStart) ... owningObject, SqlConnectionString connectionOptions, String newPassword, ...
    (microsoft.public.sqlserver.server)
  • Re: Clearer version of my earlier question re sp_grantlogin and sp_grantdbaccess
    ... > file while you do changes, so the script file in the end contains the ... >> Server, doesn't have the right to grant DB access to Domain Groups. ... > Tibor Karaszi, SQL Server MVP ... >> site where they let me login as Domain Admin. ...
    (microsoft.public.sqlserver.msde)
  • Re: SQL 2005 Express setup
    ... Connect to your SQL Server Instance, ... Double click on the Login that you want to add to the "sysadmin" server role or right click on it and choose properties from the popup menu, ... Login is a member of the "sysadmin" fixed server role now. ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL 2005 Express setup
    ... Connect to your SQL Server Instance, ... Double click on the Login that you want to add to the "sysadmin" server ... Mark the "sysadmin" checkbox to make this Login a member of this Server ...
    (microsoft.public.sqlserver.setup)