PowerPivot scheduled data refresh should use an Oracle account that does not have password expiration


I have a customer who encountered a server hang issue on the PowerPivot SSAS instance. No PowerPivot reports can run on the SharePoint site. The customer cannot even connect to the PowerPivot SSAS instance using SSMS or any client tool. The connection just hangs.

 

This customer has PowerPivot for SharePoint to do scheduled data refresh for many Excel PowerPivot files. These Excel files make connections to many different types of data sources.

 

Some of the PowerPivot models connect to an Oracle server using a stored credential. When the scheduled data refresh updates the PowerPivot data model, it uses this credential as an Oracle user login account to connect to the Oracle database server

 

When the Oracle user account password expires, the Oracle OLE DB provide creates a user dialog telling the error message

 

"ORA-28001: the password has expired".

 

The Oracle provider expects some user click on the dialog box to dismiss the error message. However, since the SSAS PowerPivot service is a background service, no one is able to see this dialog box. The processing command thread hangs and waits for user input. The processing thread is unable to return the Oracle provider error to the processing command to generate a failure.

   

After some time, the PowerPivot service in SharePoint tries to delete the processing database. SharePoint sends out a dozen of Delete commands, and they are blocked by the hang processing command. When new connections are made to the PowerPivot SSAS instance, the new connection threads are blocked by the delete command threads. This means the whole PowerPivot SSAS instance hangs, and no user is able to browse data in the PowerPivot gallery in SharePoint, and no scheduled data refresh can execute.

   

The best practice is to use an Oracle account that does not have password expiration set.

   


Comments (0)

Skip to main content