Link to home
Start Free TrialLog in
Avatar of wasabi3689
wasabi3689Flag for United States of America

asked on

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.













SOLUTION
Avatar of MrAli
MrAli
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wasabi3689

ASKER

>>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




SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

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"
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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This problem is automatically fixed, magically this morning. Now everything returned back to normal :)
But, still don't know why the cause.
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 ;)
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.).