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.

1. 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.
2. 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.
   
   • Within SQL Server Configuration Manager, check SQL Server and SQL Server Analysis Services and ensure that they are set to use the LocalSystem user account. Using another account may cause a problem as they may not have full access to the required areas. You can check this setting as follows:
     
     

     Note: All users must be logged out of the entire Sage 200 Professional application if amendments need to be made after checking the settings below.

     1. Start > Programs > Microsoft SQL Server > Configuration Tools > SQL Server Configuration Manager.
     2. Select SQL Server Services in the left-hand panel. This will list the services on the right-hand-side. 
     3. Ensure the 'Log On As' column has the identity set to 'LocalSystem'. If it isn't, make a note of the current username then right-click on the entry and choose Properties. Set the option to 'Built-in account' and choose Local System from the list.
     4. Repeat this for SQL Server Analysis Services if necessary.
        
        

        Note: If SQL Management Studio is installed on a different system to your Application Server, then the LocalSystem user will not work. You will need to use an alternative account that has been set as a 'Full Access' account.

   • Within SQL Management Studio, check that the NT AUTHORITY\SYSTEM user does not just have 'public' access in Server Roles. This user will need a SysAdmin Role also. This can be checked by right-clicking on the username, selecting Properties and then Server Roles.

1. 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).
2. 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.
3. 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.
4. 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

   • Please refer to the following link which may help you with configuring the firewall: http://www.mssqltips.com/tip.asp?tip=1987
     
     

     Note: This is a link to an external site - Sage (UK) Ltd are not responsible for any undesired results or outcome as a result of using the advice. Please refer to your IT Administrator if further assistance is required.

   • The following files should be set to be excluded from monitoring by any firewall in place:
     
     • SQLServr.exe - found at C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\SQLServr.exe
     • msmdsrv.exe - found at C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\bin\msmdsrv.exe
     • Ports 1433 and 1434 should also be available for use and not used by any other application.
5. 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.
6. 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]