Troubleshooting Windows Azure Pack & Usage (Part 2)

In the previous post we talked about how you can verify if Usage and Metring is working

In this blog post it’s time to see what could go wrong during installation / configuration of Usage & Metering

The following are potential issues you might run into:

  • SPF cannot connect to OM DW.
  • WAP Usage is failing: Error: 404 (Not Found), 401 (Authentication failed).
  • Service Reporting shows “401 Authentication Failure” from WAP Usage API.
  • Service Reporting SQL Job is failing.
  • Processing Cubes are failing.
  • Databases or Cubes are not showing when connecting PerformancePoint to Service Reporting DW.

Troubleshooting Usage

After installing the end to end solution several times, I have run into different challenges which I will explain below, with a solution to the problem.

SPF cannot connect to OM DW:

Error: LastId does not increase in IIS Log on SPF Server.

 

Cause: Normally if SPF can’t access Operations Manager DW you will see that LastId will stay the same in ISS log on the SPF Server.

2013-11-18 13:21:13 192.168.1.11 GET /usage/usage lastId=14966&batchSize=10000 8090 Contoso\!spf 192.168.1.40 - - 200 0 0 1843

2013-11-18 13:23:21 192.168.1.11 GET /usage/usage lastId=14984&batchSize=10000 8090 Contoso\!spf 192.168.1.40 - - 200 0 0 531

2013-11-18 13:32:21 192.168.1.11 GET /usage/usage lastId=14984&batchSize=10000 8090 Contoso\!spf 192.168.1.40 - - 200 0 0 46

2013-11-18 13:41:20 192.168.1.11 GET /usage/usage lastId=14984&batchSize=10000 8090 Contoso\!spf 192.168.1.40 - - 200 0 0 46

As it shows above no more data is coming in as LastID keeps being the same for over several hours and the report in OM VM,, shows data from this time period.

 

Solution:

One of the following reasons could cause the data import to fail:

    • Running under the wrong user account or the Usage Service is not running.
    • SPF is not pointing to the OMDW database.
    • SPF App-User does not have the needed rights to OMDW.

To verify what could be wrong do the following:

 

Running under the wrong user or Usage Service is not running:

When SPF is extracting data from Operations Manager DW it will do it with the credentials that the SPF Web service is running. If the SPF Web Service is running under Network Service it cannot get access to OMDW.

 

To verify that the SPF Web Service is running under the right service account do the following:

1. Login to the SPF server as an administrator.

2. Start IIS Manager.

3. Expand SPF Server > Sites and verify that SPF shows in the list.

4. Select Applications Pools under connection menu.

5. Verify that Usage is running under a domain user and that the Web Service is started:

   

 

SPF is not pointing to the OMDW database

To verify if SPF is pointing to the right Database to the following:

1. Logon to the SPF server as an Administrator.

2. Open PowerShell console with administrative privilege.

3. Run the following command:

   Import-module spfadmin

   Get-SCSPFSetting

This command should return an output similar to below:

  

From the output we want to look for the following

Name: Should be the name of the Server holding the Operations Manager Data Warehouse Database.

SettingString: Should contain the Name of the Operations Manager Data Warehouse Database Server as well as the Database name.

Server: Should contain the name of the Operations Manager RMS Server (First installed OM Server).

If these settings are not correct please follow the steps described in this blog post: Configuring VMM and OM for IaaS usage and metering.

 

SPF App-User does not have the needed rights to OMDW

To verify SPF App-User has the needed rights in the OMDW database do the following:

1. Open SQL Management Studio.

2. Connect to the SQL Server listed when you ran the Get-SCSPFSetting PowerShell cmdlet (in our example, the “Data source= “ eg. DB04.contoso.com)

3. Expand Databases and verify that there is a database called OperationsManagerDW (Default name)

   

4. Expand Security > Users and verify that the user which SPF App pool is running under is listed:

  

5. Double click on the user, select User Mapping > OperationsManagerDW and verify that the user has minimum the following rights:

  

6. Lastly select Status and verify that the user is enabled

  

If the user does not exists in SQL Database, the SPF App-pool domain user will have to be created and assigned the permissions listed above.

To verify that SPF is getting data from OM DW, go back to the IIS log on the SPF Server to verify that the LastId number has increased:

2013-11-18 13:32:21 192.168.1.11 GET /usage/usage lastId=14984&batchSize=10000 8090 Contoso\!spf 192.168.1.40 - - 200 0 0 46

2013-11-18 13:41:20 192.168.1.11 GET /usage/usage lastId=14984&batchSize=10000 8090 Contoso\!spf 192.168.1.40 - - 200 0 0 46

2013-11-18 16:03:39 192.168.1.11 GET /usage/usage lastId=15020&batchSize=10000 8090 Contoso\!spf 192.168.1.40 - - 200 0 0 3469

WAP Usage is failing: Response status code does not indicate success: Error: 404 (Not Found), Error 401 (Unauthorized), Unable to connect to the remote server in WAP Server Windows Event log.

Error: Error: 404 (Not Found), Error 401 (Unauthorized), Unable to connect to the remote server in WAP Server Windows Event log.

Cause: This error could be caused by the following things:

  • Error 404 (Not Found) / Unable to connect to the remote server normally indicates that WAP Usage does not point to the right SPF Service Location or SPF Web Services are not running
  • Error 401 (Unauthorized) The user Specified when registering WAP Usage does not have the needed rights in SPF.

 

Solution: Verify that the right permissions are given and that the right path is given to the web service. To verify or fix these errors do the following:

1. Login to the WAP Admin Portal.

2. Select VM Clouds.

3. Click on the icon with the lightning clip_image001under VM Clouds.

4. Click on Currently Registered Service Provider Usage endpoint:

5. Verify the setting shown in the REGISTER window.

  

6. Logon to the SPF Server.

7. Start IIS Manager.

8. Expand Sites and verify SPF shows under sites.

9. Expand SPF and verify Usage shows under SPF.

10. Verify the port (8090 by default) that SPF is running under “Action” Menu and verify that the website is running.

11. Select Applications and verify that Usage Application Pool is running.

  clip_image001[4]  clip_image002  clip_image003

12. Open Computer Management.

13. Expand System Tools > Users.

14. Locate the user specified under Service Provider Usage.

15. Double click on the user and select Member Of tab.

16. Verify that the user is member of SPF_ local groups.

 

   If not add the SPF_ groups to the user.

17. On the WAP Server start a PowerShell prompt.

18. Run the following command to verify connection between WAP server and SPF is open in the firewall (Windows 2012 R2 only):

   Test-NetConnection <SPF Server> -port 8090 for example: Test-NetConnection vmm01 -port 8090

19. Start WAP Portal.

20. Select VM Clouds.

21. Select clip_image001[6]under VM Clouds.

22. Click on Currently registered Service Provider Usage endpoint:

23. Verify that the following format is used:

Protocol: HTTPS

SPF Server: VMM01 (SPF is installed on the VMM01 server)

Port: 8090

Service: Usage

Example Https://VMM01:8090/Usage

User: VMM01\SPF

Password: <Password for local user>

 

Service Reporting shows “401 Authentication Failure” from WAP Usage API

Error: 401 Authentication Failure.

 

Cause: The user for the WAP Rest API has not been set and therefor the Web Service will return “401 Authentication Failure”

Solution: Set password for the web service:

When WAP Usage Web Service is installed, a random user and password is assigned to the WAP Usage API.

In order for Service Reporting to extract data from WAP Usage API a new User and Password needs to be set for WAP Usage.

This error will typically show if the WAP Usage user and password have not been set.

The Error would normally look like this: Katal Usage Data Extraction - Failed with ErrorMessage: The remote server returned an error: (401) Unauthorized.

To set the WAP Usage User, do the following:

1. Logon on to WAP Server and Start Windows PowerShell as Administrator

2. Load the WAP PS Module:

Import-module MgmtSvcAdmin

3. Set the User Name for the WEB Service:

Set-MgmtSvcSetting -Namespace UsageService -Name Username -Value '<User Name>'

Example: Set-MgmtSvcSetting -Namespace UsageService -Name Username -Value 'SC'

4. Set the password for the user:

Set-MgmtSvcSetting -Namespace UsageService -Name Password –Value ‘<Password>' –Encode

Example: Set-MgmtSvcSetting -Namespace UsageService -Name Password -Value 'Password1' –Encode

Note: The Post deployment config script will have to be run again in order to update the User and Password, to do this do the following:

 

5. Login on the Server where Service Reporting is installed.

6. Open Windows PowerShell as an administrator.

7. Navigate to the <InstallationDrive>:\Program Files\Microsoft System Center 2012 R2\Service Reporting\Maintenance folder. For example, type cd “c:\Program Files\Microsoft System Center 2012 R2\Service Reporting\Maintenance” and then press ENTER.

8. Run the following command to configure Service Reporting to talk to Operations Manager and WAP Usage REST API:

.\PostDeploymentConfig.ps1 –User <User from previous command> –Password <Password from previous step>

Example: .\PostDeploymentConfig.ps1 –User SC -Password Password1

 

Service Reporting SQL Job is failing

Error: Execution of Job “Service Reporting DW System Job” failed.

   

Cause: The SQL Job may fail if it can’t get access to the OM DB, this would cause the Service Reporting DW System Job to fail, and never get to the second part of the job which would be extracting WAP Usage data.

Solution: Change SQL Agent Service to run under a domain user that has access to Operations Manager Database.

As the SQL Agent is executing the Service Reporting DW System Job in context of the credentials that the Windows Service is running under, we want to verify that the SQL Agent is running and that the SQL Agent is running with credentials that has access to Operations Manager Database.

To verify or fix these error do the following:

1. Logon to the Database Server where the Service Reporting Database is running as Administrator.

2. Start SQL Server Configuration Manager.

3. Select SQL Server Services.

  

4. Select SQL Server, right click and select Properties.

5. Select “This Account” and browse for a Domain user.

6. Specify Password.

7. Select OK and Click Yes to restart the service.

   The SQL Agent should now run under a new service.

 

8. Start SQL Management Studio.

9. Connect to the SQL Instance that holds the Operations Manager Database.

10. Expand Security > Logins.

11. Right Click on Logins and Select New Login.

12. Select the User that SQL Agent for Service Reporting for Login Name.

13. Select User Mapping > OperationsManager and select db_datareader.

 

14. Click OK to create the user.

15. In SQL Management Studio connect to the instance holding the Service Reporting databases.

16. Restart the SQL Agent.

17. Select SQL Server Agent and expand Jobs.

18. Right Click on Service Reporting DW System Job and Select “Start Job at Step.. ”, Click Start.

19. Verify that the job status is success for all steps.

   

20. Close SQL Management Studio.

Processing Cubes are failing

Error: Internal error: The Operation terminated unsuccessfully

Cause: Analysis Service does not have the required rights to access the UsageDatawarehouseDB database. By default Analysis services will run under a NT Service account, which does not have access to the SQL instance holding the UsageDatawarehouseDB database.

Solution: Modify the Analysis Service on the SQL Server holding the Service Reporting Analysis Services.

To verify or fix this error do the following:

1. Logon to the Server running SQL Analysis Services as Administrator.

2. Start SQL Management Studio.

3. Connect to Analysis Server that is hosting the Service Reporting Cubes.

4. Expand Databases > UsageAnalysisDB > Data Sources > SRDWRepositoryDB.

5. Right Click and select Properties.

6. Hover over the Connection String to get the location of the Service Reporting Database and the name of the database.

  

7. Start SQL Server Configuration Manager.

8. Check the Log On As user for Analysis Services, by default is should be running under “NT Service\MSSQLServerOLAPService

  

Note: If the SQL Analysis Services and the SQL Instance are not installed on the same SQL Server we need to change the service to use a domain account, if Analysis Services and SQL Instance is on the same server you can grant the NT Service\MSSQLServerOLAPService access to the UsageDatawarehouseDB Database. In this example I’m granting NT Service\MSSQLServerOLAPService access to the database. To change the SQL Analysis Account do the following:

a. Select SQL Server Analysis Service, right click and select Properties.

b. Select “This Account” and browse for a Domain user.

c. Specify Password.

d. Select OK and Click Yes to restart the service.

The SQL Analysis Service should now run under a new service account.

9. Start SQL Management Studio as SQL Administrator.

10. Connect to the SQL Instance holding the UsageDatawarehouseDB.

11. Expand Security right click on Logins and select New User.

12. In login name specify the user that the Analysis service is running under.

13. Click User Mapping and Select UsageDataWarehouseDB.

14. Select db_datareader and click Ok.

15. Using SQL Management Studio connect to Analysis Services holding the Service Reporting Cubes.

16. Expand Databases > UsageAnalysisDB, right click and select Process.

17. Click OK.

18. Verify that the Process Succeeded.

  

19. Close SQL Management Console.

 

Databases or Cubes are not showing when connecting PerformancePoint to Service Reporting DW

Error: Databases or Cubes are not showing when connecting PerformancePoint to Service Reporting DW using SharePoint 2013 and SQL 2012.

Cause: PerformancePoint cannot connect to the Analysis Services due to missing ADO Client on SharePoint Server or Cubes has not been processed

Solution: Install ADO Client on SharePoint Server and verify that Cubes has been processed at least once.

I know these two issues might not be directly related to Service Reporting, but they took me quite some time to fix, as I’m not a SharePoint or SQL expert.

The first issue dealt with SharePoint running PerformancePoint. I could not get PerformancePoint to connect to Service Reporting Cubes.

By default SharePoint does not install the DB Connection agent (which it did in 2010), so this agent (SQL 2012) will have to be installed manually. To do this do the following.

  • Install DBO Agent.
  • Update SharePoint.
  • Generate SecureStore for PerformancePoint.
  • Verify Cubes have been processed.
  • Start PerformancePoint Wizard and verify connection.

Install DBO Agent

1. Logon to the server running SharePoint 2013 with SecureStore and PerformancePoint roles enabled.

2. Download SQL 2012 SP1 ADOMD.NET from here and install the client on the server.

3. Navigate to \Program Files\Microsoft Office Servers\15.0\WebServices\PpsMonitoringServer.

4. Open the web.config file.

5. Locate the following section.

<runtime>

    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">

      <dependentAssembly>

        <assemblyIdentity name="Microsoft.AnalysisServices.AdomdClient" publicKeyToken="89845dcd8080cc91" culture="neutral" />

        <bindingRedirect oldVersion="9.0.0.0" newVersion="10.0.0.0" />

      </dependentAssembly>

    </assemblyBinding>

</runtime>

6. Change the values in the

   <bindingRedirect> element from oldVersion="9.0.0.0" to oldVersion=10.0.0.0" and from newVersion="10.0.0.0" to newVersion="11.0.0.0"

7. Save the file.

 

Generate SecureStore for PerformancePoint

1. Logon to the SharePoint Server.

2. Start SharePoint Central Administration.

3. Under Application Management > Service applications select Manage Service Applications.

4. Select SecureStore Service.

5. In the ribbon select Generate a new key.

6. Type a password and confirm it.

7. Click OK.

8. Click Application Management > Service Applications select Manage Service Applications.

9. Select PerformancePoint Service Application.

10. Select PerformancePoint Service Application Settings.

11. Type <User Name> for username and Password: <Password>.

12. Click OK.

     Add the <User Name> to SQL Analysis services where Service Reporting Cubes are hosted.

13. Open SQL Management Studio.

14. Connect to Analysis Service Instance hosting Service Reporting.

15. Right click on Analysis Server and select Properties.

16. Select Security and add the user specified for the Secure Store.

17. Using SQL Management Studio, connect to the SQL Instance holding the Service Reporting databases

18. Add the user to UsageAnalysisDB with db_datareader permission

19. Click OK.

Verify Cubes have been processed

1. In Analysis Services expand Databases.

2. Right Click on the UsageAnalysisDB and select Process.

3. Click OK to Process.

4. Verify that the processing goes well.

If the process fails please see Processing Cubes are failing above for a possible solution.

Start PerformancePoint Wizard and verify connection

1. Open a browser

2. Go to the PerformancePoint page

3. Click Performance Point Content

4. Click Add new Item

5. Select Run to DashboardDesigner

6. Select Data Connections

7. Right Click and New Data Source.

8. Select Analysis Services.

9. Provide the server that is hosting the Service Reporting Cubes.

10. Verify that you see the Databases by using the “Drop Down” for “Database”.

  

11. Select UsageAnalysisDB.

12. Select Cube and select SRUsageCube from the “Drop Down”.

  

13. Click Test Data Source and verify that connection is successful.

14. Save the Data Connection

15. Create your Dashboard.

Please let me know if you have run into other challenges and I’ll be happy to add it to the list of things that can cause challenges with WAP & Usage.

Happy Usage and Metering with Windows Azure Pack.

Next blog post will be:

Troubleshooting Windows Azure Pack & Gallery Items