The Problem:

Trying to run an MDX Drillthrough query on Analysis Services (sitting on a
separate machine to the web page but on the same machine as SQL Server) via
an ASP .net page to process a cube on SQL Server Standard Edition.

Flow:

- ASP .Net Server: Passes a drill down query to the SQL Server (also
hosting Analysis Server (AS) ). This is done using:

o ADODB connection.

o ADODBMD cell set

- SQL & AS Server: AS receives the Drill Down query and runs it on
SQL Server. The query results are passed back to the ASP page.

- ASP .Net Server: Takes drill through query results and displays
them in a DataGrid using the Databind() method.



Scenario 1:

We connect with the following code:

Dim conn As New ADODB.Connection

conn.Open("Provider=MSOLAP.2;Data Source=http://SqlServer01;Client Cache
Size=25;Auto Synch Period=10000;User id=userA;password=passMe;")



We get the following error:

"The HTTP or HTTPS connection feature is available only with Analysis
Services for SQL Server 2000 Enterprise Edition. The server you are
attempting to connect to is not an Enterprise Edition server."



The answer to this problem we know as SQL Standard Edition does not support
access via http from Analysis Services. It works fine if we connect to a
machine that has SQL Server Enterprise Edition running on it.



Scenario 2:

We remove the "http://" from the "Data Source" in the connection string:

Dim conn As New ADODB.Connection

conn.Open("Provider=MSOLAP.2;Data Source= SqlServer01;Client Cache
Size=25;Auto Synch Period=10000;User id=userA;password=passMe;")



We get the following error:

"Unspecified error".



Question:

We'd like to use Analysis Services Drill Through functionality on a machine
that runs SQL Server Standard Edition. Since the Standard Edition does not
support access to Analysis Services via http we have to come up with another
solution. Do you have any suggestions?

Can we connect directly to Analysis Services from the ASP page without using
http?

If so can we get a simple working example.

Assuming that the ASP page server is on the same intranet as the
Analysis Server, then access should be possible by configuring cube
access for an "Anonymous" account (which was "NT Authority\Anonymous
Logon" in our case). Kerberos could be another option, but I haven't
used it yet. If you inspect the Windows Event Log of the Analysis
Server, there should be an access error when you try to connect - with
the error message listing the Anonymous account name.


The Analysis Services Operations Guide addresses this:

http://www.microsoft.com/technet/pro...ntain/anservog.
mspx#EQAA
End-User Security

End-user security in Analysis Services is based on Windows user accounts
and groups. Before you begin configuring end-user security in Analysis
Services, you must first create the user accounts and groups within
Active Directory. A frequently asked question is whether Analysis
Services supports other kinds of authentication. The answer is Yes and
No. Yes, it can support other types using HTTP access and IIS (IIS 6.0
includes some new authentication options). However, all these
authentication types must ultimately map to a Windows user account in
the general sense: including domain accounts, local accounts, the guest
account (if enabled), or the built-in NT AUTHORITY\ANONYMOUS LOGON
account. Therefore, no, Analysis Services does not support SQL standard
security or any similar technology where the authentication is not based
on Windows user accounts.

For authentication, Analysis Services uses Security Support Provider
Interface (SSPI) as the interface for application security. When you
issue a query to Analysis Services, in the connection string (see
"Authentication of Direct Connections" in SQL Server Books Online), you
specify one of the following SSPI options:

• SSPI=NTLM specifies that the normal Windows authentication protocol
be used, and enables Analysis Services to interoperate with Windows NT
4.0. Use this provider only when a client computer is connecting
directly to an Analysis server.

• SSPI=KERBEROS specifies that the Kerberos network authentication
protocol be used. Kerberos enables interoperability with other security
architectures. More importantly to Analysis Services, it supports a more
flexible authentication infrastructure. Kerberos is based on "tickets,"
which greatly reduces the need for repeated authentication on each
network resource. The principal advantage of Kerberos for Analysis
Services is that its ticket-based approach supports multi-hop
architectures: an end user's credentials being passed from the client
machine to a Web server, then forwarded to the Analysis server (a
three-machine configuration). For more information on Kerberos, see the
resources listed in Appendix B, "Resources."

• SSPI=NEGOTIATE specifies that the client and Analysis Services
dynamically evaluate which is the best authentication SSPI to use.
Currently NEGOTIATE supports only NTLM and Kerberos; more SSPIs may be
added in the future. This technique allows you to design the most
flexible application. NEGOTIATE requires all computer operating systems
to be Windows 2000 or later.

Other SSPI providers are technically possible, but not tested or
supported by Microsoft. However the infrastructure is in-place and
exposed for integration if required.

• SSPI=ANONYMOUS - This option specifies that PivotTable Service (PTS)
handle requests in a special manner. When you specify ANONYMOUS, PTS
does not send authentication credentials to the Analysis server. Instead
it tells the server to use Anonymous access, without actually saying
what that means. On the server, the OLAP service uses the built-in NT
AUTHORITY\ANONYMOUS LOGON account. This technique is useful when you
need to support a three-machine configuration-client, Web server
(typically using HTTP access) and Analysis server-but don't need or want
the infrastructure that Kerberos requires. In this configuration, rather
than controlling access on the Analysis server (since all users are
logged on using the Anonymous account), use the authentication setup on
the Web server's virtual directory.

When you use Anonymous authentication on a Windows XP or Windows 2003
computer, the built-in account is not included in the Everyone group. As
a result, you must specify the Anonymous Logon account explicitly when
configuring the access in Analysis Manager. For more information, go to
Knowledge Base and see the article "INF: Connect to Analysis Services By
Using "SSPI = Anonymous" on Windows XP."
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!