Security Manager SQL jobs are missing. (NETIQKB71737)

After backing up and restoring the Security Manager database, the default Security Manager SQL jobs are missing.

SM SQL database jobs are missing.

To correct this issue, please download NETIQKB71737.zip which is linked below in this article.  Once downloaded, complete the following steps:

1) Extract the NETIQKB71737.zip into a directory on your SQL server, or where the SQL server can access the files.
2) Open SQL Server Management Studio

For the One point server jobs:

1) Click on the New Query button.
2) Change the database to MSDB.
3) Copy/paste the script RecreateOnePointJobs.sql into the new query.  * If you have removed or renamed SA, then you wil need to change each instance of SA in the script to the service account name.
4) Click on the Execute button.
5) Once this is finished, right-click on the SQL Server Agent Jobs link and click Refresh.  The jobs will now show up properly and are already scheduled to run.

For the NetIQ_SSIS job:

1) Click on the New Query button
2) Change the database to MSDB.
3) Copy/paste the script ssis-build.sql into the new query.
4) Before you execute the job, you will need to check the following:

a. Line 24, you will need to replace SECMANLAB\smservice  with the domain/username for your service account.
b. Line 38, contains 5 entries for server names:

i. SERVER  replace SECM04 with your SQL server name  (ie smdb01)
ii. SqlServerName replace SECM04 with your SQL servername\instance  (ie smdb01\sm or smdb01)
iii. Data Source replace SECM04 with your reporting server name\instance   (ie smreport01\sm or smreport01)

c. Line 41, contains the proxyname, replace it with a valid proxyname.  You can find/add a valid name by:

i. Expand SQL Server Agent
ii. Expand Proxies
iii. Expand SSIS Package Execution
iv. If no valid id exists, righ-click and create a new proxy

1. Proxy name and Credential name should be formatted as Domain\username.
2. For subsystem, check SQL Server Integration Services Package and click ok

5) Execute.  If you get errors, correct them and retry.
6) Open a new query (as before)
7) Copy/paste SSIS-Performance.txt into the new query.
8) Execute.
9) Click the Connect button
10) Select Integration Services
11) Expand Stored Packages
12) Right-click on MSDB
13) Click Import package
14) Change location to File System
15) Put in the package path. Or browse to it. (This is where you put the CubeTransferPackage.dtsx)
16) Give the package a name (Default should be CubeTransferpackage) and click OK.
17) Once this is done,go back up to SQL Server Agent and refresh it.
18) Expand SQL Server Agent
19) Expand Jobs
20) Right-click on NetIQ_SSIS
21) Click Properties
22) Click Steps
23) Click Edit
24) At the bottom of the General tab, should be listed the cubetransfer package.  Click the dots to browse for the package.
25) Select the package/location and click OK.
26) Click Data Sources
27) Validate the server names for the 5 jobs (One point and Cubedepot are usually on the same sql server, and the other 3 may reside on a different server that is running SQL Analysis Services) and click OK.
28) Press Ok until you are out of the job and back to SQL Server management Studio.

At this point, you should be done and ready to test running the SSIS job by right-clicking it, and selecting Start Job at Step.

This can be caused by reinstalling SQL or moving SQL servers.

Formerly known as NETIQKB71737

Please note that you should always make sure that you have current backups of your databases.

NETIQKB71737.zip