Can't Connect to (local) database

Question

I have SQL Server 2008 and was forced to make a new windows 7 profile. I no longer can connect to my (local) databases.

Everything I try to use windows authentication to login I get

Cannot connect to (local).

Login failed for user 'MyAccount'. (.Net SqlClient Data Provider)
Server Name: (local)
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()

I did not install SQL Server 2008. My company did and the support people I contacted were unable to help.

I made a little bit of headway and been able to login in with "SQLEXPRESS" using windows authentication based on Wayne's answer.

I have 2 services though

SQL Server (MSSQLSERVER) - tried to log in but won't let me in
SQL Server (SQLEXPRESS) - let me in

I am trying to get (local) to work still. So I am not sure if I can use this new account to help me out.

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

Cannot connect to .\MSSQLSERVER.

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

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid) (.Net SqlClient Data Provider)

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

------------------------------
Error Number: 87
Severity: 20
State: 0


------------------------------
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.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
   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()

Edit

When checking in the log I see this

2012-01-17 11:17:45.39 Logon       Error: 18456, Severity: 14, State: 11.
2012-01-17 11:17:45.39 Logon       Login failed for user 'MyAccount'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

score 1 · Answer 1 · answered Jan 17 '12 at 19:05

1

You created a new profile or a brand new account? They're very different things and the difference is very relative here. If you created a brand new account then that new account needs to be granted access to the default instance.

If you just created a new Windows profile, then does it work for you if you try to connect using your machine's hostname?

EDIT:
based on your update, you're using the wrong thing to connect. You want to use just the plain hostname to connect to the default instance. You don't want to use a fully qualified name. Basically, there is no instance named MSSQLSERVER. It's just a label given to the services that the default instance run as.

answered Jan 17 '12 at 19:05

squillman

37,618
10
90
145

I think it is a "new user account". Weird things happened. I could not connect to all the networks drives then something happened and the next time I logged in it thought I was a new user and created a new account and was never able to get into my old account. – chobo2 Jan 17 '12 at 19:07
How do I figure out what the hostname is? I want to get (local) working as it once did as it makes it so much easier with my .webconfig(I just have to specify local and not worry about it when I switch to another computers that may have different names but same database on it) – chobo2 Jan 17 '12 at 19:10
I tired my host name and same error. I beginning to think I need to somehow setup this account to have access to the default instance. – chobo2 Jan 17 '12 at 19:52
Then yes, it looks like you've got a new account and you need to grant access to that account in the default instance like I mention above. – squillman Jan 17 '12 at 19:55
How do I do this? I cannot access my old account. So it has to be through this account(It has admin rights) – chobo2 Jan 17 '12 at 19:58
If you can't access that account then you'll need to do it from a different account. That would either be another Windows account that was granted appropriate access or the sa account. It's possible that the Administrators group was granted sysadmin access during setup in which case any account with local admin should be able to get in. If not, then you could very well be looking at rebuilding your default instance. Soemeone, though, should have the sa account credentials and be able to get you in. – squillman Jan 17 '12 at 20:01
Ok well I got to ask the people who setup the computer if anyone has that permission. I think there is some super admin account so hopefully that has it. Otherwise can I do a repair or does it have to be a full install? – chobo2 Jan 17 '12 at 20:24
A repair will just fix the binaries and registry entries necessary to run the services. It won't do anything with security. – squillman Jan 17 '12 at 20:28

score 0 · Answer 2 · answered Jan 17 '12 at 19:11

0

The State 1 indicates that the error message you have posted here is a generic logon failure error code

Locate your SQL Server Error logs and use this article to determine the real cause of the problem.

Excerpt from the above mentioned article about logon failure error states:

ERROR STATE     ERROR DESCRIPTION
2 and 5         Invalid userid
6               Attempt to use a Windows login name with SQL Authentication
7               Login disabled and password mismatch
8               Password mismatch
9               Invalid password
11 and 12       Valid login but server access failure
13              SQL Server service paused
18              Change password required

answered Jan 17 '12 at 19:11

Mathias R. Jessen

24,907
4
62
95

I have C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Log and C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log which one should I be looking at and can I use notepad to open these? – chobo2 Jan 17 '12 at 19:15
Ok I think I found it. I getting State 11 (see my edit) – chobo2 Jan 17 '12 at 19:19
It simply means that it recognizes you as a valid user, but that you do not have the necessary privileges to connect to the SQL Server – Mathias R. Jessen Jan 17 '12 at 21:38
Do you happen to know how to get the necessary privileges? I tried to run the program as admin(what many articles said to try) but that does not work – chobo2 Jan 18 '12 at 06:13

Can't Connect to (local) database

2 Answers2