PowerPivot Refresh and Timer job failures
Recently I was working with a customer in a hardened environment to get PowerPivot working in a test environment and having a few issues.
Background:
This is a lab and does not have office web apps. We started with multiple accounts for everything but have reverted to basically the farm account until we get this fully functional. We are not using an alias for anything. We are SQL2012 SP3 CU10 on the analysis server and SharePoint.
Farm Account is running the following services and is local admin on the 1 box in the farm. Is it also the unattended account for all the Secure Store Target Applications and added ad an admin on Analysis Server.
Microsoft SharePoint Foundation Sandboxed Code Service
Distributed Cache
User Profile Synchronization Service
Claims to Windows Token Service
Security Token Service Application
Application Discovery and Load Balancer Service Application
PowerPivot Service Application
Excel Services Service Application
Reporting Services Service Application
PowerPivot gallery manual and scheduled refresh will not work. I can load and interact with the spreadsheet but if I attempt to refresh I get the error: “We were unable to refresh one or more data connections in this workbook. The following connections failed to refresh:”
In the ULS logs we see:
ASPPHOST::ShowEngineError: Out of line object 'DataSource', referring to ID(s) <GUID>, has been specified but has not been used. OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001. OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.
Failed to create an external connection or execute a query. Provider message: Out of line object 'DataSource', referring to ID(s) <GUID>, has been specified but has not been used. OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001. OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001. , ConnectionName: ThisWorkbookDataModel, Workbook: TestBook.xlsx.
ASPPERRORINFO dump:[ Errorcode:aspperrConnectionFailed EXCEPTION MESSAGE: Out of line object 'DataSource', referring to ID(s) <GUID>, has been specified but has not been used. OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001. OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001. ]
PivotException::GetExceptionFromHr: hr -2146827284.
UserWorkbook.HandleExternalDataRefreshFailure: Failed to execute query, sessionId=1.V23.200Zsx2g5gOpLX6jgeyakpB14.5.en-US5.en-US36.4054e912-99c3-47c3-9abc-4a72c61bb0fa1.A1.N, exception=Microsoft.Office.Excel.Server.CalculationServer.Interop.PivotException: Exception of type 'Microsoft.Office.Excel.Server.CalculationServer.Interop.PivotException' was thrown., externalsource=SqlServer BI_TEST, extendedConnectionInfo=Microsoft.Office.Excel.Server.CalculationServer.ExtendedConnectionInfo
On the analysis server I see:
Message: OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001. (Source: \\?\D:\SQLData\MSAS11.POWERPIVOT\OLAP\Log\msmdsrv.log, Type: 3, Category: 289, Event ID: 0xC1210003)
The “PowerPivot Management Dashboard Processing Timer Job” is failing with the error
When Clicking on the “Failed” link in Central Admin for Job History I see:
Errors in the high-level relational engine. The following exception occurred while the managed IDbConnection interface was being used: Could not load file or assembly 'System.Transactions.dll' or one of its dependencies. Access is denied.
In the Application Event Log for SharePoint I see:
Event ID 6398 The Execute method of job definition Microsoft.AnalysisServices.SPAddin.UsageProcessingTimerJob (ID >GUID>) threw an exception. More information is included below. Errors in the high-level relational engine. The following exception occurred while the managed IDbConnection interface was being used: Could not load file or assembly 'System.Transactions.dll' or one of its dependencies. Access is denied.
Troubleshooting
I’ve verified that the USERS group has read/execute on 'System.Transactions.dll' located in c:\Windows\Microsoft.NET\assembly\GAC_64\System.Transactions\v4.0_4.0.0.0__b77a5c561934e089 for both SSAS and SharePoint
I verified that USERS group and everyone is in the security right to Bypass Traverse checking for SharePoint as I have had issues in the past with .NET 4 DLLs. https://blogs.technet.microsoft.com/leesab/2014/11/03/how-to-determine-what-user-rights-assignments-are-required-by-sharepoint-2010/
I ran a procmon filtering initially on OWSTIMER but do not see any access denied errors or even an attempt to access to 'System.Transactions.dll'
I created an UDL file to test connectivity
I ran wireshark looking for blocked ports
I read numerous blogs and posts but everything in my environment was exactly as the blogs listed.
Finally I ran across this article https://blogs.msdn.microsoft.com/as_emea/2015/12/09/ssas-2012-in-a-hardened-windows-2012-environment/ which indicates that the security right to Bypass Traverse must be allowed when doing impersonation within SSAS. We added ‘Authenticated Users’ on the SSAS server and now all is working.