Cannot Connect To SQL Server 2005

[SteveDingle]

Hello All,

I have just insalled SQL Server 2005 (including SP's) onto my Windows 2003 Server and can not connect. I am connecting using Windows Authentication and the user is Admin with all domain rights.

I have use SAC to verify:
- Data Engine is running
- SQL Browse is running
- Remote Connection enable via TCP/IP
- Firewall turned off to make sure that wasn't problem

I am new to SS2005 so am not sure where to get more details about what the problem might be, any help appreciated.

Here are details from server about:

SQL Server Management Studio-9.00.3042.00
Analysis Services Client Tools-2005.090.3042.00
Data Access Components (MDAC)-2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
MSXML-2.6 3.0 6.0
Internet Explorer-7.0.5730.13
.NET Framework-2.0.50727.1433
OS-5.2.3790

Toodles,
Steve Dingle

http://www.dsbusinesssolutions.co.uk

D&S Business Solutions Ltd

 

[mrdenny]

What's the complete error message that you are receiving?

Can you post the ERRORLOG as well from about the time that you are connecting?

Have you run through the steps in FAQ faq962-5481?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[SteveDingle]

Hello Denny,

Here is the message I am getting. The error log doesn't seem to be recording anything while trying to log in. The datetime on it is from a few hours ago and I have tried to login a few minutes ago and its quite small so I just cut-n-paste it below.

I hadn't seen that FAQ but when I tried the:
telnet antec2server 1433
.. it failed with 'could not open connection to the host'

Per FAQ I checked the errorlog to find out what port the server was using and found these entries

Server is listening on [ 'any' <ipv4> 3897].
Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLSERVER2005 ].
Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLSERVER2005\sql\query ].
Server is listening on [ 127.0.0.1 <ipv4> 2858].

I tried using the telnet with both 3897 and 2858 and both failed. I am definitely not running a firewall and I can connect to file share folder from another PC.

I am quite naive when it comes to ports etc.. any thoughts on what I should be looking for or where I should be looking

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

Cannot connect to ANTEC2SERVER\SQL2005.

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

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)

------------------------------
For help, click:

http://go.microsoft.com/fwlink?Prod...rver&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

------------------------------
Error Number: -1
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()

ERRORLOG
**********************************
2008-08-15 11:18:31.98 Server Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

2008-08-15 11:18:32.29 Server (c) 2005 Microsoft Corporation.
2008-08-15 11:18:32.29 Server All rights reserved.
2008-08-15 11:18:32.32 Server Server process ID is 1664.
2008-08-15 11:18:32.32 Server Authentication mode is WINDOWS-ONLY.
2008-08-15 11:18:32.37 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2008-08-15 11:18:32.42 Server This instance of SQL Server last reported using a process ID of 2656 at 8/15/2008 11:15:58 AM (local) 8/15/2008 10:15:58 AM (UTC). This is an informational message only; no user action is required.
2008-08-15 11:18:32.42 Server Registry startup parameters:
2008-08-15 11:18:32.53 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2008-08-15 11:18:32.53 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2008-08-15 11:18:32.53 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2008-08-15 11:18:32.89 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2008-08-15 11:18:32.89 Server Detected 2 CPUs. This is an informational message; no user action is required.
2008-08-15 11:18:34.20 Server Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.
2008-08-15 11:18:37.07 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.
2008-08-15 11:18:42.03 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2008-08-15 11:18:42.03 Server The time stamp counter of CPU on scheduler id 1 is not synchronized with other CPUs.
2008-08-15 11:18:42.06 Server The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
2008-08-15 11:18:42.45 Server Database mirroring has been enabled on this instance of SQL Server.
2008-08-15 11:18:42.95 spid5s Starting up database 'master'.
2008-08-15 11:18:43.87 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2008-08-15 11:18:44.73 spid5s SQL Trace ID 1 was started by login "sa".
2008-08-15 11:18:44.84 spid5s Starting up database 'mssqlsystemresource'.
2008-08-15 11:18:44.90 spid5s The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2008-08-15 11:18:47.46 spid5s Server name is 'ANTEC2SERVER\SQLSERVER2005'. This is an informational message only. No user action is required.
2008-08-15 11:18:47.51 spid9s Starting up database 'model'.
2008-08-15 11:18:49.31 spid9s Clearing tempdb database.
2008-08-15 11:18:51.87 Server A self-generated certificate was successfully loaded for encryption.
2008-08-15 11:18:51.90 Server Server is listening on [ 'any' <ipv4> 3897].
2008-08-15 11:18:51.90 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLSERVER2005 ].
2008-08-15 11:18:51.90 Server Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLSERVER2005\sql\query ].
2008-08-15 11:18:51.92 Server Server is listening on [ 127.0.0.1 <ipv4> 2858].
2008-08-15 11:18:51.92 Server Dedicated admin connection support was established for listening locally on port 2858.
2008-08-15 11:18:52.31 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2008-08-15 11:18:54.78 spid9s Starting up database 'tempdb'.
2008-08-15 11:18:56.93 spid12s The Service Broker protocol transport is disabled or not configured.
2008-08-15 11:18:56.93 spid12s The Database Mirroring protocol transport is disabled or not configured.
2008-08-15 11:18:57.39 spid14s Starting up database 'msdb'.
2008-08-15 11:18:57.39 spid15s Starting up database 'AdventureWorksDW'.
2008-08-15 11:18:57.67 spid16s Starting up database 'AdventureWorks'.
2008-08-15 11:18:57.71 spid12s Service Broker manager has started.
2008-08-15 11:18:59.48 spid5s Recovery is complete. This is an informational message only. No user action is required.
2008-08-15 11:22:42.04 Server CPU time stamp frequency has changed from 1101251 to 2194529 ticks per millisecond. The new frequency will be used.
2008-08-15 11:27:23.48 spid51 Using 'xpstar90.dll' version '2005.90.3042' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.


Toodles,
Steve Dingle

http://www.dsbusinesssolutions.co.uk

D&S Business Solutions Ltd

 

[SteveDingle]

Problem solved:

Turns out that in login - Serve name - dropdown list the server to login to was listed as

ANTEC2SERVER\SQL2005

Then I looked closer at what SAC was connected to and it had listed

SQLSERVER2005

So changed above and it worked.

Sorry for the stupid error and wasting your time denny

thanks

Toodles,
Steve Dingle

http://www.dsbusinesssolutions.co.uk

D&S Business Solutions Ltd

 

[mrdenny]

Not a problem. It's usually the simple mistakes that we spend the most time fixing.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog