kart


Hi,

I'm facing a strange issue in SQL 2000 AS connectivity. I have an .dll file that talks to SQL AS 2000 and gets the Dimension Hierarchy - so display as a list in a webpart page.

I use the below code to connect to SQL AS 2000

AdomdConnection connection = new AdomdConnection(conString)

connection.Open();

where my connection string is

Provider=MSOLAP.2;Integrated Security=SSPI;Persist Security Info=True;Data Source=myserver;Initial Catalog=MyDataMart;Client Cache Size=25;Auto Synch Period=10000;MDX Compatibility=1.

It works well when I open the the page in Server, whereas, it doesn't when I open the user desktop browser. I end up with error

"A connection cannot be made. Ensure that server is running". The stack trace is

at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenTcpConnection(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla()
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()

at MyClass.myFunction()

Not sure why its, but I'm in a crunch situation. So any help is highly appreciated.

Regards,

Kart





Re: Connection issue to SQL 2000 AS using ADOMD - Urgent

Bruno Guardia (MSFT)


Since the connection string works in the server, we can assume it is correct, and go diagnosing connectivity issues.

a) From one of the clients that cannot connect, ping "myserver", verify it is available and that the IP is correctly resolved

b) Verify both your client and server has TCP/IP configured

c) It is not clear if your connection is using HTTP or not; in any case, verify the port your Analysis Services is listening on, and make sure your firewall is not blocking the port for your client to reach it

Tell us what is the result of each step.






Re: Connection issue to SQL 2000 AS using ADOMD - Urgent

kart

Hi,

For a. It is connecting as I'm using the same ADOMD.NET in exce 2007 to connect to AS 2000

For b. It should be there because, I'm able to execute a normal windows application from the desktop PC directly to AS 2000

I couldn't get c - but I'm not sure whether you got my case. Please find the details below.

It is a web-part in sharepoint that talks to SQL AS 2000 to display hierarchy in tree format. WHen I go the the server, open and IE browser and navigate to the page, it displays the hierarchy. But, when I open an IE browser in a user PC (or) a developer PC and open the webpart page, it is say the aforementioned error. Hope this clarifies.

Regards,

Kart







Re: Connection issue to SQL 2000 AS using ADOMD - Urgent

Chu Xu

Is port 80 enabled

For more common connectivity issues, you can check this article on Microsoft TechNet (it talks about configuring HTTP for SQL 2005 but I think it also applies to SQL 2000)

Chu






Re: Connection issue to SQL 2000 AS using ADOMD - Urgent

Nicolae Mogoreanu

OK, here are my thoughts:

ASP.NET engine impersonates the user that connects to it, because "Integrated Windows Authentication" is enabled on the website. This is why when you connect to the website from the server (supposedly you are logged in with Administrator privileges) everything is OK, but when a normal user (not admin) connects he fails.

There are some possible ways to solve this problem:
1. Disable Integrated Windows Authentication on the website and grant the NETWORK_SERVICE of whatever role your asp.net engine runs on the SERVER privileges on Analysis Services (there is another post here on how to do that). This can break the entire authentication mechanism on you website this is why I give 80% that this solution will not work for you. Also it is not so security friendly.

2. Before making the call to Analysis Services force your thread to impersonate a user that has SERVER privileges on the Analsysis Services machine. This can be a special user created specially for this purpose. This way is more secure.

3. ... there are many other but can't think something specific right now.

I would use the second approach for a quick solution.

If you are not sure how to accomplish the step search live.com Smile (Microsoft rules) for the exact steps (like Thread Impersonate).

Best Regards,
Nicolae