Troubleshooting the Data Warehouse: ETL failures due to login issues

This is the latest in “Troubleshooting the Data Warehouse” series. The goal of this series is to not only help you troubleshoot specific issues you may be experiencing but also teach you more about the data warehouse. For a complete list of posts in the series, check out Troubleshooting the Data Warehouse: An overview.

 

The symptom

Some or all ETL jobs have failed. The Operations Manager event log in the Data Warehouse Management Server indicates the ETL job failure is related to a login user failure.

 

The troubleshooting steps

To troubleshoot this issue, investigate the following things in order.

1. Check if the password for each run as accounts has changed or expired.

If it is, you need to update it. To do this:

· Click on Data Warehouse > Security > Run As Account, click on the related run as account, then click the Properties from the Tasks pane, update the Password field in the window and then click OK

· If this run as account is Operational System Account, you also need to update the services that are running under this account.

o In Data Warehouse Management Server, click Start > Run .., type Services.msc

o In the Services window, update the passwords for the services that run under this account, for example, System Center Data Access Service and System Center management Configuration

o Restart the services

 

Note that MP Sync job and Extract job for Service Manager can use a different run as account other than Operational System Account. This run as account is created while Service Manager is registered to Data Warehouse. See the following Data Warehouse Registration Wizard, the New … button

 

It is easy to update the password if it is expired. However it is complicated to update the system if you change the run as account. We don’t recommend that you change run as accounts.

 

2. If the job failure is not related to password

You should make sure the run as account for the failed job can be used to connect to the target database. For example, ensure the Extract job run as account can be used to connect to the Service Manager database. If not, make sure the SQL service hosting the database is running.