none
OleDB OLAP provider, http and basic authentication RRS feed

  • Question

  • I am using OLAP OleDB provider (or ADOMD.NET - doesn't make difference) to connect to SSAS through HTTP. IIS is configured to accept on Basic Authentication.

    The thing works, but there is a problem. Every request issued by OleDB provider first tries anonymous authentication and only when it is rejected it goes with basic authentication. So it works, but every request is composed of at least two roundtrips.

    Any idea how to persuade OleDB client to use basic authentication from start?


    Miha Markic [MVP C#] http://blog.rthand.com
    Tuesday, March 29, 2011 4:45 PM

All replies

  • Hi Miha,

    Have you tried to disable the Anoymous authentication? Additionally, this mostly sounds to be IIS authentication question. It is better to ask it in window server forums.

    thanks,
    Jerry

    Wednesday, March 30, 2011 6:19 AM
    Moderator
  • Hi Jerry,

    This is the exact situation - anonymous authentication on IIS is disabled. Yet, the client still tries every time with anonymous first, and then basic. I don't think this is the problem on the server side but rather with OLAP client.


    Miha Markic [MVP C#] http://blog.rthand.com
    Wednesday, March 30, 2011 1:27 PM
  • You mention "OLAP Client" but don't mention which client you are using. Is this Excel or your own custom client?

    Also, have you seen the following similar issue: http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/79d2f225-df35-46da-aa22-d06e98f7d658

    Not exactly the same issue but some of the info in there might provide you with some help.


    http://bi-logger.blogspot.com/
    Wednesday, March 30, 2011 2:03 PM
  • I mean my custom .net client which in turn uses a 3rd party control which uses OLAP OleDB Provider (ADOMD.NET provider performs the same)

    That thread deals with similar problems but doesn't help. My is only a performance problem.


    Miha Markic [MVP C#] http://blog.rthand.com
    Wednesday, March 30, 2011 2:08 PM
  • Lol, I wasn't expecting it to be a silver bullet but more a start at things to check and compare to your setup. (eg. If using HTTPS are you following the information in the link, info on username/password combinations etc.)

    Anyway, so what happens if you use Excel? Do you also see a first attempted connection as anonymous followed by basic?


    http://bi-logger.blogspot.com/
    Wednesday, March 30, 2011 3:20 PM
  • :-)

    Good idea to try Excel. Unfortunatelly and unsurprisingly the result is the same.


    Miha Markic [MVP C#] http://blog.rthand.com
    Wednesday, March 30, 2011 3:53 PM
  • It goes like this for every request (courtesy of MS Network Monitor):

     

    362 17:50:33 30.3.2011 39.9662873 EXCEL.EXE [IP1] [IP2] HTTP HTTP:Request, POST /olap/msmdpump.dll  {HTTP:105, TCP:104, IPv4:65}
    365 17:50:33 30.3.2011 39.9753524 EXCEL.EXE [IP2] [IP1] HTTP HTTP:Response, HTTP/1.1, Status: Unauthorized, URL: /olap/msmdpump.dll , Using Basic realm="someaddress" Authentication {HTTP:105, TCP:104, IPv4:65}
    368 17:50:33 30.3.2011 39.9761544 EXCEL.EXE [IP1] [IP2] HTTP HTTP:Request, POST /olap/msmdpump.dll , Using Basic Authorization {HTTP:105, TCP:104, IPv4:65}
    418 17:50:33 30.3.2011 40.1194915 EXCEL.EXE [IP2] [IP1] HTTP HTTP:Response, HTTP/1.1, Status: Ok, URL: /olap/msmdpump.dll  {HTTP:105, TCP:104, IPv4:65}
    

     


    Miha Markic [MVP C#] http://blog.rthand.com
    Wednesday, March 30, 2011 3:56 PM
  • Let me state upfront that I don't know the answer. But here are some questions and suggestions to try.

    What connection string are you using in your .NET app? Does the connection string include: UserID=yourdomain\youruser;Password=yourpassword;Integrated Security=Basic? (If not, try that, as those properties are for HTTP(S) from what I understand). Or are you doing any impersonation in your code so that the connection occurs using the credentials of the .NET app?

    What version of Microsoft.AnalysisServices.AdomdClient are you using (if you go to c:\windows\assembly and look at the properties of that assembly and go to the version tab.)


    http://artisconsulting.com/Blogs/GregGalloway
    Wednesday, March 30, 2011 6:19 PM
    Moderator
  • Hi there,

    Sure, any suggestion is welcome.

    The connection string include both user id and password. If I add Integrated Security=Basic I get the following error: "The integrated security 'Basic' is not supported for HTTP or HTTPS connections.". Funny, because help states that Basic is the only valid value for a HTTP(S) connection.

    No impersonatio is used.

    AdomdClient is version 10.


    Miha Markic [MVP C#] http://blog.rthand.com
    Wednesday, March 30, 2011 6:36 PM
  • Clutching at straws a bit now I admit but assuming you have IIS7 maybe just double check that your configuration matches this: http://technet.microsoft.com/en-us/library/gg492140.aspx so that we can hopefully rule this out as being an issue.

    I can see lots of similar issues regarding this though they appear to be mainly related to AS2005.

    Also, could you direct me to the help location that mentions about using Basic, I have clearly lost my ability to search on Google 'cos I can't find it. ;-)


    http://bi-logger.blogspot.com/
    Thursday, March 31, 2011 9:09 AM
  • Hi Philip,

    I'll check, and sure, why not - it could be anything.

    As per Integrated Security setting this is the page I was referring to:

    http://technet.microsoft.com/en-us/library/microsoft.analysisservices.adomdclient.adomdconnection.connectionstring.aspx

    It says:

    "Sets the control access to use. If set to SSPI, an SSPI supported security package is used for user authentication. If set to Basic, the UserName and Password settings is required. An HTTP connection can use only the setting of Basic."


    Miha Markic [MVP C#] http://blog.rthand.com
    Thursday, March 31, 2011 9:14 AM
  • That's rather amusing. Yessss, I suspect that Integrated Security defaults to Basic unless you override to SSPI. As such you probably cannot specify Basic because it is the default so to use it, just do not specify SSPI. A bit poor really but hey...

    So, any news on the configurations with relation to the link I posted? All set as per the link and still attempting anonymous first or all sorted?


    http://bi-logger.blogspot.com/
    Friday, April 1, 2011 5:52 PM
  • Can you quantify the performance hit? Will you write a small test app that connects 100 times and runs an MDX query? Try it with a http connection and with a standard connection and time it.
    http://artisconsulting.com/Blogs/GregGalloway
    Thursday, April 7, 2011 12:41 PM
    Moderator
  • Hm, does it matter? I mean one certainly looses at least latency timespan for each request - which depends on the internet connection.
    Miha Markic [MVP C#] http://blog.rthand.com
    Thursday, April 7, 2011 12:43 PM
  • Hi,

    The issue should be related with windows environment.It is recommended that you contact Microsoft Customer Support Services (CSS) via telephone which will be a charged call for an extended support. 

    To obtain the phone numbers for specific technology request please take a look at the web site listed below.

    http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS

    If you are outside the US please see http://support.microsoft.com for regional support phone numbers.

    thanks,
    Jerry

    Sunday, April 10, 2011 3:15 AM
    Moderator
  • Somehow I am failing to see the relation to Windows environment as the provider should decide what authentication method to use - why would that depend on Windows environment?

    I am not saying that is doesn't but it'd be strange if it did.


    Miha Markic [MVP C#] http://blog.rthand.com
    Sunday, April 10, 2011 10:09 AM
  • Miha,

    Did you ever find out the solution--as I am having the same issue!  There is pretty obviously not a lot of documentation on it aside from these forums.  If you figured out how to solve it, I'm sure a LOT of people would be interested in nominating you as Hero of the Day.

    Thanks,

    Dave

    Thursday, January 10, 2013 8:06 PM
  • Unfortunately not :-(

    Miha Markic [MVP C#] http://blog.rthand.com

    Saturday, January 12, 2013 4:49 PM
  • Hi Miha,

    Which authentication you want to use ?   Per user identity or unattended ?

    I mean , What do you want to have in your result from SSAS , permissions based row level data or generic data?


    Neelam Gupta


    Saturday, January 12, 2013 5:28 PM
  • I used ADOMD.NET and got it working with the following settings.

    On IIS only turn on Windows authentication on your OLAP provider

    Within ADOMD.NET use a connection string like

    Data Source=http://Servername/OLAP/msmdpump.dll; Initial Catalog=SSASCube; User ID=UsernameWithoutDomain; Password=Password

    As you can see I skipped the Integrated security part in my connection string, if I add that section then I get all the errors mentioned below.

    Hope this helps

    Tuesday, December 17, 2013 2:21 PM