November 30, 2009 at 6:45 pm
I have SQL 2005 64 on a remote VMWare server. Everything's been fine for months, worked on it from home over the weekend for a little bit. I have also had SQL Maestro for SQLite installed for several months.
This morning, after having a problem with SQL Maestro, upgrading it and rebooting, SQL Server 2005 fails to connect with:
"Error occured during pre-login handshake.TCP Providor. An existing connection was forcibly closed by the remote host"
I also notice that the SQLSERVER Agent Service will not start. There have not been any network changes, TCP/IP is enabled, I don't see what could cause the problem.
Thoughts ?
EDIT: Error Log Info Posted Below.
RESOLUTION: The Network Admin found a problem with the Encryption Certificate being corrupted, so there was no handshake between SQL services and the OS
"Thank You" to everyone who tried to help me with this.
November 30, 2009 at 9:04 pm
Could you tell us in detail about windows firewall and which tcp port sql server was listening in past. Did you check event logs for more info.
November 30, 2009 at 11:43 pm
start sql browser service...
December 1, 2009 at 6:43 am
I don't know much about the settings .. where should I look ? I think the port is the default.
There are some errors showing after the reboot yesterday:
"Initiator failed to connect to the target. Target IP address and TCP Port number are given in dump data." and
"iSCSI discovery via SendTargets failed 0xefff0003 to target portal 123.456.789.10 0003260 Root\SCSIIADAPTER\0000_0"
SQL Browser is already started.
December 1, 2009 at 12:20 pm
Here is the SQL Error Log (with some details made generic)
About 2/3 down there are errors about file location on "E" of one .MDF, But the file does exist in that location. Nothing has changed on months, so I don't know why SQL can't see it. The E drive is on a SAN, while the F drive is local.
2009-11-30 12:08:57.95 Server Microsoft SQL Server 2005 - 9.00.4035.00 (X64)
Nov 24 2008 16:17:31
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
2009-11-30 12:08:58.01 Server (c) 2005 Microsoft Corporation.
2009-11-30 12:08:58.01 Server All rights reserved.
2009-11-30 12:08:58.01 Server Server process ID is 1860.
2009-11-30 12:08:58.03 Server Authentication mode is MIXED.
2009-11-30 12:08:58.04 Server Logging SQL Server messages in file 'F:\MSSQL\DATA\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2009-11-30 12:08:58.06 Server This instance of SQL Server last reported using a process ID of 1848 at 11/30/2009 12:06:38 PM (local) 11/30/2009 5:06:38 PM (UTC). This is an informational message only; no user action is required.
2009-11-30 12:08:58.07 Server Registry startup parameters:
2009-11-30 12:08:58.09 Server -d F:\MSSQL\DATA\MSSQL.1\MSSQL\DATA\master.mdf
2009-11-30 12:08:58.10 Server -e F:\MSSQL\DATA\MSSQL.1\MSSQL\LOG\ERRORLOG
2009-11-30 12:08:58.10 Server -l F:\MSSQL\DATA\MSSQL.1\MSSQL\DATA\mastlog.ldf
2009-11-30 12:08:58.25 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2009-11-30 12:08:58.25 Server Detected 8 CPUs. This is an informational message; no user action is required.
2009-11-30 12:08:58.32 Server Large Page Extensions enabled.
2009-11-30 12:08:58.33 Server Large Page Granularity: 2097152
2009-11-30 12:08:58.33 Server Large Page Allocated: 32MB
2009-11-30 12:08:59.06 Server Using locked pages for buffer pool.
2009-11-30 12:08:59.90 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-11-30 12:09:00.75 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2009-11-30 12:09:01.06 Server Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2009-11-30 12:09:01.14 Server Database mirroring has been enabled on this instance of SQL Server.
2009-11-30 12:09:01.30 spid5s Starting up database 'master'.
2009-11-30 12:09:01.81 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2009-11-30 12:09:02.16 spid5s SQL Trace ID 1 was started by login "sa".
2009-11-30 12:09:02.26 spid5s Starting up database 'mssqlsystemresource'.
2009-11-30 12:09:02.28 spid5s The resource database build version is 9.00.4035. This is an informational message only. No user action is required.
2009-11-30 12:09:02.84 spid5s Server name is 'DB164'. This is an informational message only. No user action is required.
2009-11-30 12:09:02.84 spid9s Starting up database 'model'.
2009-11-30 12:09:03.08 spid9s Clearing tempdb database.
2009-11-30 12:09:03.29 spid9s Starting up database 'tempdb'.
2009-11-30 12:09:03.31 Server The certificate was successfully loaded for encryption.
2009-11-30 12:09:03.34 Server Server is listening on [ 'any' <ipv4> 1433].
2009-11-30 12:09:03.34 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2009-11-30 12:09:03.34 Server Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2009-11-30 12:09:03.35 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2009-11-30 12:09:03.35 Server Dedicated admin connection support was established for listening locally on port 1434.
2009-11-30 12:09:03.45 spid12s The Service Broker protocol transport is disabled or not configured.
2009-11-30 12:09:03.45 spid12s The Database Mirroring protocol transport is disabled or not configured.
2009-11-30 12:09:03.48 Server The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2009-11-30 12:09:03.49 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2009-11-30 12:09:03.64 spid12s Service Broker manager has started.
2009-11-30 12:09:04.04 spid25s Starting up database 'Incidents'.
2009-11-30 12:09:04.04 spid26s Starting up database 'Reservation_Development'.
2009-11-30 12:09:04.04 spid27s Starting up database 'Ensemble'.
2009-11-30 12:09:04.04 spid24s Starting up database 'Common'.
2009-11-30 12:09:04.04 spid29s Starting up database 'WarehouseGeneric'.
2009-11-30 12:09:04.04 spid22s Starting up database 'msdb'.
2009-11-30 12:09:04.04 spid28s Starting up database 'Archive_2009'.
2009-11-30 12:09:04.04 spid30s Starting up database 'Development_Vendor'.
2009-11-30 12:09:04.04 spid32s Starting up database 'Warehouse_Houston'.
2009-11-30 12:09:04.04 spid31s Starting up database 'Warehouse_Atlanta'.
2009-11-30 12:09:04.04 spid33s Starting up database 'Warehouse_Livonia'.
2009-11-30 12:09:04.04 spid34s Starting up database 'Warehouse_Baltimore'.
2009-11-30 12:09:04.04 spid35s Starting up database 'Warehouse_Pennington'.
2009-11-30 12:09:04.05 spid36s Starting up database 'Warehouse_Pennsauken'.
2009-11-30 12:09:04.05 spid37s Starting up database 'Warehouse_Union'.
2009-11-30 12:09:04.04 spid30s Error: 17207, Severity: 16, State: 1.
2009-11-30 12:09:04.04 spid30s FCB::Open: Operating system error 3(The system cannot find the path specified.) occurred while creating or opening file 'E:\MSSQL.1\MSSQL\Data\Development_Vendor.mdf'. Diagnose and correct the operating system error, and retry the operation.
2009-11-30 12:09:04.06 spid30s Error: 17204, Severity: 16, State: 1.
2009-11-30 12:09:04.06 spid30s FCB::Open failed: Could not open file E:\MSSQL.1\MSSQL\Data\Development_Vendor.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
2009-11-30 12:09:04.06 spid30s Error: 5120, Severity: 16, State: 101.
2009-11-30 12:09:04.06 spid30s Unable to open the physical file "E:\MSSQL.1\MSSQL\Data\Development_Vendor.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
2009-11-30 12:09:04.17 spid30s Error: 17207, Severity: 16, State: 1.
2009-11-30 12:09:04.17 spid30s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:\MSSQL.1\MSSQL\LOG\Development_Vendor_1.ldf'. Diagnose and correct the operating system error, and retry the operation.
2009-11-30 12:09:04.17 spid30s File activation failure. The physical file name "E:\MSSQL.1\MSSQL\LOG\Development_Vendor_1.ldf" may be incorrect.
2009-11-30 12:09:06.00 spid26s CHECKDB for database 'AllstateIBReservation_Development' finished without errors on 2009-03-15 20:12:11.027 (local time). This is an informational message only; no user action is required.
2009-11-30 12:09:13.08 spid28s CHECKDB for database 'Archive_2009' finished without errors on 2009-05-27 02:12:31.967 (local time). This is an informational message only; no user action is required.
2009-11-30 12:09:13.10 spid5s Recovery is complete. This is an informational message only. No user action is required.
2009-11-30 13:10:25.31 Server SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
2009-11-30 13:10:26.55 spid12s Service Broker manager has shut down.
2009-11-30 13:10:26.55 spid12s Error: 17054, Severity: 16, State: 1.
2009-11-30 13:10:26.55 spid12s The current event was not reported to the Windows Events log. Operating system error = 1717(The interface is unknown.). You may need to clear the Windows Events log if it is full.
2009-11-30 13:10:26.58 Server The connection has been lost with Microsoft Distributed Transaction Coordinator (MS DTC). Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) will begin once the connection is re-established. This is an informational message only. No user action is required.
2009-11-30 13:10:26.62 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
December 1, 2009 at 12:34 pm
Are the other databases located in the same directory?
December 1, 2009 at 12:34 pm
Are you sure that the SAN LUN was present and available at the time the SQL Service started? Could it have been that the drive only became available afterwards?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2009 at 12:40 pm
Lynn Pettis (12/1/2009)
Are the other databases located in the same directory?
All the other databases are located on F, just the 1 problem database on E.
GilaMonster (12/1/2009)
Are you sure that the SAN LUN was present and available at the time the SQL Service started? Could it have been that the drive only became available afterwards?
I can't find any problems with the drive. If I log onto the server, I can see the E and F drives fine, there don't appear to be any permission issues.
December 1, 2009 at 12:48 pm
homebrew01 (12/1/2009)
I can't find any problems with the drive. If I log onto the server, I can see the E and F drives fine, there don't appear to be any permission issues.
Sure, the drives are there now. Was the E drive present and accessible at the time that the SQL Server Service started? Is it possible that the drive came online after SQL started but before you checked? Anything in the Windows Event logs
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2009 at 12:53 pm
I've rebooted several times, and the drives have been available. Not sure about drive availability when the problem first appeared.
The errors in post #4 came from the event log.
If SQL can't find a particular file or drive for a non-system DB, wouldn't SQL still start ok, but just give an error on that database, showing as "suspect" or something ?
December 1, 2009 at 1:04 pm
homebrew01 (12/1/2009)
If SQL can't find a particular file or drive for a non-system DB, wouldn't SQL still start ok, but just give an error on that database, showing as "suspect" or something ?
Sure, and from the error log that's what happened
At 12:09 there's the message about the inaccessible file. After that, SQL's running for an hour. At 13:10 SQL stops due to a server shutdown. During that hour if any logins had that as their default DB, the login would fail. Logins with other default DBs should still succeed.
Pre-login failures wouldn't show up in the error log because they occur before the login. Check for errors in the windows event log, system and security. Last time I saw this it was a windows-level network problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2009 at 1:28 pm
Just out of curiosity, have you tried just shutting down and restarting SQL Server, no server reboot?
December 1, 2009 at 1:39 pm
Lynn Pettis (12/1/2009)
Just out of curiosity, have you tried just shutting down and restarting SQL Server, no server reboot?
I had, through the Services screen. I just tried again, through SQL Configuration manager, and both services started this time !. However, I still get the error trying to open SSMS to the server.
For fun, I thought I'd try installing SP3 in case it would fix something, but I don't get far before failing on User Authentication:
[font="Courier New"]login failed : HResult 0xE9. Level 16 State 1 shared memory providor no process is on the other end of the pipe. Sqlcmd: Error: MS Native Client: Client unable to establish connection.[/font]
The pop-up box says: "Your Account Information Could not be Verified... blah blah"
December 1, 2009 at 2:18 pm
homebrew01 (12/1/2009)
Lynn Pettis (12/1/2009)
Just out of curiosity, have you tried just shutting down and restarting SQL Server, no server reboot?I had, through the Services screen. I just tried again, through SQL Configuration manager, and both services started this time !. However, I still get the error trying to open SSMS to the server.
For fun, I thought I'd try installing SP3 in case it would fix something, but I don't get far before failing on User Authentication:
[font="Courier New"]login failed : HResult 0xE9. Level 16 State 1 shared memory providor no process is on the other end of the pipe. Sqlcmd: Error: MS Native Client: Client unable to establish connection.[/font]
The pop-up box says: "Your Account Information Could not be Verified... blah blah"
In the SQL Server Configuration Manager, what protocols are enabled for the SQL Server instance?
December 1, 2009 at 2:34 pm
Lynn Pettis (12/1/2009)
In the SQL Server Configuration Manager, what protocols are enabled for the SQL Server instance?
"TCP\IP only" ... our usual setting
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply