Can't log on - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Can't log on

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-07 : 16:19:19

Hi, I have a user name 'hhhh' under windows domain, that means that I can log on to windows with it.
Now I can't log on to sql server 2005 management studio.
But it is okay if I log on to windows with 'Administrator', I heard that I need to add user to the sql server something but still confused.

Thanks for details.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-07 : 17:10:23

Yes the user needs to be added to SQL Server. Logon with administrator and then add hhhh to SQL Server by right clicking on the Logins folder and selecting new login. In that screen, you can also provide the permissions and database access.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-07 : 17:13:50

Should I log on with 'Administrator' first then add the user name 'hhhh'?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-07 : 17:27:54

Yes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-07 : 17:48:13

User has been added successfully. However when I tried to open the database. An error occuied.

The databse BBBBBB is not accessible.

The details are

   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.DatabaseNavigableItem.GetChildren()
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ExplorerHierarchyNode.BuildChildren()

Test ODBC failed.
When I opened SQL Server Configuration Manager, several protocols were disabled. I right click to make them "Enabled" but no response.
When I open the services such as SQL Server Browser, it was disabled and could not make it enable.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-07 : 18:19:59

an Orphaned User???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-07 : 18:55:44

You must not have granted the user access to the database. Double click on the login to fix it or open up the database with your Administrator account and then add the user to security there. You still need to add permissions at this point as either method just gives it access to open the database, but not to query anything. What permissions you add is dependent upon what your user needs.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-07 : 18:56:18

No it wouldn't be an orphaned user. That only happens when you restore the database to another SQL instances and the sids don't match between the two systems.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-08 : 09:30:07

Today I failed even I lon on to windows authentication with Administrator.

TITLE: Connect to Server
------------------------------

Cannot connect to HZ_MULTI.

------------------------------
ADDITIONAL INFORMATION:

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

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

------------------------------
BUTTONS:

OK
------------------------------

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-08 : 09:51:56

By the way, the database is from my coworker.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-08 : 09:55:53

Program location at

 

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()

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-08 : 10:13:49

I failed also when I try to start servive from SQL Server 2005 Surface Area Configuration.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-08 : 10:22:31

[url]http://www.mydigitallife.info/2007/10/31/error-has-occurred-while-establishing-a-connection-to-sql-server-2005-which-does-not-allow-local-and-remote-connections/[/url]
I followed the link above but still failed.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-08 : 10:31:02

I checked the sql server log file.


2009-10-08 10:28:26.34 Server      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) 
	Oct 14 2005 00:33:37 
	Copyright (c) 1988-2005 Microsoft Corporation
	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2009-10-08 10:28:26.34 Server      (c) 2005 Microsoft Corporation.
2009-10-08 10:28:26.34 Server      All rights reserved.
2009-10-08 10:28:26.34 Server      Server process ID is 3468.
2009-10-08 10:28:26.34 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2009-10-08 10:28:26.34 Server      This instance of SQL Server last reported using a process ID of 2808 at 10/8/2009 10:22:10 AM (local) 10/8/2009 2:22:10 PM (UTC). This is an informational message only; no user action is required.
2009-10-08 10:28:26.34 Server      Registry startup parameters:
2009-10-08 10:28:26.34 Server      	 -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2009-10-08 10:28:26.34 Server      	 -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2009-10-08 10:28:26.34 Server      	 -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2009-10-08 10:28:26.36 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2009-10-08 10:28:26.36 Server      Detected 1 CPUs. This is an informational message; no user action is required.
2009-10-08 10:28:27.16 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2009-10-08 10:28:27.19 Server      Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2009-10-08 10:28:27.21 Server      The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted.  If you would like distributed transaction functionality, please start this service.
2009-10-08 10:28:27.21 Server      Database Mirroring Transport is disabled in the endpoint configuration.
2009-10-08 10:28:27.22 spid5s      Starting up database 'master'.
2009-10-08 10:28:27.41 spid5s      SQL Trace ID 1 was started by login "sa".
2009-10-08 10:28:27.45 spid5s      Starting up database 'mssqlsystemresource'.
2009-10-08 10:28:27.64 spid5s      Server name is 'HZ_MULTI'. This is an informational message only. No user action is required.
2009-10-08 10:28:27.82 Server      A self-generated certificate was successfully loaded for encryption.
2009-10-08 10:28:27.83 Server      Error: 17182, Severity: 16, State: 1.
2009-10-08 10:28:27.83 Server      TDSSNIClient initialization failed with error 0x7e, status code 0x60.
2009-10-08 10:28:27.83 Server      Error: 17182, Severity: 16, State: 1.
2009-10-08 10:28:27.83 Server      TDSSNIClient initialization failed with error 0x7e, status code 0x1.
2009-10-08 10:28:27.83 Server      Error: 17826, Severity: 18, State: 3.
2009-10-08 10:28:27.83 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2009-10-08 10:28:27.83 Server      Error: 17120, Severity: 16, State: 1.
2009-10-08 10:28:27.83 Server      SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-08 : 10:35:03

netstat -ano -p TCP | findstr TCP_PORT

return empty

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-10-08 : 11:29:19

Resloved it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources