July 1, 2009 at 5:00 pm
Hi Guys,
I am getting this error very frequently , when i am getting connected to SQL Server from command line. But iam able to connect to the Instance using Management Studio with any problems.
Check the ErrorLog but could'nt figure out what is the actual problem.
Can anybody explain how to fix this problem?
Here is the error what am getting!!
C:\>net start mssql$sql_02
The requested service has already been started.
More help is available by typing NET HELPMSG 2182.
C:\>sqlcmd -Smssql$sql_02 -E
HResult 0x35, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53].
Sqlcmd: Error: Microsoft SQL Native Client : 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..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
C:\>
Thanks in Advance.
July 1, 2009 at 5:09 pm
Please go through this KB article
July 1, 2009 at 5:54 pm
Hey Kalyani,
I have enabled the remote connections in my Surface Area Configuration but still the Error persists!!!!
July 1, 2009 at 6:06 pm
Kalyani,
He was mentioning the below 3 things :
* Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.
------->>> I have done this using Surface Configuration Tool
* Turn on the SQL Server Browser service.
-------->>> What is the main use of SQL Server Browser Service??????
* Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.
--------->>> I think, for doing this we need to take help of the System / Network Team.
Thanks!
July 1, 2009 at 6:19 pm
How many instances do you have, if you have more than 1, then you have to do it for that also.
July 1, 2009 at 11:29 pm
I am having only one instance i.e one named instance running on my machine.
July 2, 2009 at 5:41 am
What is the machine name and what is the sql server instance name?
To get SQL Server Instance name please exeucte the following
select serverproperty('InstanceName')
I think you know how to get computer name.
July 2, 2009 at 5:49 am
Here is my machine details:
But how would these help me in fixing the above error??????????
select @@servername
-- output : MAHESH\SQL_01
select serverproperty('instancename')
-- output : SQL_01
July 2, 2009 at 6:02 am
mahesh.vsp (7/2/2009)
Here is my machine details:But how would these help me in fixing the above error??????????
select @@servername
-- output : MAHESH\SQL_01
select serverproperty('instancename')
-- output : SQL_01
Trying connecting with sqlcmd using this instance and let meknow how it goes.
sqlcmd -SMAHESH\SQL_01 -E
July 2, 2009 at 6:36 am
Still the error persists!!!
steps : enable local and Remote connections
using TCP/IP and Named Pipeds
2. Restarted my SQL Server
3. tried to connect using Sqlcmd but failed.
C:\>
C:\>sqlcmd -SMAHESH$SQL_02 -E
HResult 0x35, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53].
Sqlcmd: Error: Microsoft SQL Native Client : 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..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
C:\>
4. i copied the ERRORLOG on to my Desktop and opened it notedpad
2009-07-02 17:55:32.26 Server Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2009-07-02 17:55:32.26 Server (c) 2005 Microsoft Corporation.
2009-07-02 17:55:32.26 Server All rights reserved.
2009-07-02 17:55:32.26 Server Server process ID is 1628.
2009-07-02 17:55:32.26 Server Authentication mode is WINDOWS-ONLY.
2009-07-02 17:55:32.26 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2009-07-02 17:55:32.27 Server This instance of SQL Server last reported using a process ID of 2372 at 7/2/2009 5:49:15 PM (local) 7/2/2009 12:19:15 PM (UTC). This is an informational message only; no user action is required.
2009-07-02 17:55:32.27 Server Registry startup parameters:
2009-07-02 17:55:32.27 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2009-07-02 17:55:32.27 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2009-07-02 17:55:32.27 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2009-07-02 17:55:32.29 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2009-07-02 17:55:32.29 Server Detected 1 CPUs. This is an informational message; no user action is required.
2009-07-02 17:55:32.60 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-07-02 17:55:32.64 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2009-07-02 17:55:34.68 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-07-02 17:55:34.68 Server Database mirroring has been enabled on this instance of SQL Server.
2009-07-02 17:55:34.68 spid5s Starting up database 'master'.
2009-07-02 17:55:35.08 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2009-07-02 17:55:35.40 spid5s SQL Trace ID 1 was started by login "sa".
2009-07-02 17:55:35.56 spid5s Starting up database 'mssqlsystemresource'.
2009-07-02 17:55:35.58 spid5s The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2009-07-02 17:55:36.10 spid5s Server name is 'MAHESH\SQL_02'. This is an informational message only. No user action is required.
2009-07-02 17:55:36.12 spid9s Starting up database 'model'.
2009-07-02 17:55:36.75 Server A self-generated certificate was successfully loaded for encryption.
2009-07-02 17:55:36.78 Server Server is listening on [ 'any' 1154].
2009-07-02 17:55:36.79 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQL_02 ].
2009-07-02 17:55:36.79 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQL_02\sql\query ].
2009-07-02 17:55:36.81 Server Server is listening on [ 127.0.0.1 1155].
2009-07-02 17:55:36.82 Server Dedicated admin connection support was established for listening locally on port 1155.
2009-07-02 17:55:37.84 spid9s Clearing tempdb database.
2009-07-02 17:55:38.73 spid9s Starting up database 'tempdb'.
2009-07-02 17:55:39.03 spid12s The Service Broker protocol transport is disabled or not configured.
2009-07-02 17:55:39.03 spid12s The Database Mirroring protocol transport is disabled or not configured.
2009-07-02 17:55:39.13 spid12s Service Broker manager has started.
2009-07-02 17:56:09.76 Server The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 4. 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-07-02 17:56:09.76 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2009-07-02 17:56:09.80 spid14s Starting up database 'msdb'.
2009-07-02 17:56:09.80 spid15s Starting up database 'ReportServer$sql_02'.
2009-07-02 17:56:09.80 spid16s Starting up database 'ReportServer$sql_02TempDB'.
2009-07-02 17:56:09.80 spid17s Starting up database 'AdventureWorksDW'.
2009-07-02 17:56:12.23 spid15s Starting up database 'AdventureWorks'.
2009-07-02 17:56:13.85 spid5s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
2009-07-02 17:56:13.85 spid5s Recovery is complete. This is an informational message only. No user action is required.
2009-07-02 17:59:33.53 Server CPU time stamp frequency has changed from 606591 to 2533236 ticks per millisecond. The new frequency will be used.
2009-07-02 17:59:52.61 spid52 Using 'xplog70.dll' version '2005.90.3042' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
2009-07-02 17:59:52.71 spid52 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.
Nothing related to error!!!!
5. Also, checked the Windows Event Viewer, nothing is logged.
This is really frustrating!!!!!!!!!!!!!
Hey Kalyani, is there anything that we need to configure in the SQL Server Configuration Manager for connecting through command line. Anything Like Named Pipes anything??????????????????
July 2, 2009 at 6:47 am
Kalyani,
Pl find the attached screen shots which show my network configuration.
Is there anything which need to change the order. If so, pl suggest.
July 2, 2009 at 9:26 am
C:\>
C:\>sqlcmd -SMAHESH$SQL_02 -E
HResult 0x35, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53].
Sqlcmd: Error: Microsoft SQL Native Client : 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..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
C:\>
4. i copied the ERRORLOG on to my Desktop and opened it notedpad
2009-07-02 17:55:32.26 Server Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2009-07-02 17:55:32.26 Server (c) 2005 Microsoft Corporation.
2009-07-02 17:55:32.26 Server All rights reserved.
2009-07-02 17:55:32.26 Server Server process ID is 1628.
2009-07-02 17:55:32.26 Server Authentication mode is WINDOWS-ONLY.
2009-07-02 17:55:32.26 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2009-07-02 17:55:32.27 Server This instance of SQL Server last reported using a process ID of 2372 at 7/2/2009 5:49:15 PM (local) 7/2/2009 12:19:15 PM (UTC). This is an informational message only; no user action is required.
2009-07-02 17:55:32.27 Server Registry startup parameters:
2009-07-02 17:55:32.27 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2009-07-02 17:55:32.27 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2009-07-02 17:55:32.27 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2009-07-02 17:55:32.29 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2009-07-02 17:55:32.29 Server Detected 1 CPUs. This is an informational message; no user action is required.
2009-07-02 17:55:32.60 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-07-02 17:55:32.64 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2009-07-02 17:55:34.68 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-07-02 17:55:34.68 Server Database mirroring has been enabled on this instance of SQL Server.
2009-07-02 17:55:34.68 spid5s Starting up database 'master'.
2009-07-02 17:55:35.08 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2009-07-02 17:55:35.40 spid5s SQL Trace ID 1 was started by login "sa".
2009-07-02 17:55:35.56 spid5s Starting up database 'mssqlsystemresource'.
2009-07-02 17:55:35.58 spid5s The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2009-07-02 17:55:36.10 spid5s Server name is 'MAHESH\SQL_02'. This is an informational message only. No user action is required.
2009-07-02 17:55:36.12 spid9s Starting up database 'model'.
2009-07-02 17:55:36.75 Server A self-generated certificate was successfully loaded for encryption.
2009-07-02 17:55:36.78 Server Server is listening on [ 'any' 1154].
2009-07-02 17:55:36.79 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQL_02 ].
2009-07-02 17:55:36.79 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQL_02\sql\query ].
2009-07-02 17:55:36.81 Server Server is listening on [ 127.0.0.1 1155].
2009-07-02 17:55:36.82 Server Dedicated admin connection support was established for listening locally on port 1155.
2009-07-02 17:55:37.84 spid9s Clearing tempdb database.
2009-07-02 17:55:38.73 spid9s Starting up database 'tempdb'.
2009-07-02 17:55:39.03 spid12s The Service Broker protocol transport is disabled or not configured.
2009-07-02 17:55:39.03 spid12s The Database Mirroring protocol transport is disabled or not configured.
2009-07-02 17:55:39.13 spid12s Service Broker manager has started.
2009-07-02 17:56:09.76 Server The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 4. 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-07-02 17:56:09.76 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2009-07-02 17:56:09.80 spid14s Starting up database 'msdb'.
2009-07-02 17:56:09.80 spid15s Starting up database 'ReportServer$sql_02'.
2009-07-02 17:56:09.80 spid16s Starting up database 'ReportServer$sql_02TempDB'.
2009-07-02 17:56:09.80 spid17s Starting up database 'AdventureWorksDW'.
2009-07-02 17:56:12.23 spid15s Starting up database 'AdventureWorks'.
2009-07-02 17:56:13.85 spid5s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
2009-07-02 17:56:13.85 spid5s Recovery is complete. This is an informational message only. No user action is required.
2009-07-02 17:59:33.53 Server CPU time stamp frequency has changed from 606591 to 2533236 ticks per millisecond. The new frequency will be used.
2009-07-02 17:59:52.61 spid52 Using 'xplog70.dll' version '2005.90.3042' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
2009-07-02 17:59:52.71 spid52 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.
Nothing related to error!!!!
5. Also, checked the Windows Event Viewer, nothing is logged.
This is really frustrating!!!!!!!!!!!!!
Hey Kalyani, is there anything that we need to configure in the SQL Server Configuration Manager for connecting through command line. Anything Like Named Pipes anything??????????????????
[/quote]
Mahesh
You have entered wrong servername
MAHESH$SQL_02 please replace mentioned name with this "Mahesh\SQL_02" and then give a try.
Murthy
July 2, 2009 at 11:31 pm
Finally, we won the battle 🙂
sorry that was my bad.
Thanks for figuring it out.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply