ASP.NET Access to Remote Database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Analysis Services (2000)

ASP.NET Access to Remote Database

Author

Topic

canderson
Starting Member

14 Posts

Posted - 2004-11-18 : 17:48:01

I have two basic problems.

My client used to run their SQL Server databases and Analysis Services on one computer with IIS installed (the actual IIS server was a different machine). While this structure was in place, I could access OLAP cube data via ASP.NET pages on their website.

They reorganized and decided not to install IIS on the new machine (TERA) running SQL Server & Analysis Services due to a security issue created by IIS. I attempted to connect to the OLAP cube data via those same ASP.NET pages and received the following error:

"COMException (0x800a0e78): Operation is not allowed when the object is closed."

The last line of the stack trace before this error is:

"ADODB.ConnectionClass.Close() +0"

I read that IIS must be installed on the same machine as Analysis Services or this error will appear. The client refused to install IIS on that machine, but agreed to install it with Analysis Services & SQL Server on another machine (WSRVCORP). The Analysis Services database was placed on this machine and the connection changed to see the SQL database on TERA (connection tests successfully). But, when attempting to process a dimension, I received this error:

"Data source provider error: Login failed for user '[domain]\WSRVCORP$'.;42000;[Date/Time]"

Online documentation suggested a bug in Service Pack 3, but installing Service Pack 3a did not eliminate the problem. Other documentation suggested permissions issues, but the client has three layers of permissions and the Sys Admin does not want to try and troubleshoot this for fear of opening up security breaches. He said there are three different administrator logins this could be using.

I later tried to connect through the web site and still ran into "Operation is not allowed when the object is closed."

I am not a network person, so Internet research has been my main means of resolving network issues. I am open to any and all advice as to what else I should try to do. The client is eating up my time with delays.

Charles W. Anderson
MCSD.NET
Naia Corporation
Birmingham, AL

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-11-18 : 18:35:45

ASP.net, but you are getting a COM exception??
Are you doing InterOp to get to the A.S. Stuff?

B'ham eh? I'm 4 hours south of there, in Mobile!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

canderson
Starting Member

14 Posts

Posted - 2005-01-05 : 14:42:34

Apparently so. The error message included this line:

Exception Details: System.Runtime.InteropServices.COMException: Operation is not allowed when the object is closed.

Sorry for the delay. I thought this thing would email me when someone posted. Please email me at canderson@naiacorp.com so we can discuss this more efficiently. Thanks for the reply.

Charles W. Anderson
MCSD.NET
Naia Corporation
Birmingham, AL

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-01-05 : 14:48:44

If you "subscribe to topic" it will e-mail you when someone replies.

Can you tell me (or show some code) that is being called that raises this error? It might be something as simple as
SET NOCOUNT ON
in your stored proc that's being called. From what I'm reading here I'm thinking this is a connection thing, so if you could display your connection code and a connection string (replace username and password with a dummy string) that would be helpful.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

canderson
Starting Member

14 Posts

Posted - 2005-01-05 : 15:04:17

This is the connection code referenced by the code that breaks:

public cNSDataMD()
{
mdConn = new ADODB.ConnectionClass();
mdConn.Provider = "MSOLAP.2";

sOLAPDataSource = ConfigurationSettings.AppSettings["OLAPDataSource"];
sOLAPInitialCatalog = ConfigurationSettings.AppSettings["OLAPInitialCatalog"];

sOLAPConnStr = "Data Source=" + sOLAPDataSource + "; Initial Catalog=" + sOLAPInitialCatalog + "; SSPI=anonymous";
}

It breaks somewhere in here, most likely in the first three lines after the try:

public DataTable MakeDataTableCS(string sMDX, string sRowHead, int nMemberLevelCaption)
{
ADOMD.Cellset oCS = new ADOMD.CellsetClass();
DataTable oTab = new DataTable();
string sUserName = ConfigurationSettings.AppSettings["OLAPUserName"];
string sPassword = ConfigurationSettings.AppSettings["OLAPPassword"];

try
{
d.mdConn.Provider = "MSOLAP.2";
d.mdConn.Open(d.sOLAPConnStr, sUserName, sPassword, 0);
oCS.Open(sMDX, d.mdConn);

ADOMD.Position oPos;
int iRows = 0;
int iCols = 0;

DataRow oRow;
ADOMD.Cell cell;
//Retrieve number of rows and columns
iCols = oCS.Axes[0].Positions.Count;
iRows = oCS.Axes[1].Positions.Count;
//Make Columns
oTab.Columns.Add(sRowHead);
foreach (ADOMD.Position p in oCS.Axes[0].Positions)
{
oTab.Columns.Add(p.Members[nMemberLevelCaption].Caption);
}
//Insert rows
for (int i = 0; i < iRows; i++)
{
oRow = oTab.NewRow();
oRow[0] = oCS.Axes[1].Positions[i].Members[0].Caption;
for (int j = 0; j < iCols; j++)
{
object[] coords = new Object[2];
coords[0] = j;
coords[1] = i;
//Array args = (System.Array)coords;

//reference the current cell by ordinal array
cell = oCS.get_Item(ref coords);
oRow[j + 1] = cell.Value;

}
oTab.Rows.Add(oRow);
}
}

catch (Exception e)
{
cErrHandler eh = new cErrHandler(e, "cOLAP");
}
finally
{
//oCS.Close();
d.mdConn.Close();
}

return oTab;
}

Charles W. Anderson
MCSD.NET
Naia Corporation
Birmingham, AL

canderson
Starting Member

14 Posts

Posted - 2005-01-05 : 15:16:24

It looks like the ADODB.ConnectionClass() [variable name mdConn] is where the problem lies.

Charles W. Anderson
MCSD.NET
Naia Corporation
Birmingham, AL

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-01-05 : 15:16:43

Can you step through the code to see exactly what line? I suspect that it's the d.mdConn.Open(d.sOLAPConnStr, sUserName, sPassword, 0); line.

I think this is going to be one of those things that is a permissions thing and it's gonna be tough for me to walk you through troubleshooting it on that network.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

canderson
Starting Member

14 Posts

Posted - 2005-01-05 : 15:34:30

The network setup on the development machine (where everything is on the same machine) does not give me this problem. The code in production (where this problem lies) is mostly via DLLs so I cannot step through it. Based on the Stack Trace, I would agree with you that "d.mdConn.Open(d.sOLAPConnStr, sUserName, sPassword, 0);" is the issue.

Charles W. Anderson
MCSD.NET
Naia Corporation
Birmingham, AL

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-01-05 : 15:36:13

Ok, can you connect to the OLAP database using Query Analyzer and a SQL Server Authentication login? If so, then I think we can modify your connection string a bit and make this thing work.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-05 : 15:43:45

You could try the MSOLAP provider iof the MSOLAP.2 provider, to see if there is any difference.

Since you recieve an error when trying to process a dimension, check how the datasource for the OLAP DB is set up.
(When you test the connection from a client running AM(Analysis Manager) then it is the clients NT creds that are used for the test!,
so it can test fine on the client but fail when server tries to process dimension)

rockmoose

canderson
Starting Member

14 Posts

Posted - 2005-01-05 : 15:47:25

I'm not sure. I don't think I've done that. The OLAP database (JuneOLAP) to me is what I access via A. S., but it is based on a database (ATPort) in Enterprise Mgr. I apologize, but you'll have to explain this to me in more detail.

Charles W. Anderson
MCSD.NET
Naia Corporation
Birmingham, AL

canderson
Starting Member

14 Posts

Posted - 2005-01-05 : 15:54:17

rockmoose,

I'll try that, too. I'll have to get my client's network guy to check that setup. He told me that there were several layers of permissions and he was wary of tinkering with them. It looks like he'll have to do something to it, though.

Charles W. Anderson
MCSD.NET
Naia Corporation
Birmingham, AL

canderson
Starting Member

14 Posts

Posted - 2005-01-05 : 16:14:34

MichaelP,

I can connect to the Enterprise Mgr database ATPort via Query Analyzer, but I am not sure how to connect to the A. S. database JuneOLAP (where the connection string is pointed to) through Query Analyzer.

Charles W. Anderson
MCSD.NET
Naia Corporation
Birmingham, AL

canderson
Starting Member

14 Posts

Posted - 2005-01-05 : 17:30:18

MichaelP,

I think you meant to tell me to connect to the computer (TERA) where the OLAP database lives via a SQL Server authentication login. The password provided to me was not correct, so I was unable to achieve that. The network guy will try to figure that out again tomorrow when he is in town. Sorry for the misunderstanding.

Charles W. Anderson
MCSD.NET
Naia Corporation
Birmingham, AL

canderson
Starting Member

14 Posts

Posted - 2005-01-06 : 08:53:01

Neither Enterprise Mgr nor A. S. are installed on the IIS Server. They are installed on the CORP Server as well as the TERA Server. I was led to believe that would not be an issue. Is that correct?

Charles W. Anderson
MCSD.NET
Naia Corporation
Birmingham, AL

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-01-06 : 09:45:32

That shouldn't be a problem.

Ok, here's basically what I think the deal is. I suspect that you are running into some sort of Windows Authentication Permissions things (since you are using Windows Authentication and not SQL Server Authentication). So, that's why I said find a SQL Server authentication account that you can use to test this stuff out and get around the Windows auth problem. This cuts your network guy outta the equation, and will probably make things work.

You could just use EM to create a new SQL Server users for testing purposes....
Needless to say, you'll need to make some minor connection code changes to connect via SQL Server auth vs Windows Auth and I can help you with that.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

canderson
Starting Member

14 Posts

Posted - 2005-01-06 : 11:43:33

From my examinations, the code is pulling login info from the Web.config file which appears to be trying to connect via SQL Server Authentication. There was a mismatch with the login/password but even by correcting that or setting it to other login/passwords available, I could not get any other error message than the one we had received.

From the CORP server, I can connect to the TERA server with Query Analyzer using the desired SQL Server Administration login/password. That, at least, is a plus.

Charles W. Anderson
MCSD.NET
Naia Corporation
Birmingham, AL

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-01-06 : 11:49:37

Well Charles, something doesn't compute then. The code you posted above looked like it was trying to use Windows Authentication if I'm not mistaken. I think that the code is trying to connect one way, and you are feeding it login info for the other way. Double check your code and connection strings.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

dmbaker
Starting Member

1 Post

Posted - 2005-01-12 : 16:37:11

Have you found a solution to your problem? Here's some additional things to check, found a lot of this through painful trial and error and a lot of searching on the web...

1) Can you access the database through Analysis Manager, and process the database without error? If not, check the "OLAP Administrators" User Group on the box where Analysis Services is installed (if you're logged in and trying to access via Analysis Manager, then you need to be in "OLAP Administrators" on the A.S. server. Also make sure that the share "MSOLAPRepository$" on the server where A.S. is installed has not inadvertently had its permissions messed up (A.S. by default uses an MS Access database to store some metadata, access to this share and the files in it are necessary).

2) Is the problem accessing/administering the A.S. database from a web site? If so, note that you may have a problem if you're using NT Authentication, because IIS will not delegate your credentials from your workstation, through the web server, and then on to the A.S. server (e.g. it won't go across "two hops"--from your workstation to IIS uses NT authentication, but IIS will then try to access the A.S. server with the .NET "anonymous user" account, or the ASP.NET worker account). If this is the problem, then on the A.S. server you should see messages in the event log saying that an unathorized request was received, and the error will probably reference the ASP.NET account from your web server. Look in the Event Logs on the servers for errors on or around the time you try to access the system.

3) Make sure yor connection to the SQL Server database is valid, as already mentioned (can you access the database through Query Analyzer, with the UID/PWD you're specifing in your connection settings on the web site?)

Problem #2 can be difficult to diagnose let alone resolve...you *could* allow the anonymous user access to your Analysis Server (e.g. add the NT Anonymous User to OLAP Administrators) but that would be a bad idea (although it works...we've tried it here). Or, you can use Kerberos authentication (yeah, right) or supposedly use "basic" authentication somehow, but haven't tried that. I can post some more links to some more info on this, if you suspect this is the problem.

If you happen to be using XML for Analysis on your web server then I can say with fair certainty that you're encountering the second problem.

canderson
Starting Member

14 Posts

Posted - 2005-03-23 : 12:50:17

Sorry for the delay. I have had other issues to handle. First, the network guy solved the second problem (Processing cubes), but we have not resolved the first issue (Operation not allowed when the object is closed). However, I do have more specific details on the remaining error.

The connection string to OLAP (d.sOLAPConnStr) is set to "DataSource=[machine name]; InitialCatalog=[database]; SSPI=anonymous". This string is used to try and open a connection (d.mdConn.Open(d.sOLAPConnStr, sUserName, sPassword, 0); d is the connection object, mdConn is an ADODB.ConnectionClass, other 3 are strings). When the connection is attempted, though, I get the following error: "Property name was not recognized, 'SSPI'". It therefore fails to open the connection, so, when it tries to close it later, it gives the error "Operation not allowed when the object is closed" because the connection was never opened.

What I need to know is: What can I do to resolve the "Property name was not recognized, 'SSPI'" error? I found a message online that said anonymous users are not automatically assigned to the Everyone group in Server 2003 (the operating system of each of the machines involved). Anonymous users were automatically assigned to the Everyone group in Server 2000 (the previous machine's operating system, where this code worked). This is vague, though, and neither I nor the network guy for my client has been able to use this information to any effect. He has made some changes, including enabling anonymous users to be in the Everyone group on different machines, but no luck.

Any suggestions appreciated. Thanks.

Charles W. Anderson
MCSD.NET
Naia Corporation
Birmingham, AL

canderson
Starting Member

14 Posts

Posted - 2005-03-31 : 10:33:38

Attempting Integrated Windows authentication instead of Anonymous authentication. This called for an adjustment in the connection string to "DataSource=[machine name]; InitialCatalog=[database]; Integrated Security='SSPI'". New error message says "OLAP Server error: The operation requested failed due to security problems (the user could not be authenticated)." Searching online has yielded a vast array of possible solutions ranging from impersonating a Windows user in the machine.config or web.config to a complex set of grouping and trusting that goes beyond my experience or authorization.

I am at a loss to resolve this one. It probably is the Windows vs. SQL user issue described by MichaelP. If anyone has networking suggestions, please let me know. Thanks.

Charles W. Anderson
MCSD.NET
Naia Corporation
Birmingham, AL

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources