| Troubleshooting advice for Sage 200 Business Intelligence |
Description | We would recommend this article is followed for all Sage 200 Business Intelligence queries where there are issues when attempting to get Business Intelligence set up or if the cubes wont refresh, including the following errors: - 'OLE DB Error or ODBC error Cannot Open Database Login failed 42000' errors.
- 'error copy data warehouse'.
The information below applies to versions 2011 and above. If you receive an error 'Could not load file or assembly microsoft.analysisservices, version 11.0.0.0.' read more. |
Resolution | Please refer to the guidance below and where necessary, attempt to start Business Intelligence again.- Microsoft SQL Server and Analysis Services must be installed on the same machine with the same instance name. These should also be installed at the same time.
- For Microsoft SQL Management Studio 2008 and below, you must ensure that the same user exists both for SQL Server and Analysis Services within the SQL Server Configuration Manager application.
- If using SQL Server 2012 and above, please ensure the correct settings have been created for the Service Accounts as per the Deployment and Installation Guide. The changes made to the Deployment Guide regarding use of SQL Server 2012 and above were made to reflect the recommendations made by Microsoft about the installation and deployment of SQL Server. These new guidelines are designed to provide enhanced security, something often desired by Business Partners, as well as dealing with added security within SQL Server (especially on more complicated system setups).
- The setup detailed within the Deployment and Installation Guide is the only setup we test against. Business Partners are free to install and set up SQL Server as they wish but we do not recommend less secure setups than that recommended by Microsoft. Alternative system setups are to be used for testing/troubleshooting only. Live deployments must be set up as per the Deployment and Installation Guide.
- To establish if the issue is caused by the Sage 200 Professional application, can you create a non-Sage database manually within Analysis Services? If you're unable to do so, this suggests an issue with Analysis Services that must be resolved before Sage 200 Business Intelligence can also successfully create a new database or perform requests against an existing database.
- The Sage 200 Deployment and Installation Guide for the version of the software being installed must be referred to. The Guide has important references in the Business Intelligence section in relation to security, set up and firewall exclusions. Certain ports must also be available for use, an overview of which is provided below.
Analysis Services and Firewall - If Analysis Services has been installed as a named instance on the server, then Analysis Service will be assigned a dynamic port. In such a scenario, you must ensure that SQL Server Browser Service is running and the UDP Port 1434 which is used by SQL Server Browser Service is open within the Windows firewall. You should also ensure TCP Port 2382 has been opened in the Windows firewall so that client PCs can connect to the SQL Server Browser to be redirected to the named instance of SQL Server Analysis Service. Otherwise the dynamic port which is used by the Analysis Service Named Instance should be open in Windows firewall.
- If the Business Intelligence data has been transferred from another server (both the Data Warehouses and Analysis Services cubes) then check the following setting to ensure that it is referring to the new server:
- Within Analysis Services, choose the database > Data Sources> right-click and select Properties > Select Connection String and edit if necessary.
[BCB:19:UK - Sales message :ECB]
|
|