3

I have installed SQL Server 2005 (Developer edition) on a Windows 7 machine (I know - it wasn't my choice!) Machine is named user-PC, and has only one user: user no pwd.

I am trying to connect to DATABASE ENGINE, using server USER-PC (taken from the options list) using windows authentication.

I get the message Logon failed for user-PC\user (Microsoft SQL server error 18456).

Here's the trace: 

Cannot connect to USER-PC.

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

Login failed for user 'user-PC\user'. (.Net SqlClient Data Provider)

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

------------------------------
Server Name: USER-PC
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()
Ben Pilbrow
  • 12,041
  • 5
  • 36
  • 57
Chris Kemp
  • 31
  • 2
  • 1
    SQL only isn't an option. You can only have Windows only or Mixed. – Shane May 02 '11 at 15:28
  • Can you try and run SQL Server Management Studio as Administrator and let us know what happens? (Right click on it and select Run As Administrator". – Mike Walsh May 02 '11 at 15:46

3 Answers3

1

The more I think about it, the more I think that comment is the answer - Try and run as Administrator (Right click, choose run as Administrator) and see if that works. My guess is that the account may not be privileged as Admin or in the administrators group. Once in to SQL you can then add that account to SQL Server as a login and grant it Sysadmin rights (presuming that is the access you want it to have).

Mike Walsh
  • 437
  • 2
  • 8
  • Mike was right. The underlying account did not have sql admin privileges. So at his suggestions, I opened SSMS as administrator, then added the windows default user accound to the login, and gave him sysadmin privileges. Thanks to @Mike_Walsh @brentO & @AaronBertrand on Twitter – Chris Kemp May 02 '11 at 16:24
0

Lots of questions come to mind:

  1. Are you connecting locally or remotely?

  2. Is this a default instance or a named instance?

  3. What protocoles are enabled and what connection method are you using? TCP/IP, Named Pipes, Shared Memory?

joeqwerty
  • 109,901
  • 6
  • 81
  • 172
  • 1
    Couple quick thoughts: 1. Good question. My guess from the description is local but still a good question. 2. He found the right instance because of the error 3. He is getting past the protocols because of the error. The instance is talking to the client. It is refusing to authenticate. That error message indicates as much as it is SQL responding to Chris. My guess here is that the account logged in doesn't have proper administrative rights and therefore isn't in the "builtin\administrator" group SQL 2005 automatically adds. Running as Admin should help see as much. – Mike Walsh May 02 '11 at 16:00
0

With SQL Server 2005 (all editions) by default:

  • There is no network access (using the SQL Server Configuration tool locally can change this: don't forget to add a firewall exception).

  • Only the Local Administrators group has access (you can add logins/users that map to other Windows user's with appropriate permissions with SQL Server Management Studio or SQL statements).

(SQL Server 2008 prompts in the installer for users/groups to get initial server admin access.)

Richard
  • 5,324
  • 1
  • 23
  • 20