Event 24581, SQLWRITER

Issue

Log Name:   Application
Source:        SQLWRITEREvent ID:     24581
Level:           Error
User:            N/A
Computer:   Description:
Sqllib error:
System table sys.sysdatabases in SQL Server instance  is empty.
Nothing reported in SQL Server error logs.
To reproduce the error, execute vssadmin list writers

Fix
I had removed SQL Server sysadmin permissions from NT AUTHORITY\SYSTEM to enhance security. 

Granting sysadmin to NT AUTHORITY\SYSTEM fixed the problem.

Seems VSS Writer requires sysadmin to work ....  

SQL 2008 Reporting Services Permissions Issue

Issue

In SQL Server 2008 Managment Studio, when attempting to open Security > Roles > {any of the security groups} I get the following error:

Cannot show requested dialog.
Additional Information:
The permissions granted to user 'domain\login' are insufficient for performing this operation (rsAccessDenied)(Report Services SOAP Proxy Source)
The permissions granted to user 'domain\login' are insufficient for performing this operation (rsAccessDenied)(ReportingServicesLibrary)

I am a local administrator on the server.

Fix

When starting SQL Server 2008 Management Studio, choose 'Run As Administrator'. 

DOH!

Broken WMI on SQL Server after restore

Issue

Virtual SQL Server was restored after DR scenario recently.  Databases were recovered and SQL Server up and running.

When attempting to start SQL Server Configuration Manager, the following error was generated:
Cannot connect to WMI provider.  You do not have permission or the server is unreachable.  Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager.  Invalid Namespace [0x8004100e]

Similarly, in Surface Area configuration when attempting to 'configure surface area for localhost', the following error was generated:
Computer localhost does not exist on the network, or the computer cannot be configured remotely.  Verify that the remote computer has the required Windows management Intrumentation components and then try again. (SQLSAC)
Additional Information: SQL Server WMI provider is not available on localhost. (Microsoft.SqlServer.Smo)
Invalid Namespace (System.Management)

Fix

Our WinTel engineer fixed the issue, links below:

Repairing and re-registering the WMI Services
Rebuilding the WMI Repository
If you experience behavior when using WMI, such as application errors or scripts that used to work are no longer working, you may have a corrupted WMI repository. To fix a corrupted WMI repository, you have to reinstall WMI. Follow these steps:

• Click Start, Run and type CMD
• Type this command and press Enter:

net stop winmgmt

• Using Windows Explorer, navigate to %systemroot%\system32\wbem directory and delete the Repository directory. By default, the repository folder is located in the C:\Windows\system32\wbem directory.
• Switch to Command Prompt window, and type:

net start winmgmt
Re-registering the WMI components
The .DLL and .EXE files used by WMI are located in %windir%\system32\wbem. You might need to re-register all the .DLL and .EXE files in this directory. If you are running a 64-bit system you might also need to check for .DLLs and .EXE files in %windir%\sysWOW64\wbem.
To re-register the WMI components, run the following commands at the command prompt:
cd /d %windir%\system32\wbem
for %i in (*.dll) do RegSvr32 -s %i
for %i in (*.exe) do %i /RegServer

http://www.nucleustechnologies.com/Repair-WMI-Services.php

http://blogs.technet.com/b/askperf/archive/2009/04/13/wmi-rebuilding-the-wmi-repository.aspx

SQL2000 Maintenance Plan Error

Error
This error occurs when SQL Server Agent database maintenance (dbcc and index optimisation) job fails on SQL 2000:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'

Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: date
Time: time
User: NA
Computer: servername
Description:
SQL Server Scheduled Job 'Optimizations Job for DB Maintenance Plan 'DB Maintenance Plan3'' (0x385AEFD8A5406044BF854C7011E501AD) - Status: Failed - Invoked on: 2005-06-23 16:05:37 - Message: The job failed. The Job was invoked by User CORPA\Administrator. The last step to run was step 1 (Step 1).
For more information, see Help and Support Center at http://support.microsoft.com.

Fix
Documented in http://support.microsoft.com/kb/902388

Add -SupportComputedColumn parameter to job steps where xp_sqlmaint is executed from.

For example, add the parameters to the command as shown in the following command:

• EXECUTE master.dbo.xp_sqlmaint N'-S ServerName\InstanceName -PlanID <GUID> -WriteHistory -RebldIdx 10 -SupportComputedColumn'
• Integrity Checks Job for DB Maintenance Plan 'DB Maintenance Plan name'
  For example, add the parameters to the command as shown in the following command:
  EXECUTE master.dbo.xp_sqlmaint N'-S ServerName\InstanceName -PlanID <GUID> -WriteHistory -CkDB -SupportComputedColumn'

Noes:
* If you change the configurations in the Properties dialog box of the maintenance plan, the EXECUTE master.dbo.xp_sqlmaint command that is included in the first step of this job will be overwritten.
* The -SupportComputedColumn parameter is a new feature that was introduced in SQL Server 2000 Service Pack 4 (SP4). Therefore, this workaround requires that you have SQL Server 2000 SP4 installed.

Reporting Services - Missing Schedules after DR/migration

Issue & Troubleshooting
After restoring Reporting Services databases to a new SQL instance (and restoring Reporting Services app server from tape) the app support team could not edit subscriptions and subscriptions were not running to schedule.

- All security listed as pre-requisite in RS text had been setup correctly
- Reporting Services was not throwing up any errors
- Reports were visible from the reportserver link in explorer
- Reporting Services was connecting to the new database server (and databases) with no problem

No SQL Agent jobs had been created by reporting services on the new server.  I initially suspected the reports would need to be redeployed and that is when the subscriptions/schedules would be created on the new db server.

However, I found Microsoft text explaining that when a reporting services database is moved, subscriptions and schedules would be updated automatically when the reporting services was repointed to the new database. 

When I logged onto the Reporting Services GUI on the app server, the following error was thrown up:
EXECUTE permission denied on object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'

Fix
Researched and added RSExec required system stored procedure permissions.
Script below.
USE master
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO RSExecRole
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO RSExecRole
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO RSExecRole
GO
USE msdb
GO
-- Permissions for SQL Agent SP's
GRANT EXECUTE ON msdb.dbo.sp_help_category TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_help_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_delete_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO RSExecRole
GO
GRANT SELECT ON msdb.dbo.sysjobs TO RSExecRole
GO
GRANT SELECT ON msdb.dbo.syscategories TO RSExecRole
GO

SQL Services wont start from the Services manager

Issue
In a recent DR scenario, a SQL2000 server was restored from tape backup.  As expected, the flat file backup succeeded but databases were not available and had to be restored from last known good database backup to disk.

I completed the master database restore by starting the SQL Server in single user mode from cmd (using sqlservr.exe) and wanted to restore the rest of the databases afterwards.  I could not start SQL Server using services console, error reported was reported in the eventlog, but not SQL Server errorlog.
The MSSQLSERVER service failed to start due to the following error:
The system cannot find the path specified.

Diagnostics

I could start the service using cmd (sqlservr.exe) so it appeared that something was wrong with the service and where it was pointing to attempt to start sqlservr.exe. 
Also confirming this was the fact that there was no new SQL error log written, indicating that it wasn't finding sqlservr.exe to attempt to start the service.

Fix

The windows engineer had a look and modified the registry for the path of the service (to sqlservr.exe).  Once completed, the services could be started/stopped from services management console.

SQL Reporting Services Errors

Issue
SQL Reporting Services not working on dedicated SQL Reporting Server.

Symptoms and Troubleshooting
When attempting to run SQL Reporting Services configuration manager on an app server with RS installed (databases on different host), an 'invalid namespace' error was reported.

When I had a look, the ReportServerWindowService reported the following error in the application eventlog:
Event ID: 107
Source: Report Server Windows
"Report Server Windows Server (MSSQLSERVER) cannot connect to the report server database"

I attempted to run rsconfig.exe to repoint reporting services to the SQL Server, since the ReportServer and ReportServerTempDB databases had moved.  I got another error:
"No report servers were found on the specified machine."

I figured that since this server was restored from a tape backup, I'd check with the server team to make sure all looked ok at their end (which it wasn't)

Fix

The server team granted the IIS public account list permissions on the %SystemPath%\Temp directory.  SQL Reporting Services is running successfully now.