Server Upgrade Error Due to a Critical Database Script “add_server_role”

Products

• Carbon Black App Control (formerly Cb Protection)

Issue/Introduction

• During the Server upgrade process an error is displayed similar to:

  A critical database script "add_server_role" needed to install the App Control Server failed with an error.

• The ServerInstall-TIMESTAMP.log includes a reference similar to the following

  Cannot find the server principal 'SA', because it does not exist or you do not have permission.

• Clicking "Ok" and continuing the installation completes and the database was successfully upgraded however
  • The Server and Console software were only partially upgraded
  • Some Registry Entries were only partially updated
• Partial upgrades could also result in
  • Scheduled Tasks not completing, with errors in the ReporterLog similar to:

    ERROR DatabaseConnectionWrapper - Database exception: Procedure or function 'ReportScheduledTaskExecution' expects parameter '@last_exec_time', which was not supplied.

  • New features/functionality failing to work properly, with errors in the ReporterLog similar to:

    ERROR DatabaseConnectionWrapper - Database task exception: CPEDataSync - Object reference not set to an instance of an object.
    WARN DatabaseConnectionWrapper - System.NullReferenceException: Object reference not set to an instance of an object.
    at ReporterService.CPEDataSync.DoFetchCPEData(Int32 start_index, String last_query_date_time)
    at ReporterService.CPEDataSync.DoSyncCPEData()
    at ReporterService.CPEDataSync.Process(DataRow data)
    at ReporterService.ProcessingContext.ProcessData(Object source, ElapsedEventArgs e)

Environment

• App Control Server: 8.10.2+

Cause

The error is displayed when the SQL Server doesn't have a login principal with name "SA" configured.

Resolution

1. Verify the Service Account has the correct SQL permissions.
2. Log in to the server with the App Control Service Account
3. If an Agent is installed on the application server temporarily
   • Disable Tamper Protection
   • Move the Agent to Local Approval
4. Open Control Panel > Programs > Uninstall Carbon Black App Control Server
5. Run the Server Installer again > Don't accept the conditions just yet
6. Navigate to the following folder:

   C:\Users\<AppCServiceAcct>\AppData\Local\Temp

7. Order by date modified and find the most recent folders
8. Search for the following file: 

   C:\Users\<AppCService>\AppData\Local\Temp\{XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXX}\{ECD2D524-718A-4A49-8026-337428C013EB}\add_server_role.sql

9. Open the file "add_server_role.sql" in Notepad and find the following file:

   CREATE SERVER ROLE [AppCWebServer] AUTHORIZATION SA;

10. Update the line to: 

    CREATE SERVER ROLE [AppCWebServer];

11. Save the changes to "add_server_role.sql"
12. Continue with the server setup wizard and App Control will install successfully afterwards