August 27, 2008 at 6:58 am
Setup:
Windows Server 2003
SQL Server 2005 SP2 using a named instance SQL2005
Excel 2007 client connecting from a non-domain computer using HTTP access.
Up to now we have had Anonymous Authentication enabled in IIS and when connecting to Analysis Services 2005 from Excel using a http:// /olap/msmdpump.dll address everything works fine - we can browse the cubes and retrieve data.
We are now trying to enable Windows Integrated authentication in IIS and turn off Anonymous Authentication. In the IIS Manager we go to the virtual directory, directory security and disable Anonymous Access and leave Windows Integrated checked.
We attempt to connect via Excel 2007, using the http address for the server name and the credentials of an administrator account. The account has full access and has no problem connecting via Management Studio and other tools.
The connection hangs for a bit in Excel and returns an authentication failure... "Unable to connect to data source. Reason: Access denied. You either made a mistake in typing in your User ID and/or Password, or you do not have permission to access the database server".
In the Server 2003 security event log there is a successful login event recorded. This would seem to imply that IIS has accepted the credentials.
In SQL Server Profiler there is an Audit Login event showing the correct credentials - again it would seem that IIS has authenticated and is now impersonating that user to Analysis Services. Immediately after this entry there is an error line with error number -1055981562. The description on that line reads
XML parsing failed at line 1, column 2: Incorrect document syntax.
.
Note the period in the second line, which should be the XML request.
Any suggestions?
Thanks in advance.
JW
September 2, 2008 at 9:50 am
Are the SSAS and IIS machines one and the same? ie does the IIS box need to impersonate across to another server?
Steve.
September 2, 2008 at 9:53 am
Yes, they are on the same server.
Thanks,
JW
September 3, 2008 at 10:41 am
Edit - 09/03/08 Focus on provider differences.
After some additional testing we're focusing on the differences between the OLE DB and ADOMD providers. We're connecting from a machine that is not on the same domain as the IIS/SSAS server. In this case when testing using Business Intelligence Development Studio (BIDS) using a reporting services project, a data connection defined using the ADOMD provider to the HTTP destination works fine, but one that uses the OLE DB 9.0 provider fails.
Our test case:
1. Create a new reporting services project.
2. Create a new Shared Data source
3. Chose the Microsoft SQL Server Analysis Services provider. This is the ADOMD provider.
4. Enter the http destination and the specify the machine account (username/password) to be used to make the connection.
5. Test the connection. A successful result is returned and the SQL Profiler shows the log in/new session and the NTUserName value is the username specified in step 4.
6. Create a second new Shared Data source
7. Choose OLE DB for the type and click Edit.
8. Chose Microsoft OLE DB Provider for Analysis Services 9.0 from the list of providers.
9. Enter the same username/password that was used in step 4.
10. Test the connection. An error is returned "No error message available, result code: DB_SEC_E_AUTH_FAILED(0x80040E4D).". The SQL Profiler shows the Audit Login event with a 0 error code (success) and the correct username in the NTUserName column. This is immediately followed by a line with the XML Parsing error noted above.
So does anyone know of any differences in how the two providers handle authentication and/or establishing the session? If I read the trace correctly the login is successful (error code 0) but the next step, establishing a session, is failing. The XML parsing error would seem to imply a mal-formed request of some sort.
September 3, 2008 at 11:05 am
hmm, interesting....
looking at the msdn entries for both (ADOMD and OLEDB), the ADOMD explicitly mentions that it utilises XMLA under the covers and also allows for http connectivity. The oledb driver doesn't mention this at all.
When XMLA first came out (as add on really) with AS2000, it was a wrapper over the oledb driver (ie it exposed the query and DDL functionality through a web service but converted everything to oledb calls on the server). not sure what happened exactly on the move to SSAS2k5 but it could be that the oledb driver is lower level still, and therefore doesn't support the http connectivity. It would appear that your error might support this - ie, the xml parser fails right ont he first or second char of the file /request passed to it, which might suggest it's not getting passed xml messages/envelopes.
can you force the non domained client to use the ADOMD connection rather than an oledb one?
Steve.
September 3, 2008 at 11:35 am
Thanks for the information. We're trying to use Excel 2007, so now we're looking into whether Excel 2007 can use the ADO MD client instead of OLE DB. It almost seems like the username/password values are not supported when using Windwos Integrated authentication from a non-domain client. Basic authentication works and uses the username/password. The error only pops up when IIS is set up to do WIndows Integrated.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply