Ok,

We have finally got OWC working over the internet. I believe that the time
out issue described in SQL server magazine is due to the fact that the
analysis server is located in the private network and the client on the
internet cannot see it.

The trick we found to make OWC working over the internet is that the
Analysis sever has to be accessible directly from the internet through Port
80 (HTTP). The internet user use the IIS user to access the cube. If we
allow anonymous access, this would mean anyone can access the cube using
excel, as long as he knows the IP address and the IIS user is granted access
to the cube, and I did experiment and proved this point. A potential
solution is not to allow anonymous access, but this creates a lot of
management problems.

My current understanding is that once the client use the Web server to
locate the URL, it will bypass the web server and talk to the analysis
server directly. Therefore, the AS has to be accessible by the client, i.e.
it must be on the internet with Port 80 open. This is not desired for
obvious reasons.

Also, I just tried to not allow anonymous access in IIS. I can make it work
through Excel across the internet by suppling a domain account, but not the
OWC hosted on the web site. Can anyone confirm with me if the above
understanding is right and do you have any solution on the security side of
AS on the internet in general.

You might want to look at:
http://msdn.microsoft.com/library/en...l_datapump.asp
Hope this helps.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Eugene" <eugene.chen (AT) iwl (DOT) com.au> wrote

Dave
We are trying to use anonymous access. The application takes care of the
security. Our problem is that the client has to talk to the AS directly. In
your article, you mentioned the function of the Msmdpump.dll. This pump
object is supposed to do the talking between the Web Server and the AS.
What we have found is that the web page will work on the LAN without the dll
file. That is the client is talking to the AS, not the Web Server. If we
write a asp page with MDX, or use the Web Thin Client, then the web server
will talk to the AS. In a nutshell, I don't see the function of the
msmspump.dll file. Can you describle more on how to make this dll to work?
I guess the security requirement is to make the AS invisible on the
internet, and we have not been able to to this.
Thanks

From the name you can it's function -- "pump". The asp page creates a COM
object that takes the data from the http MIME buffers (from the http
connection) and translates that to a normal client-server connection. And
then it takes the resultset coming back from Analysis Services and wraps it
in the http MIME buffers and pass it back through IIS.. So you get something
like this as a logical diagram:

client (Internet) <-- http connection, port 80 --> IIS msolap.asp <--
normal client-server (through shared memory) --> Analysis Services

The msmdpump.dll is the underlying COM object that msolap.asp uses to
actually move the data.This allows you to run what would normally be a
client-server connection *transparently* out on the Internet.

My guess is that your security issues revolve around how you've configured
the IIS virtual directory. I would look at the configurations steps outlined
in the pointers that I sent.

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Eugene" <eugene.chen (AT) iwl (DOT) com.au> wrote

Dave,

We have been trying to figure out how PTS (Pivot Table Services) works
accross the Internet. This is where these questions are coming from. We have
read quite a bit of information on how OLAP and connecting to a Cube accross
the Internet should work but have been unable to find any specific
information how this is actually done.
My question is as follows; A client machine on the Internet uses Pivot Table
services and OWC needs to connect to an MS OLAP cube behind a firewall. From
all the documentation we have read and diagrams we have seen, this is
suppose to be possible. This is what i would like to have!

client--> internet --> f/w --> web server--> analysis server

From what i can see is that this will work, sort of. Once the client
connects to the web server and a htm or asp file is called, the client then
wants to talk directly to the analysis server (bypassing the web server),
what is the point of the web server? Very insecure in my view. The
documentation stipulates that you have the configuration as above, but you
need to have IIS installed on the analysis server as well. WHY? For the
reasons that i mentioned maybe, so that the client can communicate directly
with the AS. The clinet should never be able to communicate with the AS
server accross the Internet, the web server is the one that should be
communicating with AS and the clinet communicates with the web server.
Rather simple and logical connectivity for Internet usage.

Maybe we have missed something while researching this issue, can you please
clarify. Or if anyone has done this the way i have drawn above can you
please respond to this group.

Thanks

David

"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote

To all the group:

One big question. Do you need Enterprise Server for this to work? or can
you run it on a Standar Server.?


"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote

Xavier
Enterprise is needed for HTTP, otherwise you get "server error".
"Xavier Mernio" <xmerino (AT) grupomas (DOT) com> wrote

First, PTS is actually a OLEDB provider and it is located at: C:\Program
Files\Common Files\System\OLE DB\msolap80.dll

If you run the dependency walker utility against it, you will notice that it
is linked against WININET.dll and WSOCK.dll -- WININET.dll is used for http
requests; WSOCK32 (which is Winsock dll) is used for normal client-server
connections. If you do this against the SQL 7 version of PTS (which is also
a OLEDB provider, but called msolap.dll), you will notice that it is only
linked against WSOCK32; not WININET.

So, http access is new for SQL 2000 and PTS for SQL 2000 really has *two*
different access methods inside it.

If the servername starts with "http://", then PTS knows that it needs to
wrap all of its traffic in a http MIME encoding and do a http POST to the
specified URL. In some of the older SQL 2000 presentations this was called
the ICUBE (for Internet Cube). Normally this post is done to port 80 like
any other normal web browser access, but you can specify a different port on
the url if you want (and you have configured IIS to listen on that port for
the web site, e.g.

servername = http://www.mycompany.com:8080/msolapvd

will invoke the msolap.asp page under the 'msolapvd' virtual directory. As I
said, PTS uses WININET to make the actual http connection, so WININET is the
actual component that parses the URL and notices that you want port 8080 --
PTS doesn't really know about http port numbers. PTS takes the servername
you have specified and adds the string "msolap.asp" to the URL you've given,
so the actual web page that will be given for the connection is:

http://www.mycompany.com:8080/msolapvd/msolap.asp

That is why, on the server side, you have to create a virtual directory (in
this case called msolapvd, but you can name it anything you want so long as
it included on the servername URL), and then copy msolap.asp into that
virtual directory. The msmdpump.dll that msolap.asp invokes gets the http
MIME encoded buffer from the POST requests, does some buffer translations
and then passes it to the msmdsrv service, just as if the data had came in
on a normal client-server connection (without all of this http stuff in the
middle). With SQL 2000 SP2 and earlier, this "passing" between the data pump
and msmdsrv had to be through shared memory, this is why IIS and Analysis
Services has to be on the same machine. With SP3, there is an optional
technique where the data can be "passed" using a named pipe. Look at:
http://msdn.microsoft.com/library/en...l_datapump.asp for
specifics on this additional technique.

Most of the security setups that have to be done for http access involve
configuring IIS authentication so that the NT account that is used is valid
and acceptable to the Analysis Services msmdsrv service. Again, look in the
white paper above for more specifics. All of the options and restrictions
are layed out in the white paper.

But, anyway, let's go back to PTS. If PTS notices that the servername does
*not* start with "http://", which is the normal case, PTS assumes that you
are asking for a client-server connection to TCP port 2725 and connects to
the servername itself using normal Winsock. This is the way that PTS has
always done its connections. SQL 7 version of PTS used ports 2393 and 2394;
SQL 2000 version of PTS uses a single port which is 2725. You can see these
port numbers in action this if you go to http://www.sysinternals.com and
load the tcpview utility. You will see the msmdsrv process (which is the
actual Analysis Services NT service itself) listening on ports 2393, 2394
and 2725 (so it can handle PTS connections from SQL 7 or SQL 2000 clients).

In both cases -- either http access or normal client-server connections --
the same binary data flows back and forth on the wire, the only question is
whether it is wrapped in additional messages (which is what http access
does).

Hope this helps.

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"david" <davidpe (AT) dsfsd (DOT) com> wrote