Sql-server – Can’t Connect to (local) database

localsql serversql-server-2008

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>]

Best Answer

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.

Related Solutions

SQL Server – Cannot Connect to Local SQL Server 2008

Ok, can you open your services console and scroll down to S for SQL Server. You should now see the services. Please ensure SQL Server (SQLEXPRESS) is running and then try .\SQLEXPRESS instead of (local).

So as per your example:

Server type: Database Engine
Server name: .\SQLEXPRESS
Authentication: Windows Authentication

Hope this helps

Update: These instructions are because I assume you are running Express Edition not Dev/Std/Ent edition of SQL Server

Try ensuring the appropriate protocols are enabled:

Start the SQL Configuration Manager (ie: Start->Programs->SQL Server->Configuration Tools)
Expand the SQL native Client configuration
Click Client Protocols (you may have a 32-bit and a 64-bit, apply to both)
Ensure Shared memory, TCP/IP, Named Pipes are enabled in that order
Expand SQL Server Network Configuration
Ensure Shared Memory for either SQLEXPRESS and/or MSSQLSERVER is enabled
Click SQL Server Services
Restart any running services

You should now be able to login to the instance

If you find you cannot login at all you may need to follow these instructions to get SQL Server into single user mode. See here for the full instructions from Microsoft.

By default, sqlservr.exe is located at C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn. If a second instance of SQL Server is installed, a second copy of sqlservr.exe is located in a directory such as

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\binn.

You can start one instance of SQL Server by using sqlservr.exe from a different instance, but SQL Server will start the version of the incorrect instance as well, including service packs, which may lead to unexpected results.

To avoid this, use the MS-DOS change directory (cd) command to move to the correct directory before starting sqlservr.exe, as shown in the following example.

cd \Program Files\Microsoft SQL Server\MSSQL10_50.1\MSSQL\Binn

To start the default instance of SQL Server in single-user mode from a command prompt

From a command prompt, enter the following command:

sqlservr.exe -m

Single-user mode can be useful for performing emergency maintenance when you do not want other users to connect to SQL Server, but any user can become the single user, including the SQL Server Agent service.

You should now be able to login to the instance and add yourself to the security tab and grant full access.

Alternate Method:

THere is a script here that claims to add the current user to the SQL Server sysadmin role. This may work in single user mode but I have not verified it

Sql-server – Can’t connect to local SQL Server

If you've done a standard install using the default name (i.e. not a specified instance name) you should be able to connect to either "localhost" or "DM".

Does SQL Browser not return the name of the local service when you browse for SQL Servers?

Best Answer

Related Solutions

SQL Server – Cannot Connect to Local SQL Server 2008

Sql-server – Can’t connect to local SQL Server

Related Topic