Error 'Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot be impersonated, or you do not have permission' when importing Agent based SQL Server assets.

Products

• Control Compliance Suite
• Control Compliance Suite Standards Database

Issue/Introduction

 In Control Compliance Suite (CCS) you are seeing the following

Error 'Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot be impersonated, or you do not have permission' when importing Agent-based SQL Server assets.

Resolution

 When trying to import SQL Server assets from a CCS 12.6.x agent that has the SQL Snap-in installed, it returns the following error:

EXAMPLE.COM\EXAMPLE: Source [Microsoft SQL-DMO (ODBC SQLState: 42000)]; Description [[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot be impersonated, or you do not have permission.]

 

Check the following to resolve the error:

1. SQL 2012 on Windows 2012 as a default instance running under Local System:
   1. You need to ensure that if the SQL Server service (or the instance) is running under “NT Service\MSSQLServer”, then “NT Authority\System” (NOT “NT Service\MSSQLServer”) has “db_datareader” role to the “master” database. 

2. SQL 2012 on Windows 2012 as a named instance running under Local System:
   1. If you have multiple instances of SQL 2012 installed on the same Windows server AND if the SQL Server service (or the instance) is running under “NT Service\MSSQLServer” or “NT Service\MSSQL$ServerXInstanceX”, then ONLY “NT Authority\System” (and NOT “NT Service\ MSSQL$ServerXInstanceX”) has “db_datareader” role to the “master” database. 

3. SQL 2012 on Windows 2012 as a default or named instance running under Domain or Local user account:
   1. You need to ensure that if the SQL Server service (or the instance) is running under “Mydomain\MyUser”, then “Mydomain\MyUser” (NOT “NT Authority\System”) has “db_datareader” role to the “master” database (as seen in the pictures below)

If “NT Authority\System” or whatever account the SQL Server service is running under DOES NOT have “db_datareader” role, then Asset Import job will return the following error:

EXAMPLE.COM\EXAMPLE: Source [Microsoft SQL-DMO (ODBC SQLState: 42000)]; Description [[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot be impersonated, or you do not have permission.]