Create and Run SQL Server Maintenance Plan

Problem:
SQL Server Maintenance plan does not run.
Error Message:
Executed as user: YourServer\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 6:14:46 PM Could not load package "Maintenance Plans\test plan" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E4D (Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.). The SQL statement that was issued has failed. Source: Started: 6:14:46 PM Finished: 6:14:47 PM Elapsed: 0.422 seconds. The package could not be loaded. The step failed.

Solution: (Replace all user account, credential and proxy account names with name you want to use)

1: Create Local User Account on Server with Administrative rights on server (i.e. SqlMaintenanceUser).

2: Create a Credential (SQL Server Management Studio)

A credential is a record containing the authentication information needed to connect to a resource outside of SQL Server. Most credentials consist of a Windows login name and password. On Microsoft Windows Server 2003 and later, the password may not be required.

A single credential can be mapped to multiple SQL Server logins. But a SQL Server login can be mapped to only one credential.

Only users with ALTER ANY CREDENTIAL permission can create or modify a credential.

To create a credential

1. In Object Explorer, expand Security, right-click Credentials, and then click New Credential.

2. In the New Credentials dialog box, in the Credential Name box, type a name for the credential(i.e. SqlMaintenanceCredential).

3. In the Identity box, type the name of the account used for outgoing connections (when leaving the context of SQL Server). Typically, this will be a Windows user account(i.e yourserver\SqlMaintenanceUser). But the identity can be an account of another type.

4. In the Password and Confirm password boxes, type the password of the account specified in the Identity box. If Identity is a Windows user account, this is the Windows password. The Password can be blank, if no password is required.

5. Click OK.

3: Create job executor account (SQL Server Management Studio)

1. In Object Explorer, right-click Security, and then click New Login (i.e. SqlMaintenanceSqlUser).

2. Type your password, default database can be your target database.

3. Server roles: check “sysadmin”

4. User mapping: your target database

Msdb database: you make sure to include SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole

5. Then click OK


4: Configure above User to Create and Manage SQL Server Agent Jobs (SQL Server Management Studio) (Create Proxy)


1. In Object Explorer, expand a server.

2. Expand SQL Server Agent.

3. Right-click Proxies and select New Proxy.

4. On the General page of the New Proxy Account dialog,
-- specify the proxy name (i.e. SqlMaintenanceProxy)
-- credential name (i.e. SqlMaintenanceCredential)
-- and description for the new proxy.

5. Check the appropriate subsystems for this proxy.
-- At minimum select "SQL Server Integration Services Package)

6. On the Principals page, add or remove logins (i.e SQLMaintenanceSqlUser) or roles to grant or remove access to the proxy account.

5: Create Maintenance Plan or Create SSIS package

Create Maintenance Plan
1. In object Explorer, right-click Maintenance Plans, and then click Maintenance Plan Wizard

2. In Wizard
-- Enter name of the plan
-- Select Maintenance Tasks
-- Select databases
-- Select folder to write a report or enter email to email report
-- Finish

Create SISS Package
-- In MS SQL Server Business Intelligence Development Studio, you use job executor account (i.e. SqlMaintenanceSqlUser) to create the SSIS package (DTS) and make sure you can execute this package in SQL Server Business Intelligence Development Studio. Compile/build this package.


6: Create the job, schedule the job and run the job (SQL Server Management Studio)

1. In Object Explorer, Expand SQL Server Agent

2. right-click Jobs, and then click New Job

3. In Wizard
a. Enter name of Job
b. Under Steps, New Step
1 Step name: Step1
2 Type: SQL Server Integration Service Package
3 Run as: proxy created above (i.e. SqlMaintenanceProxy)
4 For Maintenance Plan
-- Package Source: SQL Server
-- Server: your server
-- Use Windows Authentication or Provide SQL Server Authentication
-- Package: plan created above
-- click ok

4 For SISS Pakcage
-- Package source: File System
-- Browse to select your package file xxx.dtsx
-- Click Ok

c. Under Schedules
-- Schedule your job and enable it