CODEWITHSUNDEEP
oledbssasadomd.net
Alex Duggleby
Alex Duggleby

Reputation: 8038

Differences between ADOMD.net and Analysis Services OLE DB Provider (especially regarding authentication)

I'm attempting to connect to a SQL Server Analysis Services cube on a remote server which is not connected to the domain I'm connected to.

I can connect to the cube with Excel after providing username and password. Now I'm trying to connect to the same cube via code.

Unsuccessful connection via AdomdConnection

Using ADOMD.net (AdomdConnection) for SQL Server 2008 R2 I try to connect with this connection string:

Data Source=SRV1;Initial Catalog=Name of Cube;User Id=RemoteAccount;Password=***

This connection fails on the Open method of the connection. Interestingly the "user id" and "password" properties are allowed, because if the syntax was wrong you get a KeyNotFoundException.

Successful connection via OleDbConnection

I can successfully connect if I use OleDbConnection instead and the following connection string (copied from the Excel connection):

Provider=MSOLAP.4;Persist Security Info=True;User ID=SRV1;Password=***;Initial Catalog=Name of Cube;Data Source=SRV1;

So my questions are:

All downloads are available here.

Upvotes: 1

Views: 7303

Answers (2)

ISHIDA
ISHIDA

Reputation: 4868

If you cannot connect the cube with excel, then you cannot connect it with the code.

The difference between ADOMD.Net and OleDb connection is that, the OleDb MSOLAP provider can be used to send MDX, DAX and DMX queries to an Analysis Services database. The result can only be a scalar or a table, so you cannot use an MDX query returning results on more than two axes. When you use a DAX query, the result will always fit well in a table, you can use a DataReader class to get the result.

We prefer mainly ADOMD.NET on OleDb due to the presenceConnection bug which hasn't been resolved.

In order to connect to SSAS instance with ADOMD.NET we need to add the following line in web.config file ( After creation of console application in Visual studio 2012)

Since we will be using Windows authentication to connect to Microsoft Analysis Services 2005, this addition of line in the web.config file is required to impersonate a user having access to the Analysis Services.

We need to configure the IIS server by creating a new application pool.

Following MSDN link will give other parameters of the ADOMD.NET connection String.

https://msdn.microsoft.com/en-us/library/microsoft.analysisservices.adomdclient.adomdconnection.connectionstring

Upvotes: -1

lvmeijer
lvmeijer

Reputation: 1032

I'm using the AdomdConnection succesfully with the username / password keys, but the difference is that I'm connecting to a HTTP data source. The solution I'm using is that I tunnel OLAP traffic over HTTP via IIS 7 as described in MSDN. The user name and password are then impersonated by IIS to Windows credentials.

Note: Disable Anonymous and Windows Authentication in IIS. I only got it working with Basic Authentication. For enhanced security, use SSL.

Upvotes: 0

Related Questions