PMImport Fails with Failed to process manifest file error

book

Article ID: 181054

calendar_today

Updated On:

Products

Patch Management Solution for Windows Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

Problem

Errors like the following are seen in the Notification Server logs after trying to run PMImport (aka MetaData Import Task or Import Patch Data) or update Vendors and Software:

 

Process: aexsvc.exe
Module: AltirisNativeHelper.dll
Source: Altiris.PatchManagementCore.Tasks.ImportPMResourcesTask.processPMImportXmlFile
Description: Failed to process manifest file http://www.solutionsam.com/imports/6_2/patch/microsoft/PMImport_InvariantLanguage.cab -> Invalid object name 'TempCSDetails'.

 

 

When viewing the Ownership of stored procedures or tables in the database, it is evident that they were not created with the DBO schema.  These can be created as [app id].storedprocedure rather than dbo.storedprocedure. If the Application Identity was DBO, then they would be created as [dbo].storedprocedure.

Cause

Altiris requires that it's AppID or Database Credentials default to the "DBO" schema of its own database by owning the database.   Many confuse the idea of owning the database with the “db_owner” role which is assigned to database users.  Ownership of all SA level logins are by virtue of being an SA, able to login to all databases as the user(schema) “DBO”. Additionally the owner of the database also logs into the database it owns as “DBO”. A SQL login can own a database even if they have no other rights outside of that database on the SQL server. In SQL 2000 only the owner of the database and all all SA level SQL logins use the “DBO” schema as needed by Altiris.  In SQL 2005 a user can be switched to default to a different schema without having to be either the owner of the database or a SA level SQL login. 

While logged into a database using the same login that the Notification Server uses, identify the database user by running “select user” which will return the database user that is logged in.  With SQL 2000 if it does not return "dbo" then the SQL login does not explicitly use the "DBO" schema.
 
The “DBO” user always creates its objects as “dbo.” unless a specific schema is specified. Database users that have “dbo_owner” role rights will have the same permissions as the “DBO” user, but unless specified, they will always use their own database user name for their default schema when creating objects. In addition, unlike the “DBO” user, all other users in a database can be assigned multiple permissions, even overlapping permissions with the more restrictive settings taking precedence.
 
Issues within the Altiris solutions occur when SQL objects get created under a schema other than "DBO". This can be verified by running the following SQL command against the Altiris database; if it returns any results then the objects listed are database objects that have the wrong schema ownership were created because the database login used by Altiris did not have the correct schema.

select name,type from sysobjects where uid>4 
 

Resolution

All the database objects that are not using the “DBO” schema need to be switched to the "DBO" schema.  This can be done either one-at-a-time or by running the attached SQL 2000 script "Delete duplicate sysobjects and change ownership.sql" which will remove duplicates and change all database objects to be owned by the "DBO" schema.   There is also a SQL 2005 version of this script.

To fix one object, run the following command putting in the objects schema name and object name separated by a ".". 

SQL 2000: sp_changeobjectowner 'Schema Name.Object Name','DBO'
 Ex: sp_changeobjectowner '<user>.spCreateKeyIndex','dbo'

SQL 2005 / 2008: Alter Schema dbo Transfer 'Schema Name.Object Name'
 Ex: Alter Schema dbo Transfer <user>.spCreateKeyIndex If the "DBO" schema already has an object by that name, then this procedure will fail; in which one of the versions will have to be deleted.  Generally the one created last should be kept and changed to be owned by "DBO".

 

To prevent other objects from getting created under the wrong schema, the SQL login account used by Altiris needs to be switched to default to the "DBO" schema

For SQL 2000

  1. Open Enterprise Manager
  2. Open the Altiris database
  3. Remove the user that has the same name as the SQL login used by Altiris.
  4. Open Query Analyzer using an account that has SA Rights to the SQL server.
  5. Switch to the Altiris database and execute the following command:

sp_changedbowner 'SQL login Name used by Altiris'


For SQL 2005

  1. Open the SQL Server Management Studio
  2. Open the Altiris database Schemas
  3. Remove the schema that has the same name as the SQL login used by Altiris
  4. Open the Altiris database users
  5. Edit the user with that has the same name as the SQL login used by Altiris
  6. Change this users default schema to "DBO" and save

For SQL 2014

  1. Open the SQL Server Management Studio
  2. Open Security>Logins
  3. Edit the SQL login used by Altiris
  4. Go to User Mapping
  5. Change the Default Schema to dbo
  6. Make sure db_owner is checked

Attachments

Delete duplicate sysobjects and change ownership.sql get_app
Delete duplicate sysobjects and change ownership - SQL 2005 and Above.sql get_app