Issues with using msolap.x provider name in linked server definition

During a recent investigation I realised that a number of people are using MSOLAP.x as the provider name for a linked server e.g. MSOLAP.4 when creating a linked server... e.g.

EXEC master.dbo.sp_addlinkedserver @server = N'lsname', @srvproduct=N'msolap', @provider=N'MSOLAP.4', @datasrc=N'servera\inst', @catalog=N'SSASDBName'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'lsname',@useself=N'False',@locallogin=NULL,@rmtuser=N'dom\user',@rmtpassword='########'

etc

If you do this and you then right click the Linked Server that is shown in the SSMS linked servers list and select properties then you will note that SSMS displays the linked server as if it is a SQL provider linked server. If you just script it then it will appear to be using the options that you chose originally.

If you were to use processmonitor to check what it happening then you will also note that as well as checks on msolap from sqlserver.exe there will be activity done by dllhost for msolap.4. This is because the provider has defaulted to out of process because there is no provider called MSOLAP.4 in the SQL Server list of providers. From a sql server point of view the provider is called MSOLAP.

Change the linked server to use MSOLAP. So long as your MSOLAP driver installation is correct on the SQL Server machine then it will pick up the latest MSOLAP driver. For SQL Server Analysis Server 2008 R2 this will be MSOLAP.4.

It will then correctly use the provider in InProcess mode so long as AllowInProcess is set for the MSOLAP provider.

e.g.

EXEC master.dbo.sp_addlinkedserver @server = N'lsname', @srvproduct=N'MSOLAP', @provider=N'MSOLAP', @datasrc=N'servera\inst', @catalog=N'SSASDBName'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'lsname',@useself=N'False',@locallogin=NULL,@rmtuser=N'dom\user',@rmtpassword='########'

To test the interface use something like:

select * from openquery (lsname,'select [Measures].[testm] on 0 from [TEST]')

Here [TEST] is a cube name in the database [SSASDBName].

Generally the MSOLAP.4 options will work for any user in Local Administrators group on the SQL Server but users that are not in this group will probably get an error for example:

Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "MSOLAP.4" for linked server "lsname".

An error may also be reported in the SQL Error Log and sometimes the Application Event log on the server

The OLE DB initialization service failed to load. Reinstall Microsoft Data Access Components. If the problem persists, contact product support for the OLEDB provider.

Error: 17054, Severity: 16. State: 1.