[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 642
  • Last Modified:

The report server cannot open a connection to the report server database

I have MS SQL server 2008 on Windows 2008 standard server. It's hosting the reporting service for data warehouse.

Tonight after I applied the Windows patches to the server and reboot the server, then I log into the web portal, I have the following message "...The report server cannot open a connection to the report server database..." shown on the web, then I use MS management studio to open the report MS SQL database, I could not. I have the message "...cannot connect to xxxx..."

Then I check the event log and see the following error
"...Report Server (MSSQLSERVER) cannot connect to the report server database."

"initerrlog: Could not open error log file 'E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'. Operating system error = 5(Access is denied.)."

The, I tried a couple of methods trying to bring up the server started
1. Use the Windows service to start SQL server, but failed with the same message
2. Use cmd command prompt like below, but still cannot bring up the server

>Binn\sqlservr.exe -sMSSQLSERVER
sqlservr.exe -c -s INSTNAME
sqlservr.exe -c


When I use the above command, I did see something recovering but when running to some point, it stops there. I see the log file

.....
2011-10-26 21:00:19.76 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
....
2011-10-26 21:00:24.07 spid15s     Starting up database 'tempdb'.
2011-10-26 21:00:24.27 spid18s     The Service Broker protocol transport is disabled or not configured.
2011-10-26 21:00:24.27 spid18s     The Database Mirroring protocol transport is disabled or not configured.
2011-10-26 21:00:24.27 spid18s     Service Broker manager has started.
2011-10-26 21:00:24.28 spid7s      Recovery is complete. This is an informational message only. No user action is required.



Anyone tell me what is the problem, and how to bring the report database up again.













0
wasabi3689
Asked:
wasabi3689
  • 5
  • 4
3 Solutions
 
MrAliCommented:
Hi,

So you mentioned:
"The, I tried a couple of methods trying to bring up the server started
1. Use the Windows service to start SQL server, but failed with the same message
2. Use cmd command prompt like below, but still cannot bring up the server"

Let's make sure we're on the same page.  Please go to services by clicking on start, run, typing in SERVICES.MSC and hit enter.  Go down to SQL Server (<instancename>).  Is it 'STARTED' status?  If so, what is the "Log On As"?  

If it is started, can you open up SSMS and try to connect to it directly from the server?  Based on what you have listed in your log file, it looks like SQL Server *IS* serving and up now, whether you did it or not I'm not sure, but let's just do this quick check first.
0
 
wasabi3689Author Commented:
>>Let's make sure we're on the same page.  Please go to services by clicking on start, run, typing in >>SERVICES.MSC and hit enter.  Go down to SQL Server (<instancename>).  Is it 'STARTED' status?  >>If so, what is the "Log On As"?  

It's not started when I use service, it pops up a message asking me to check the Windows log. The Windows log tells me the following

initerrlog: Could not open error log file 'E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'. Operating system error = 5(Access is denied.).

>>If it is started, can you open up SSMS and try to connect to it directly from the server?  

I can open SSMS, but when I connect either by the name of the server or [local], cannot connect with the following message

"cannot connect to database server name"

Please help




0
 
MrAliCommented:
Ok, so it's clear your user does not have access to open up the error log to see what is wrong.  You should log in as an admin account and try to open it that way.  Open up a command prompt and type in whoami    It should return your credentials, make sure you're an admin.  

If that doesn't work, it might let you copy it and open it elsewhere, perhaps there's a issue with where it's stored.  

Also, what user is the SQL Server service using to start up?  Do you have the username/password for that account?  We basically need to get your SQL Service to start then we can see what's going on.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
wasabi3689Author Commented:

I type whoami, the command prompt returned domain\myloginname

domain\myloginname is an admin user in Windows server, we use Windows authentication mode in MS SQL server.

In SQL server service, i see the Log On As :This account: Network Service. I don't have username/password for that "Network Service"
0
 
wasabi3689Author Commented:
Update:

I can connect to the server by SSMS, and I can see the drop-down for the server. But, the service is not started and the sql agent is not started, and both has red. When I start it, it gave me the exception handling message.
0
 
MrAliCommented:
Dang,

It looks like your SQL Server installation might be messed up, but what's throwing me for a loop here is the fact that you get a 'access denied' error when you try to open up the error log, and you're a windows admin on this server, which means you shouldn't be getting access denied errors.  Do you know what patches you installed?  You might need to uninstall them to roll them back to see if it resolves your error but I haven't seen many updates hose a system this bad.  

Let's try to copy the error log out and see if u can open it that way, or if you can even copy it.  If you can't copy it, review the NTFS permissions in the security tab under properties of the folder.  Maybe your permissions just got messed up somehow?  Good luck.
0
 
wasabi3689Author Commented:
This problem is automatically fixed, magically this morning. Now everything returned back to normal :)
But, still don't know why the cause.
0
 
MrAliCommented:
Nice!  That's always a relief *AND* scary :)

Perhaps some of the work you did yesterday and maybe a power cycle/service restart or something of the sort needed to occur for the fix to take place.  Either way, I'll be happy to look at the error log if you can copy it out just so we have an idea of what went wrong, for next time you run into this ;)
0
 
wasabi3689Author Commented:
Yesterday night I ran the MSSQL server manually by using command prompt  but, the command prompt screen running and then staying on one point...

sqlservr.exe -c
or
sqlservr.exe

When the command prompt running, I could connect to report sql server database by SSMS on the same machine, not use my workstation's SSMS to connect report server. But the database server still could not be started. However, when I closed the command prompt, I could not connect to report MS SQL server locally from server and  I could not connect to report MSSQL server remotely from my workstation by SSMS, and I have the following error "Unable to load UDL file"

This morning, I closed the command prompt, then I open SSMS to connect to MSSQL report server, I succeeded.

I don'[t know why? Can you tell?

I compared two log file between succeeded log and failed log. The difference is

initerrlog: Could not open error log file 'E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'. Operating system error = 5(Access is denied.).
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now