Solved

Sql server

Posted on 2010-08-15
18
300 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
Comment Utility
Check if you Servers service is running in Run -> Services.msc
0
 
LVL 4

Expert Comment

by:Audhi203
Comment Utility
0
 

Author Comment

by:Tecorno
Comment Utility
status of sql server and server agent are blank in services.msc
0
 

Author Comment

by:Tecorno
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Tecorno
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
Local System is fine if not needing to access network resources.
0
 

Author Closing Comment

by:Tecorno
Comment Utility
did not get correct solution
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now