Solved

Sql server

Posted on 2010-08-15
18
306 Views
Last Modified: 2012-05-10
Hi,

My SQl Server and SQl server agent are not starting.When I execute sqlserver.exe from command prompt I am able to login to server.As soon as I close command prompt it throws error as given below.

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
0
Comment
Question by:Tecorno
  • 9
  • 7
  • 2
18 Comments
 
LVL 4

Expert Comment

by:Audhi203
ID: 33440670
Check if you Servers service is running in Run -> Services.msc
0
 
LVL 4

Expert Comment

by:Audhi203
ID: 33440676
0
 

Author Comment

by:Tecorno
ID: 33440694
status of sql server and server agent are blank in services.msc
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:Tecorno
ID: 33440760
I found this error in event viewer when i try to start services from sql server configuration manager.

initerrlog: Could not open error log file ''. Operating system error = 3(The system cannot find the path specified.).
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33440894
Did this ever work or is this a new installation? Have you installed anything and rebooted just recently? Maybe try backing out the recent change to the system. If this is a new install, you can try repairing or reinstalling SQL as something may have gotten corrupted / omitted on the first attempt.
0
 

Author Comment

by:Tecorno
ID: 33440906
Hi,

This was installed long back and recently sql server is manually stopped in sql server configuration manager and from then on it never started again.As i said in my query it works fine after starting sqlserver.exe from command prompt but once I closr command prompt I loose the connection.

Thanks,
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33440915
How did you manually stop this -- in other words, did you change the services startup from automatic to disabled | manual. Check that you have the service starting with Network Service and and all is set correctly, then try to start from command line (you will find the service short name to use from command line in Services.msc when you go check on the properties specified earlier) with NET START {service short name}.
0
 

Author Comment

by:Tecorno
ID: 33440941

Thanks for your quick response.
I manually stopped by right clicking on sql server in configuartion manager(i dont think i changed it from automatic to manual before stopping)

but now i changed both sql server and sql server agent to manual both are log on as nt authority\network service.

please find below error when i try to run from command line
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\seethap1>net start mssqlserver
The SQL Server (MSSQLSERVER) service is starting..
The SQL Server (MSSQLSERVER) service could not be started.

A service specific error occurred: 17058.

More help is available by typing NET HELPMSG 3547.

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33440974
That is interesting and it has persisted even after rebooting the operating system. Must be something with the error -- hadn't experience it myself, but check this:

http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/383bc31d-5d87-4e07-b2fb-acbfbe5776a1

There was a possibility when stopping the services that the settings for the error log were changed OR this path has become unavailable to SQL either due to permissions changes or path deletion. In the past with NT there were occasional times when NTFS permissions would seemingly get out of whack and reapplying service pack would magically fix things ... haven't seen those kinds of gremlins as much in newer versions of Windows but it never hurts to reapply NTFS permissions even if they appear correct when you look at them in the GUI.
0
 

Author Comment

by:Tecorno
ID: 33441031
Log files are existing in correct path and nt authority\network service.
has full access to that log file.Can you please let me know how to reapply NTFS permissions I have no clue how to apply them.

Thanks,
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33441035
Right click on folder or file and choose properties, then click on the Security tab and then you should be able to reapply the permissions on the file or if folder to all its children.
0
 

Author Comment

by:Tecorno
ID: 33441040
I mean the logfile specified in sql server start up parameters is existing and has full access.
when i start sql server
using below command the error log file gets updated properly

C:\Documents and Settings\seethap1>"C:\Program Files\Microsoft SQL Server\MSSQL1
0.MSSQLSERVER\MSSQL\Binn\sqlservr.exe"
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33441066
But that is probably using your login credentials since you are running the file directly. The SQL service is starting with Network Service therefore you have to ensure that the local accounts like Local System and Network Service have access to read/write/modify in the specified folder in the SQL configuration ... please see the discussion on the error message you were getting at the link provided -- it may make it more clear. At this point you are in normal Windows troubleshooting mode using event viewer message so I would keep tracking this down by the exact content of your event viewer log that appears whenever the startup of service fails.
0
 

Author Comment

by:Tecorno
ID: 33441155

Hi,

I have given full rights to Network service and system and restarted sql server from configuration manager and below is the error details from Event viewer



initerrlog: Could not open error log file ''. Operating system error = 3(The system cannot find the path specified.).

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.


0000: 000042a2 00000010 0000000b 00450044
0010: 00500056 00500041 0044004d 00330043
0020: 00000000 0000
0
 

Author Comment

by:Tecorno
ID: 33441296
I am able to start the service after changing log on as local system from Network service.
I could not make this change in sql server configuration manager (as I am getting WMI error).
I went to administrativetools\services and changed on sql server and sql server agent properties.

But now the services are running under local system.Do you see any issue here?
I tried to revert back to nt authority\network service but it is asking for password which i don thave.

Thanks for all your help.Appreciated.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33441298
Double-check that there is not a NTFS permission above the level of the errorlog folder that is blocking access. Maybe the rights are set appropriately at that folder, but Network Server | System aren't allowed at top level drive; therefore, they can't traverse down to that folder. I would set all the permissions and restart Windows and not just SQL.

Everything I am reading on the error is due to lack of permissions or incorrect path like with a cluster where the directory | resource isn't available to both servers necessarily so typically is fixed by setting the service to manual so that it only tries to load this when the cluster has failed over and so log directory is available.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 33441304
Local System is fine if not needing to access network resources.
0
 

Author Closing Comment

by:Tecorno
ID: 33686524
did not get correct solution
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question