OWC 11 security problem connecting to AS

[James]

We want to give access to Analysis Services through OWC to worldwide users.
Each user should only see data from his country, Windows authentication is
not an option.

We are setting up an IIS virtual directory for each country, called Dir_USA,
Dir_UK, etc...
Logon is anonymous, forms authentication, and in IIS we set-up the "Windows
account used for anonymous access" to USA_User, UK_User, etc...

Then in Analysis Services, we set up dimension security to restrict access
for UK_User to the members that are located in the UK.

Finally, we created an aspx page containing OWC, and the following
connection string:

PivotTable1.ConnectionString = "Provider=MSOLAP.2;" + _
"Data Source=http://10.10.10.10/Dir_UK;Initial Catalog=ASDB"
PivotTable1.DataMember = "TheCube"

Everything works fine for well-behaved UK users. They see only the UK data
in their OWC pivot table.
If they try to navigate to the Dir_USA aspx page, they are redirected to a
login screen and are unable to sign-on.

However, we have noticed that UK users just need to replace Dir_UK in the
connection string of the aspx page with Dir_USA and they are able to see the
US data, even as they are logged-on to the UK site.
I have verified the value of
System.Security.Principal.WindowsIdentity.GetCurrent().Name, and it does
contain MyServer\UK_User, as they are able to get US data.

How is it possible to connect to Analysis Services through another virtual
directory?
How can MyServer\UK_User bypass AS security and get US data?

How can I plug this hole?

Using OWC version 11.0.0.5531, Windows 2000 Server SP4, SQL Server 2000 SP3

 

[Wei-Dong XU [MSFT]]

Hi,

From my research, the OWC control is client control. When they send the
request to the server, it builds the connection with the server themselves;
which means the security credential they are using is decided by the
connection string not the login page. Even you use the form authentication,
the control still manages its connection with the AS individually. This is
because the OWC is the client ActiveX control which sends the request to
the AS server himself, not at the run time of aspx page.

For example, we can find the datasource is from the Dir_UK in your
connection string.
PivotTable1.ConnectionString = "Provider=MSOLAP.2;" + _
"Data Source=http://10.10.10.10/Dir_UK;Initial Catalog=ASDB"

And you have set "Windows account used for anonymous access" for UK_user,
US_user, so when the client OWC requests the data from
http://10.10.10.10/Dir_UK, the request will be impersonated to the UK_user
by IIS and then access the data from AS.

So for your question:
"How is it possible to connect to Analysis Services through another virtual
directory? How can MyServer\UK_User bypass AS security and get US data?"

Suggestion1:
You can only provide one link to the page located in the Dir_US for the
users. Since the new page will contain the connection string Data
Source=http://10.10.10.10/Dir_Usa. so they will then see the US data.

Suggestion2:
use one javascript code to change the "Data Source" property value of the
pivottable in the client side, This way, there is no need for IE to
download the page from Dir_usa.

Please feel free to let me know if you have any question.

Best Regards,
Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[James]

You write that the connection is then made directly from the OWC to AS,
without going through the virtual directory, where msolap.asp is located. In
effect, you are saying that OWC systematically bypasses all security and
exposes all data in the cube, whatever role security is implemented in the
cube. Do I understand you right? Why bother implementing basic or digest or
windows authentication then, since OWC is going to bypass it anyway?

I'm sorry the 2 questions in my post were confusing. I'm not trying to get
UK users to see US data.
I'm asking how come the UK_User gets to see the US data, although AS role
security is set up in a way that UK_User is blocked from seeing US data. I
have verified that role-based security does work when the users sign-in
locally on the server.
However, when accessing the data from a remote location using OWC, they just
need to change the URL in the OWC connection string to completely bypass AS
security, and see all the data. How come is it possible to bypass AS
role-based security so easily??

 

[Wei-Dong XU [MSFT]]

Hi,

Excuse me! I haven't express that concept very concisely for you.

OWC will not bypass the IIS security checking when we specify the
http://<ip>/<virutal direcotyr> link to the OWC.

"I'm asking how come the UK_User gets to see the US data, although AS role
security is set up in a way that UK_User is blocked from seeing US data."
For this question, we are currently researching this for you. Greatly
appreciate your patience on this issue. We will update when we have any
idea then.

Best Regards,
Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[James]

I beg to disagree, that's exactly what it's doing. If you reread my original
post, you'll notice that UK users are not authorized to see US data in my
set-up, but they can achieve to see the data by simply changing the URL in
the connection string.

Both IIS and Analysis Services security are completely bypassed.

Unless I'm doing something wrong, and I was hoping that somebody on this
newsgroup would hint at possible causes of the problem.

 

[James]

Let me correct my latest post. AS security isn't bypassed. Only IIS security
is bypassed.
It may be because msolap.asp is an ASP page, and the rest of the virtual
directory is ASP.NET. Can anybody confirm that Forms Authentication would be
bypassed in such a setup?

 

[Wei-Dong XU [MSFT]]

Hi,

I am testing this issue for you. Firstly, I will need to build one repro
environment. I list it below and please feel free to correct me if any of
them is not the same to yours.

one test boxes: A windows 2003 with IIS 6.0 worker process mode & Analysis
service

1. create two local user accounts (in the users group by default): uk_user,
us_user
enable asp and asp.net in the IIS6.0

2. create one web site in box A

3. configure the web site only using Basic authentication.

4. create two virtual directory
uk
only set the anonymous access for this virtual directory and grant the
account to the anonymous access: uk_user
us
only set the anonymous access for this virtual directory and grant the
account to the anonymous access: us_user

5. place the msolap.asp file in the two virtual directory respectively.

6. create one logon page as below:
//--------------
<%@ Import Namespace="System.Web.Security " %>
<HTML>
<script language="C#" runat="server">

void Login_Click(Object sender, EventArgs E) {

//the logon user crednetial is very simple for the test
// username: us / us
// password:1

if ((UserEmail.Value == "uk") && (UserPass.Value == "1")) {
FormsAuthentication.RedirectFromLoginPage("uk_user", false);
return;
}

if((UserEmail.Value == "us") && (UserPass.Value == "1")) {
FormsAuthentication.RedirectFromLoginPage("us_user", false);
return;
}

//logon failure
Msg.Text = "Please try again";

}

</script>
<body>
<form runat="server" ID="Form1">
<h3>login page</h3>
<table>
<tr>
<td>useremail£º</td>
<td><input id="UserEmail" type="text" runat="server"
NAME="UserEmail"></td>
<td><ASP:RequiredFieldValidator ControlToValidate="UserEmail"
Display="Static" ErrorMessage="*" runat="server"
ID="Requiredfieldvalidator1"
NAME="Requiredfieldvalidator1" /></td>
</tr>
<tr>
<td>password£º</td>
<td><input id="UserPass" type="password" runat="server"
NAME="UserPass"></td>
<td><ASP:RequiredFieldValidator ControlToValidate="UserPass"
Display="Static" ErrorMessage="*" runat="server"
ID="Requiredfieldvalidator2"
NAME="Requiredfieldvalidator2" /></td>
</tr>
</table>
<asp:button text="Logon" OnClick="Login_Click" runat="server"
ID="Button1" NAME="Button1" />
<p>
<asp:Label id="Msg" ForeColor="red" Font-Name="Verdana" Font-Size="10"
runat="server" />
</form>
</P>
</body>
</HTML>

//--------------

7.create one Default.aspx page as below:
//--------------
<%@ Page language="c#" Codebehind="default.aspx.cs" AutoEventWireup="false"
Inherits="webapp1.WebForm1" %>
<html><head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>Default</title>
</head>
<body><p><object classid="clsid:0002E55A-0000-0000-C000-000000000046"
id="PivotTable1" VIEWASTEXT>
<param name="XMLData" value="&lt;xml
xmlns:x=&quot;urn:schemas-microsoft-comffice:excel&quot;&gt;
&lt;xivotTable&gt;
&lt;x:OWCVersion&gt;11.0.0.6255 &lt;/x:OWCVersion&gt;
&lt;xisplayScreenTips/&gt;
&lt;x:CubeProvider&gt;msolap.2&lt;/x:CubeProvider&gt;
&lt;x:CacheDetails/&gt;
&lt;x:ConnectionString&gt;Provider=MSOLAP.2;Integrated
Security=SSPI;Persist Security Info=True;Data
Source=http://<serverIP>/uk;Initial Catalog=FoodMart 2000;Client Cache
Size=25;Auto Synch Period=10000&lt;/x:ConnectionString&gt;
&lt;xataMember&gt;Sales&lt;/xataMember&gt;
&lt;xivotView&gt;
&lt;x:IsNotFiltered/&gt;
&lt;/xivotView&gt;
&lt;/xivotTable&gt;
&lt;/xml&gt;">
<table width='100%' cellpadding='0' cellspacing='0' border='0'
height='8'><tr><td bgColor='#336699' height='25' width='10%'>&nbsp;</td><td
bgColor='#666666'width='85%'><font face='Tahoma' color='white'
size='4'><b>&nbsp; Missing: Microsoft Office Web
Components</b></font></td></tr><tr><td bgColor='#cccccc'
width='15'>&nbsp;</td><td bgColor='#cccccc' width='500px'><br> <font
face='Tahoma' size='2'>This page requires the Microsoft Office Web
Components.<p align='center'> <a
href='//sha-dds-01/Products/Applications/User/Office_System/Office_2003/file
s/owc11/setup.exe'>Click here to install Microsoft Office Web
Components.</a>.</p></font><p><font face='Tahoma' size='2'>This page also
requires Microsoft Internet Explorer 5.01 or higher.</p><p
align='center'><a href='http://www.microsoft.com/windows/ie/default.htm'>
Click here to install the latest Internet
Explorer</a>.</font><br>&nbsp;</td></tr></table></object>
</p>
</body></html>
//--------------

8. From the default.aspx page, you can see, I specify the
"http://<serverIP>/uk" as the data source of the pivottable; the Sales is
set to the DataMember property.

9. Then in the FoodMart 2000 of Analysis service, I create one security
role for the Sales cube and only permit this role. Add "uk_user" account
into the role, not add "us_user".

My test:
A: In the client, access the web application. In the login page, input
"uk_user" and password "1" to logon which was successful.
Then open a new IE window and input "us_user" and password "1" to logon
which was also successful.
B: Then change the data source of the pivottable control in the
default.aspx from
"http://<serverIP>/uk"
to
"http://<serverIP>/us"
no matter which account I input in the logon.aspx page, the pivottable
always report failure to me.

Conclusion:
This means the Form authentication doesn't touch the AS logon and security
checking.

When we successfully pass the form authentication, the pivottable still
needs to access the Virtual Directory and then access AS.
Pivottable -------------------------------> IIS
-------------------------------> Analysis Service
us vd or uk vd as
the data source

At this time, when the request from pivottable goes to the virtual
directory, since we have configured the Directory security of Virtual
directory to Anonymous (us virtual directory uses the us_user account; uk
virtual directory uses the uk_user account ), the request from Pivottable
will use the corresponding account to access the analysis service and
obtain the data.

This is to say, when we uses http://<serverIP>/UK as the data source, the
pivottable request will use the account "uk_user" to access the AS. And if
we uses http://<serverIP>/US as the data source, account "us_user" will be
used.

So from your reply:
"you'll notice that UK users are not authorized to see US data in my
set-up, but they can achieve to see the data by simply changing the URL in
the connection string."

When they change the URL in the connection string, they will use the
corresponding account to read the data from AS. This is the reason.

For your convenience, I have attached the related web application for you
in the attachment of this post. You can configure the OE to receive the
attachment if OE blocks this attachment.

Please feel free to let me know if you have any further question.

Best Regards,
Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[James]

Not sure if it makes a difference, but I'm using Windows 2000 Server SP4, and
IIS 5.
The web site has Anonymous and Integrated Windows, the virtual dirs
Anonymous only.
In the OWC connection string, I don't have the "Integrated Security = SSPI".

Then, my test was slightly different from yours, but I came to the same
conclusion, namely the msolap.asp completely ignores the Forms
Authentication, and lets everybody through.

I conclude that it is impossible to use Forms Authentication with the OWC
components and Analysis Services.
Is that an accurate statement?

I am now looking at using basic authentication with SSL. Problem is, Forms
Authentication was going to allow me to use a single sign-on for our various
ASP.NET applications. This won't be possible with Basic Auth.

 

[Wei-Dong XU [MSFT]]

Hi ,

"I conclude that it is impossible to use Forms Authentication with the OWC
components and Analysis Services."
You are correct. And I have also tested not using the "Integrated Security
= SSPI" gives the same result.

So for your scenario, I have one suggestion for you:
Set the virtual directory Dir_UK, Dir_US etc to Basic authentication or
digest or Windows Integreated Authentication.
Then specify the user name and password in the connection string so that
Pivottable control can communicate with the AS without popping up one logon
window.
For example:
"user id=<user account>;password=<password>"

This way, the pivottable control will authenticate to the AS automatically
when they builds the communication with AS. However, you have to face two
security threats here.
1. internet package captured by 3rd party
some package capturer can watch the internet communication and obtain the
user name and password. You can use SSL for this issue.

2. If the end user knows how to check the html source and obtain the
username and password. there is no need for him to pass the form
authentication. He could design one html page containing the pivottable
control and then uses the user credential to obtain the data. For this
issue, you can only use Windows Integreated Authentication for your whole
site so that there is no need to specify the user cerdentail in the page.

In addition, SSO is used for the server application obtain the data from
other site so that there is no need for you to input the logon information.
The server application finished them for you. Please note this happens in
server side. For the client side, when you visit some virtual directory or
site which needs the authentication, you will still need to input the logon
credential. I don't think SSO can help a lot for you in this scenario.

The best solution I can find now is to set the authentication mode for your
Site into Integrated Windows Authentication.

Please feel free to let me know if you have any question.

Best Regards,
Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[James]

OK, I'm going to use Basic Authentication with SSL, without including the
UserID / PWD in the HTML page. Users will get the pop-up, they'll have to
live with that. (not sure what you mean by SSO)

 

[Wei-Dong XU [MSFT]]

Hi,

"Basic Authentication with SSL"
I think this is one great choice for your scenario.

SSO is not suitable for your scenario. In my last post, I try to explain
why SSO can't help you on this issue.

If you have any question regarding this issue, please feel free to let me
know.

Best Regards,
Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.