November 26, 2015 at 3:44 am
I'm trying to connect to an Analysis Services 2014 cube using C# .NET 4.6 console application. Here is the sample code I'm using:
using Microsoft.AnalysisServices.AdomdClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
class Program
{
static void Main(string[] args)
{
var connectionString = "Data Source=<SSAS machine IP number>;Catalog=<SSAS database name>;Password=<password>";
try
{
var connection = new AdomdConnection(connectionString);
connection.Open();
Console.WriteLine("OK");
Console.ReadLine();
}
catch (Exception ex)
{
Console.WriteLine(ex);
Console.ReadLine();
}
}
}
On the SSAS machine (Win Server 2012 R2 Standard x64) there is a SQL Server 2014 Standard SP1 installed. As a "side note", the SQL Server version is 12.0.4213.0, which I cannot find in the SQL 2014 build list (http://www.sqlservercentral.com/articles/SQL+Server+2014/101274/).
The Analysis Services service is up and running with log on the NT Service\MSSQLServerOLAPService account. Port 2383 is open in the Windows Firewall. There is only one default instance of SQL / SSAS install i.e. I don't need to start the SQL Browser.
The user specified in the connection string is member of the Administrators group on the SSAS server and member of a role having "full control" on the SSAS database.
None of the machines involved is member of any domain.
When trying to connect to this SSAS instance I obtain different results, apparently depending on the client's OS and on the version of Microsoft.AnalysisServices.AdomdClient.dll I'm referencing in the project, as follows:
1. Client Windows 8.1 Pro and Microsoft.AnalysisServices.AdomdClient.dll version 12.0.2000.8 (corresponding to SQL 2014). The connection fails with this error message: "System.Xml.XMLException: Element 'return' was not found line 5 position 2". In the C:\Program Files\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\Log\FlightRecorderCurrent.trc there are an "audit login" and an "audit logout" recorded for the user name I provide in the connection string.
2. Client Windows 8.1 Pro and Microsoft.AnalysisServices.AdomdClient.dll version 10.50.1600.1 (corresponding to SQL 2008 R2) - connection succeeds
3. Client Windows 10 "november update" - connection fails no matter what version of Microsoft.AnalysisServices.AdomdClient.dll I'm using.
The connection seems to "break" shortly after being established. The error message is: "A connection cannot be made. Ensure that the server is running. Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. System.Exception {System.IO.IOException}...System.Net.Sockets.SocketException An existing connection was forcibly closed by the remote host..."
In the C:\Program Files\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\Log\FlightRecorderCurrent.trc file there is an "audit login" event recorded for NT AUTHORITY\Anonymous logon (even if I provide the user name and password in the connection string) with apparently no corresponding logout event.
I've tried to connect to the cube using Excel 2013 with the same user and the connection establishes succesfully.
Please help me understand why this connection succeeds under certain circumstances only and fails in other environments - for example is the connection string wrong, or should I configure SSAS to be accessible via HTTP? Please let me know your clues. I've talked to my customers about using Reporting Services to query the SSAS cube, but, for now, .NET code is their preferred way.
Thank you for your advice 🙂
November 28, 2015 at 7:56 am
I think your problem here is the .NET process identity, and that's why you're getting the "anonymous" login attempt details.
SSAS only works with Windows Authentication, so I'd check the application pool of your .NET application and ensure that it is impersonating a Windows account with permissions to the SSAS instance.
December 30, 2015 at 7:40 am
Thank you so much for your advice.
Since the machines involved are not part of any domain I've configured the Analysis Services to be accessed via HTTP, following the steps described here:
https://technet.microsoft.com/en-us/library/gg492140%28v=sql.120%29.aspx
I'm sorry for this very delayed answer.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply