Newbie: Use XMLA to connect to SQL Server

Hi,

I am developing a Java application which is able to browse "XMLA-Type" databases. For testing, we have a SQL Server 2000 and ~2005 installed on a server an I can access them through a web service.

When I query the datasources via XMLA, I get a response like:

DataSourceInfo == Local Analysis Server
ProviderName == Microsoft XML for Analysis
ProviderType == TDP
ProviderType == MDP
ProviderType == DMP
AuthenticationMode == Unauthenticated

(in XML of course, but for simplicity, I leave the XML out...).
Now, this is all well and good, but I do have two -- probably very foolish -- questions:

1. How can I set the SQL Server 2000 to actually use an "authenticated" authentication mode, and
2. (more importantly) How do I actually provide the username/password combination in my XMLA query? Do I set properties? Or restrictions?

For reference: My current XMLA query to discover the datasources is:

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
xmlnsOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<Discover xmlns="urnchemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<RequestType>DISCOVER_DATASOURCES</RequestType>
<Restrictions>
<RestrictionList>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<Format>Tabular</Format>
<Content>SchemaData</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

So, suppose I'll get an "AuthenticationMode==Authenticated" in the response, how would I rephrase my discoverAnything queries? (Which currently look like this -- for catalogs):

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
xmlnsOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<Discover xmlns="urnchemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<RequestType>DBSCHEMA_CATALOGS</RequestType>
<Restrictions>
<RestrictionList>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<DataSourceInfo>Local Analysis Server</DataSourceInfo>
<Format>Tabular</Format>
<Content>SchemaData</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Thanks for any help!
Regards,
Philipp
Is my question just hilarilously stupid because I did not understand a concept? In that case, if you could provide me with pointers to where I can find information on authentication with XMLA (I tried google of course, but I did not find much), I'd be thankful.

Regards,
Philipp
|||

Hi Phillip, your question is not stupid, your problem is that there are not many people that actually connect using raw XMLA. There are a lot of moving parts that you need to get right.

Chris Harrington has the best collection of blog posts I have seen on the subject here

http://www.activeinterface.com/thinolap.html

Particularly this one: http://www.activeinterface.com/b2005_11_21.html

Most of his samples are in javascript, but you should be able to figure it out. I know that I was able to setup XMLA and get Chris's samples working on my old laptop, but I do not have it configured on my current one.

How were you planning to authenticate? Were you going to use windows authentication, pass a username/password or are you happy with anonymous authentication?

|||Hi Darren,

thanks for your response -- now I know that at least I did not completely make a fool out of myself. I will check out the blog immediately and try to get it to work.

As for the authentication method: The thing is that at our company, we have set up a web-service with a MS Analysis Server running on it. _I_ can connect to it anonymously (or so it seems: I can just send a "discover catalogs" xmla call and get all that I wanted).
I am of course not at all happy with that. That's why I posted my first question in the post: How do I tell MS Server to use user/password combination (simple authentication)?

So, I'd be most happy if I could support all authentication modes, but for starters, passing a username/password would be just fine.
Again: Thanks for the links. They should get me started,
|||

The key to this in Chris's samples appears to be the following line (which I have simplified a bit) which opens the connection.

oHttp.open("POST", sUrl, false, sDomain + "\\" + sUser, sPass)

So the username/password is specified when opening the connection, before sending any commands. If you have not sent through a username/password, it will be falling back to either anonymous or integrated authentication.|||Thanks again, Darren. Indeed, I found the same line and implemented the same solution in java, that is, I have the code

Authenticator.setDefault(new Authenticator(){
protected PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(username,
password.toCharArray());
}
});
urlConnection = (HttpURLConnection) url.openConnection();
urlConnection.setRequestMethod("POST");

and this does the trick. Or rather: This _seems to_ do the trick: I am still struggling to get my Microsoft Server to use authentication... It seems as if anyone who has access rights to the computer on which the service runs, can also access the database. But, I am pretty sure, I can get that to work (there's a manual to go through).

Anyway, thanks for your help!
Philipp