Connecting to SSAS on workgroup W2K3 server using SQL Server Management Studio...how?

Hi All,

I'm trying to connect to SSAS on a workgroup (i.e. non-AD) Windows 2003SP1 server from my XPSP2 PC, using SQL Server Management Studio and/or Visual Studio, with no luck.

I have set up SSAS to accept anonymous connections, firewall is off, no anti-virus software, etc. I have added 'Everyone', 'NT Authority\ANONYMOUS LOGON' to the SSAS 'server' role (I don't care about security at this stage).

I can connect to SSAS from my PC via code, though I have found out by trial-and-error that I need to specify a connection string that includes a User ID and Password; e.g.:

"Data Source=sandbox; Provider=msolap; Initial Catalog=Analysis Services Tutorial;User ID=some_user;password=password;"

When I try to connect to the server using Management Studio, there is no option to change the authentication user name or password - it is always set to the local pc\local user combination - so the attempt fails with the 'An existing connection was forcibly closed by the remote host (System)' error message.

A profiler trace review shows that the PC is connecting to the server, but it is failing authentication - most likely because it is trying local pc\local user credentials.

Unfortunately my organisation doesn't use AD - we use Novell NDS (aaarghh!) - hence the need to persist with standalone servers. I need to be able to maintain/deploy to standalone servers.

Does anyone have any ideas on how I can use Management Studio on a PC to connect to SSAS on a workgroup/standalone server?

Thanks in advance,

Tim Pedersen.

First I am sure that SSAS only accepts windows authentication.

Next, a simple way, but not secure, is to add your windows account to the administrators account on the stand alone server.

Last. You will have to run the tool, called surface area detection(or something near) configuration tool, in order for SSAS to accept external connections.

Kind regards

Thomas Ivarsson

|||

Thanks,

I've given up trying to connect via Management Studio. I just have to wait until our organisation rolls out AD fully sometime late this year. In the meantime I just have to use the backup-restore database, etc. options, and use remote desktop...

|||

Another thing to try is: Try setting RequireClientAuthentication sever property to 0.

This allows any user to connect to your server.

Here is a whitepaper about server configuration properties: http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/SSASProperties.doc

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi,

Thanks for the link to the white paper, the information in it is quite useful.

However, I have managed to solve the problem via another route. It looks as though the RequireClientAuthentication property is ignored in a workgroup network situation.

I did a network trace on the TCP conversation between the PC and the server and found that there was a SSPI handshake taking place which looked like it was failing...so this indicated that maybe I needed to ensure that there was some form of shared credentials, even though RequireClientAuthentication is set to false. So I created a user on the server with the same user id as on the PC, with identical password...and hey presto I can connect!

I am doing some more testing - it looks as though the server user still needs to be added to SSAS security for everything to work correctly.

I think that in the absence of AD/Kerebos SSAS falls back to NTLM/workgroup security with matching user name/passwords on all the computers, regardless of the setting of RequireClientAuthentication. Perhaps RequireClientAuthentication only has meaning in an AD-based network...?

Cheers,

Tim Pedersen