Mono Develop Microsoft Authentication for Microsoft Analysis server

Question

For my research I need to be able to query a Microsoft analysis server(2012) Data cube with the Unity game engine. For the connection there is a Microsoft Authentication needed and Unity is using the Mono Develop libraries for SQL connections which gives me a problem. Since so far i haven't found a solution for Mono to be able to use a Microsoft Authentication.

I want to find a nice way to use windows authentication inside a domain at the customer. Since the end application must be able to connect to Data cubes preferably using a connection string.

Data cubes using an IIS server that allows for HTTP connection using SOAP will not always be available depending on the costumer set-up. Also the desire to build the application on multiple platforms makes it hard to add custom library's if there even is a custom library for this request since I haven't been able to find one yet.

My current less fortunate side options are:

• Build an extra windows application with visual studio that query's the data and parses it to Unity (But requires and extra application to run).
• Use the http SOAP connection with an IIS service (But requires the analysis server to be set-up with IIS which isn't always possible depending on the customer).
• Find a library that allows me to use Microsoft Authentication (but probably only works on the windows platform, or doesn't work with Unity).

I'm hoping someone has already found or maybe knows a good solution that works for Unity without giving the limitations I've mentioned above.

---

I am using the Microsoft.AnalysisServices.AdomdClient; with visual studio that works fine for visual studio build but doesn't work in monodevelop in unity.

try
        {
            using (AdomdConnection adcon = new AdomdConnection(connectionString))
            {
                adcon.Open();
                using (AdomdCommand adcmd = adcon.CreateCommand())
                {
                    adcmd.CommandText = textBox3.Text.ToString();
                    AdomdDataReader dr = adcmd.ExecuteReader(CommandBehavior.CloseConnection);

                    while (dr.Read())
                    {
                        for (int i = 0; i < dr.FieldCount; i++)
                            textBox2.AppendText(dr[i] + (i == dr.FieldCount - 1 ? "" : ", ") + Environment.NewLine);
                    }
                    dr.Close();
                    textBox2.AppendText(adcmd.CommandText.ToString() + Environment.NewLine + Environment.NewLine);

                    adcmd.Connection.Close();
                }

                adcon.Close();
            }
        }
        catch(Exception e)
        {
            textBox2.AppendText(e.ToString() + Environment.NewLine);
        }

---

Thank you for any suggestions, feedback or answers!

Answer 1

Something you could try would be using the relational SQL Server engine as a kind of proxy. The relational SQL Server engine should be available whereever Analysis Services is available.

You could set up a linked server in the relational SQL Server which links to the Analysis Services server using the Analysis Services OLEDB client. How to set up linked servers is described here.

Then you can send MDX to this linked server, and get back the results in the relational engine like this:

select * from openquery(AdventureWorksOLAP, 
         'select [Measures].[Sales Amount] on columns from [Adventure Works]')

where AdventureWorksOLAP is the name of your linked server.

However, you should be aware that the column names are strange (containing closing square brackets, which need to be escaped within the square brackets enclosing column names by doubling). E. g. the above query would have a column that you have to access as [[Measures]].[Sales Amount]]]. Furthermore, all cell values come back as nvarchar and might need to be casted to a numeric type in order to work with them.

Maybe you could encapsulate the column name changing and column typecasting into a stored procedure.

And you could use SQL server authentication to access the relational database.