When you run an Asset Import job to import SQL assets from a CCS agent, you get the following error message(s):
"Failed to reconcile the asset : <asset_name>. Reason: The following primary field is missing: Domain/Workgroup Name."
or
"Failed to reconcile the asset : <asset_name>. Reason: The following primary field is missing:"
CCS Windows agent with SQL server 2012 (or newer)
For SQL asset import when using a CCS agent to import the SQL asset information, the information is gathered by the agent which runs under 'NT Authority\SYSTEM'. The 'NT Authority\SYSTEM needs to have access to the SQL information on the server to pass back to the CCS Application server.
NOTE: NT AUTHORITY\SYSTEM must have read rights (db_datareader role) on all the databases on the CCS agent that have SQL assets that need to be imported.
In addition to the db_datareader role, the following additional minimum privileges are required by NT Authority\SYSTEM on each CCS agent to import SQL server assets:
Dependent Tables (T)/Stored Procedure (SP) in SQL Server | Master/Current Database | Rights on the dependent database object |
sp_Msdbuserpriv | Master | Execute |
sp_MSdbuseraccess | Master | Execute |
sysprocesses | Master | Select |
sysusers | Master | Select |
syslogins | Master | Select |
xp_regread | Master | Execute |
sysdevices | Master | Select |
sysconfigures | Master | Select |
syscurconfigs | Master | Select |
syslanguages | Master | Select |
sysservers | Master | Select |
fn_helpcollations | Master | Select |
xp_msver | Master | Execute |
sp_helpsort | Master | Execute |
xp_instance_regenumvalues | Master | Execute |
xp_instance_regread | Master | Execute |
sp_server_info | Master | Execute |
xp_loginconfig | Master | Execute |
sp_helpsrvrole | Master | Execute |
xp_servicecontrol | Master | Execute |
Please see a sample grant script that can be used to grant NT Authority\SYSTEM privileges (also required is read rights to the db_datareader role on all the databases) on the CCS agent that have SQL assets that need to be imported.
GRANT EXECUTE ON sp_Msdbuserpriv TO [NT AUTHORITY\SYSTEM]
GRANT EXECUTE ON sp_MSdbuseraccess TO [NT AUTHORITY\SYSTEM]
GRANT SELECT ON sysprocesses TO [NT AUTHORITY\SYSTEM]
GRANT SELECT ON sysusers TO [NT AUTHORITY\SYSTEM]
GRANT SELECT ON syslogins TO [NT AUTHORITY\SYSTEM]
GRANT EXECUTE ON xp_regread TO [NT AUTHORITY\SYSTEM]
GRANT SELECT ON sysdevices TO [NT AUTHORITY\SYSTEM]
GRANT SELECT ON sysconfigures TO [NT AUTHORITY\SYSTEM]
GRANT SELECT ON syscurconfigs TO [NT AUTHORITY\SYSTEM]
GRANT SELECT ON syslanguages TO [NT AUTHORITY\SYSTEM]
GRANT SELECT ON sysservers TO [NT AUTHORITY\SYSTEM]
GRANT SELECT ON fn_helpcollations TO [NT AUTHORITY\SYSTEM]
GRANT EXECUTE ON xp_msver TO [NT AUTHORITY\SYSTEM]
GRANT EXECUTE ON sp_helpsort TO [NT AUTHORITY\SYSTEM]
GRANT EXECUTE ON xp_instance_regenumvalues TO [NT AUTHORITY\SYSTEM]
GRANT EXECUTE ON xp_instance_regread TO [NT AUTHORITY\SYSTEM]
GRANT EXECUTE ON sp_server_info TO [NT AUTHORITY\SYSTEM]
GRANT EXECUTE ON xp_loginconfig TO [NT AUTHORITY\SYSTEM]
GRANT EXECUTE ON sp_helpsrvrole TO [NT AUTHORITY\SYSTEM]
GRANT EXECUTE ON xp_servicecontrol TO [NT AUTHORITY\SYSTEM]
Official documentation can be found on page 133 of the "Security Content Update Getting Started Guide (CCS 12.x)" attached to this KB.