Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

We have several remotes sites at my company and today one of the employees came to me and said their SQL reporting tool stopped working. Up until this point I did not even know this server or database or reporting service existed!

I have RDP access to the server and can open SQL Server Management Studio 2008 R2 and the databases appear to be SQL Server Express. Currently I can login to the instance with my domain admin account but every database gives me the following message when I try to expand it:

enter image description here The webpage they go to for the reports gives them the following error:

•   An error has occurred during report processing. (rsProcessingAborted)
    o   Cannot create a connection to data source 'DNPDataSource'. (rsErrorOpeningConnection)
        ?   For more information about this error navigate to the report server on the local server machine, or enable remote errors

When I try to view the SQL Server Error log I get this error:

enter image description here

I do not have the sa password. I'm not a DBA but need to try and figure this one out, can anyone point me in a direction to start troubleshooting this? I'm completely lost.

here is the ERROR.LOG

2013-03-27 13:14:24.34 Server      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) 
    Jun 11 2012 16:41:53 
    Copyright (c) Microsoft Corporation
    Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

2013-03-27 13:14:24.34 Server      (c) Microsoft Corporation.
2013-03-27 13:14:24.34 Server      All rights reserved.
2013-03-27 13:14:24.34 Server      Server process ID is 9040.
2013-03-27 13:14:24.34 Server      System Manufacturer: 'Intel Corporation', System Model: 'S5520UR'.
2013-03-27 13:14:24.34 Server      Authentication mode is MIXED.
2013-03-27 13:14:24.34 Server      Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG'.
2013-03-27 13:14:24.34 Server      This instance of SQL Server last reported using a process ID of 2428 at 3/27/2013 1:14:02 PM (local) 3/27/2013 7:14:02 PM (UTC). This is an informational message only; no user action is required.
2013-03-27 13:14:24.34 Server      Registry startup parameters: 
     -d c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\master.mdf
     -e c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG
     -l c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\mastlog.ldf
2013-03-27 13:14:24.37 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2013-03-27 13:14:24.37 Server      Detected 16 CPUs. This is an informational message; no user action is required.
2013-03-27 13:14:24.51 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.
2013-03-27 13:14:24.51 Server      Lock partitioning is enabled.  This is an informational message only. No user action is required.
2013-03-27 13:14:24.56 Server      Node configuration: node 0: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x00000000000000ff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2013-03-27 13:14:24.62 spid7s      Starting up database 'master'.
2013-03-27 13:14:24.69 spid7s      2 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
2013-03-27 13:14:24.69 spid7s      0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
2013-03-27 13:14:24.69 spid7s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2013-03-27 13:14:24.78 spid7s      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQLEXPRESS'.
2013-03-27 13:14:24.83 spid7s      SQL Trace ID 1 was started by login "sa".
2013-03-27 13:14:24.85 spid7s      Starting up database 'mssqlsystemresource'.
2013-03-27 13:14:24.87 spid7s      The resource database build version is 10.50.2500. This is an informational message only. No user action is required.
2013-03-27 13:14:25.09 spid10s     Starting up database 'model'.
2013-03-27 13:14:25.09 spid7s      Server name is 'WCCKEMAPP\SQLEXPRESS'. This is an informational message only. No user action is required.
2013-03-27 13:14:25.21 spid10s     The tail of the log for database model is being rewritten to match the new sector size of 4096 bytes.  2560 bytes at offset 99840 in file c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\modellog.ldf will be written.
2013-03-27 13:14:25.31 spid10s     Clearing tempdb database.
2013-03-27 13:14:25.32 spid13s     A new instance of the full-text filter daemon host process has been successfully started.
2013-03-27 13:14:25.37 spid7s      Starting up database 'msdb'.
2013-03-27 13:14:25.40 Server      A self-generated certificate was successfully loaded for encryption.
2013-03-27 13:14:25.40 Server      Server is listening on [ 'any' <ipv6> 54547].
2013-03-27 13:14:25.40 Server      Server is listening on [ 'any' <ipv4> 54547].
2013-03-27 13:14:25.40 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
2013-03-27 13:14:25.40 Server      Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
2013-03-27 13:14:25.40 Server      Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
2013-03-27 13:14:25.56 spid10s     Starting up database 'tempdb'.
2013-03-27 13:14:25.60 spid13s     The Service Broker protocol transport is disabled or not configured.
2013-03-27 13:14:25.60 spid13s     The Database Mirroring protocol transport is disabled or not configured.
2013-03-27 13:14:25.61 spid13s     Service Broker manager has started.
2013-03-27 13:14:25.77 spid7s      The tail of the log for database msdb is being rewritten to match the new sector size of 4096 bytes.  2048 bytes at offset 12007424 in file c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\MSDBLog.ldf will be written.
2013-03-27 13:14:25.84 spid7s      Recovery is complete. This is an informational message only. No user action is required.
2013-03-27 13:14:25.90 Logon       Error: 17187, Severity: 16, State: 1.
2013-03-27 13:14:25.90 Logon       SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again.  [CLIENT: ::1]
2013-03-27 13:14:25.90 Logon       Error: 17187, Severity: 16, State: 1.
2013-03-27 13:14:25.90 Logon       SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again.  [CLIENT: 172.17.0.210]
2013-03-27 13:14:26.48 Server      The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/WCCKemAPP.WCC.LOCAL:SQLEXPRESS ] for the SQL Server service. 
2013-03-27 13:14:26.48 Server      The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/WCCKemAPP.WCC.LOCAL:54547 ] for the SQL Server service. 
2013-03-27 13:14:26.48 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
2013-03-27 13:14:31.04 spid51      Starting up database 'ReportServer'.
2013-03-27 13:14:31.37 spid51      Starting up database 'ReportServerTempDB'.
2013-03-27 13:14:31.76 spid51      Starting up database 'ReportServerTempDB'.
2013-03-27 13:14:32.07 spid51      Starting up database 'ReportServerTempDB'.
2013-03-27 13:14:32.24 Logon       Error: 18456, Severity: 14, State: 5.
2013-03-27 13:14:32.24 Logon       Login failed for user 'drily'. Reason: Could not find a login matching the name provided. [CLIENT: 172.17.0.210]
2013-03-27 13:15:12.28 spid55      Starting up database 'DNP'.
2013-03-27 13:15:13.75 spid55      Starting up database 'DSS'.
2013-03-27 13:19:36.62 spid57      Starting up database 'ReportServerTempDB'.
2013-03-27 13:25:31.18 spid53      Starting up database 'ReportServer$SQLExpress'.
2013-03-27 13:25:36.38 spid53      Starting up database 'DSSDL'.
2013-03-27 13:25:38.89 spid53      Starting up database 'DSSUSERDIR'.
2013-03-27 13:25:41.26 spid53      Starting up database 'permissionsAudit'.
2013-03-27 13:25:45.00 spid53      Starting up database 'PMKemmererProduction'.
2013-03-27 13:25:48.05 spid53      Starting up database 'PMKemmererProductionTEST'.
2013-03-27 13:26:01.57 spid54      Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
2013-03-27 13:26:01.58 spid54      Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2013-03-27 13:26:52.10 Logon       Error: 18456, Severity: 14, State: 38.
2013-03-27 13:26:52.10 Logon       Login failed for user 'WCC\baadmin'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
2013-03-27 13:26:53.37 spid59      Starting up database 'DSSDL'.
2013-03-27 13:26:53.60 spid59      Starting up database 'DSSUSERDIR'.
2013-03-27 13:26:53.92 spid59      Starting up database 'QuietDose'.
2013-03-27 13:26:54.16 spid59      Starting up database 'ReportServer$SQLExpress'.
2013-03-27 13:26:54.36 spid59      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
2013-03-27 13:26:54.66 spid59      Starting up database 'ReportServerTempDB'.
2013-03-27 13:26:54.89 spid59      Starting up database 'STX'.
2013-03-27 13:26:55.57 spid59      Starting up database 'Test'.
2013-03-27 13:26:55.76 spid59      Starting up database 'DSSDL'.
2013-03-27 13:26:55.91 spid59      Starting up database 'DSSUSERDIR'.
2013-03-27 13:26:56.08 spid59      Starting up database 'ReportServer$SQLExpress'.
2013-03-27 13:26:56.31 spid59      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
2013-03-27 13:26:56.52 spid59      Starting up database 'ReportServerTempDB'.
2013-03-27 13:26:56.68 spid59      Starting up database 'STX'.
2013-03-27 13:26:57.24 spid59      Starting up database 'DSSDL'.
2013-03-27 13:26:57.28 spid59      Starting up database 'DSSUSERDIR'.
2013-03-27 13:26:57.45 spid59      Starting up database 'ReportServer$SQLExpress'.
2013-03-27 13:26:57.55 spid59      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
2013-03-27 13:26:57.74 spid59      Starting up database 'ReportServerTempDB'.
2013-03-27 13:26:57.83 spid59      Starting up database 'STX'.
2013-03-27 13:29:36.55 spid54      Starting up database 'ReportServerTempDB'.
2013-03-27 13:39:36.57 spid56      Starting up database 'ReportServerTempDB'.
2013-03-27 13:41:59.55 Logon       Error: 18456, Severity: 14, State: 8.
2013-03-27 13:41:59.55 Logon       Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]
2013-03-27 13:44:07.70 Logon       Error: 18456, Severity: 14, State: 8.
2013-03-27 13:44:07.70 Logon       Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]
2013-03-27 13:49:36.57 spid53      Starting up database 'ReportServerTempDB'.
2013-03-27 13:59:36.57 spid54      Starting up database 'ReportServerTempDB'.
2013-03-27 14:09:36.56 spid53      Starting up database 'ReportServerTempDB'.
2013-03-27 14:15:50.50 spid54      Starting up database 'DSSDL'.
2013-03-27 14:15:50.75 spid54      Starting up database 'DSSUSERDIR'.
2013-03-27 14:15:51.92 spid54      Starting up database 'ReportServer$SQLExpress'.
2013-03-27 14:15:52.25 spid54      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
2013-03-27 14:15:52.51 spid54      Starting up database 'ReportServerTempDB'.
2013-03-27 14:15:52.70 spid54      Starting up database 'STX'.
2013-03-27 14:18:02.83 spid51      Starting up database 'ReportServer$SQLExpress'.
2013-03-27 14:18:46.58 Logon       Error: 18456, Severity: 14, State: 38.
2013-03-27 14:18:46.58 Logon       Login failed for user 'WCC\baadmin'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
2013-03-27 14:18:47.49 spid59      Starting up database 'DSSDL'.
2013-03-27 14:18:47.70 spid59      Starting up database 'DSSUSERDIR'.
2013-03-27 14:18:47.92 spid59      Starting up database 'ReportServer$SQLExpress'.
2013-03-27 14:18:48.04 spid59      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
2013-03-27 14:18:48.33 spid59      Starting up database 'ReportServerTempDB'.
2013-03-27 14:18:48.53 spid59      Starting up database 'STX'.
2013-03-27 14:18:49.12 spid59      Starting up database 'DSSDL'.
2013-03-27 14:18:49.33 spid59      Starting up database 'DSSUSERDIR'.
2013-03-27 14:18:49.44 spid59      Starting up database 'ReportServer$SQLExpress'.
2013-03-27 14:18:49.60 spid59      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
2013-03-27 14:18:49.84 spid59      Starting up database 'ReportServerTempDB'.
2013-03-27 14:18:49.98 spid59      Starting up database 'STX'.
2013-03-27 14:18:50.28 spid59      Starting up database 'DSSDL'.
2013-03-27 14:18:50.39 spid59      Starting up database 'DSSUSERDIR'.
2013-03-27 14:18:50.48 spid59      Starting up database 'ReportServer$SQLExpress'.
2013-03-27 14:18:50.53 spid59      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
2013-03-27 14:18:50.66 spid59      Starting up database 'ReportServerTempDB'.
2013-03-27 14:18:50.73 spid59      Starting up database 'STX'.
2013-03-27 14:19:36.54 spid59      Starting up database 'ReportServerTempDB'.
2013-03-27 14:19:36.93 spid59      Starting up database 'ReportServerTempDB'.
2013-03-27 14:29:36.55 spid53      Starting up database 'ReportServerTempDB'.
2013-03-27 14:39:36.57 spid54      Starting up database 'ReportServerTempDB'.
2013-03-27 14:49:36.57 spid53      Starting up database 'ReportServerTempDB'.
2013-03-27 14:59:36.58 spid54      Starting up database 'ReportServerTempDB'.
2013-03-27 15:09:36.60 spid53      Starting up database 'ReportServerTempDB'.

I can provide older logs too if it will be beneficial. I'm reading those now from here.

Results from query select name, state_desc, is_in_standby, is_cleanly_shutdown from sys.databases;

enter image description here

Image for Kin's request

enter image description here

share|improve this question
1  
You don't even seem to have the option for the SQL Server log there, just Windows NT and Database Mail (which probably won't be very useful). Try to navigate to where the SQL Server error log files are stored. Typically this will be something like: C:\Program Files\Microsoft SQL Server\MSSQLxx.SQLEXPRESS\MSSQL\Log` (or something similar to that) and the log files are ERRORLOG` (or *.1/2/3/4.... for archived log files). – Thomas Stringer Mar 27 at 21:11
@ThomasStringer - Thanks, I found the ERROR.LOG in that location and pasted it above. the DRILY and BAADMIN users are us in IT, I tried to login with a few default sa passwords that did not work. Does anything stick out? – ProfessionalAmateur Mar 27 at 21:15
Depending on your rights, you may not be able to look at very many diagnostic pieces here, but you may try the following query: select name, state_desc, is_in_standby, is_cleanly_shutdown from sys.databases;. Again, if you can't connect to any database, or you don't have the proper permissions that may not be possible. – Thomas Stringer Mar 27 at 21:28
In cases like this I've logged in via the NT LOCAL AUTHORITY system which has been blogged about. This way you get local admin rights and then can add a new SA user or just look around the errors you can't, and see what you need from there: sqlblog.com/blogs/argenis_fernandez/archive/2011/07/10/… With this you'll have full SA rights to the system so be careful, but at least you should get the permissions you need. – Ali Razeghi Mar 27 at 21:34
1  
What Max is saying is described at support.microsoft.com/kb/2624272. This error causes no risk to the database. – Kin Mar 27 at 22:10
show 4 more comments

1 Answer

up vote 0 down vote

From the error log, there are failed logins, are you trying to connect using any of the below ones ?

Login failed for user 'drily'. Reason: Could not find a login matching the name provided. [CLIENT: 172.17.0.210]

Login failed for user 'WCC\baadmin'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

Also, from your screenshot, the report server databases are cleanly shutdown. Do you have auto close option turned ON ?

If you have rights on the server, then below TSQL will help you :

'RESTORE DATABASE <dbname> WITH RECOVERY' or  'ALTER DATABASE <dbname> ONLINE'

What is the output for below SQL:

SELECT name, user_access_desc, is_read_only, state_desc, recovery_model_desc, is_auto_close_on, is_auto_shrink_on
FROM sys.databases;

UPDATE:

I see the problem now ... that auto_close option is turned ON for report server databases. According to BOL: 

When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.

When set to OFF, the database remains open after the last user exits.

Refer to here and here for more detailed explanation.

share|improve this answer
1) The failed login attempts were all by IT personnel today. 2) It looks like Auto Close it turned on for several but not all of the databases. 3) What will that RESTORE SQL statement perform? I don't want to run it in Production just yet without knowing. – ProfessionalAmateur Mar 27 at 22:22
Can you check if autoclose is turned on and provide the result of the TSQL that I asked for ? – Kin Mar 27 at 22:23
Sorry I hit Add comment before I finished the reply above. I'll post an image to the original post with the screen shot of the query results. – ProfessionalAmateur Mar 27 at 22:29
I have updated my answer. Also, as a side note, AUTO_SHRINK is ON as well... which is a bad practice as it will cause more fragmentation. Best explanation ever -- Auto-shrink – turn it OFF! sqlskills.com/blogs/paul/auto-shrink-turn-it-off – Kin Mar 27 at 22:41
Thanks, I will read up on both Auto-Close and Auto-Shrink tonight. Would this cause all of the databases to be inaccessible on the server? I cannot expand any of the databases in the picture above, even the ones with AutoClose and AutoShrink off. – ProfessionalAmateur Mar 27 at 22:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.