Errors when accessing data from a webservice

[Stephen Livesey]

Hi,

I am having a problem developing a web service that is accessing data using
OLE or ODBC, however this problem is only occurring on 1 development PC
(mine), I have tested the same web service on other PC's and it works fine.
I have also written applications that use the same data providers and
perform the same queries, these applications work perfectly, I only get the
problem when using web services.

To test the problem, I have created new web service, this web service has
two methods, one using ODBC and the other using OLE, both methods perform
the same simple query as follows:

[WebMethod]
public DataSet GetDataSetUsingOdbc()
{
DataSet dataSet = new DataSet();
OdbcConnection odbcConnection = new
OdbcConnection("DSN=Temp5;UID=;NODE=;APPLICATION=");
OdbcDataAdapter odbcDataAdapter = new OdbcDataAdapter("select * from
expppgi",odbcConnection);
odbcDataAdapter.Fill(dataSet,"Table");
odbcConnection.Close();
if (dataSet != null)
{
return dataSet;
}
return null;
}

[WebMethod]
public DataSet GetDataSetUsingOle()
{
DataSet dataSet = new DataSet();
OleDbConnection oleDbConnection = new
OleDbConnection("Provider=CONNXOLEDB.1;Mode=ReadWrite;User ID=ste;Data
Source=c:\\connx32\\cdds\\temp5.cdd;Extended Properties=;Persist Security
Info=False;Location=");
OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter("select * from
expppgi",oleDbConnection);
oleDbDataAdapter.Fill(dataSet,"Table");
oleDbConnection.Close();
if (dataSet != null)
{
return dataSet;
}
return null;
}


When I execute the Web Service and use the OLE method, the following error
is produced:

System.Data.OleDb.OleDbException: No error information available:
E_UNEXPECTED(0x8000FFFF). at
System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at
System.Data.OleDb.OleDbConnection.InitializeProvider() at
System.Data.OleDb.OleDbConnection.Open() at
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection,
ConnectionState& originalState) at
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet
dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand
command, CommandBehavior behavior) at
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) at
ConnxTest.Service1.GetDataSetUsingOle() in
c:\inetpub\wwwroot\connxtest\service1.asmx.cs:line 80When I execute the Web
Service and use the ODBC method, the following error is produced:

System.NullReferenceException: Object reference not set to an instance of an
object. at System.Data.Common.Odbc32.SQLFreeHandle(Int16 HandleType,
HandleRef StatementHandle) at
System.Data.Odbc.DBCWrapper.CloseAndRelease() at
System.Data.Odbc.OdbcConnection.DisposeClose() at
System.Data.Odbc.OdbcConnection.Open() at
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection,
ConnectionState& originalState) at
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet
dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand
command, CommandBehavior behavior) at
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) at
ConnxTest.Service1.GetDataSetUsingOdbc() in
c:\inetpub\wwwroot\connxtest\service1.asmx.cs:line 60
Both of the errors occur when the Fill command is being executed.

The PC is using Visual Studio 2003 and Windows XP Professional, I have also
installed MDAC v2.8 but this had no effect.



Thanks

Stephen Livesey

 

[Dave Mc]

Does your WEB service (ASP.NET Login / IIS Login or everybody) actually have access to:
- The ODBC dll's ?
- The Database files ?
- Any other propriety dll's that CONNXOLEDB uses ?

Try setting your web.config file to use the administrator account as the security context to run under as a test. That will eliminate security as a problem if it still does not work.

Looking at the errors though, it seems to hint that the sql connection handle / results handle is being prematurely destroyed - so this is probably not security. Try creating and connecting the DB connection object before the fill in a separate variable, and then disconnecting afterwards yourself manually (i.e. Don't let .net handle it for you) - maybe the web service is destroying the connection object before the results are processed, and the odbc driver is automatically destroying the results when the connection is destroyed.

 

[Stephen Livesey]

Thank you for your help, I have now managed to resolve the problem which was
due to security.

I wasn't sure how to set my web.config file to use the administrator
account, so instead I made the following users members of the
'Administrators' group:
iuser_pcname
iwam_pcname
aspnet

This cured the problem and the web service worked.

I then removed the 'Administrators' group from these 3 users, expecting it
to stop my web service from working, however it didn't, it's still working!

Very strange, but the problem is now resolved.

Thanks
Stephen Livesey

Does your WEB service (ASP.NET Login / IIS Login or everybody) actually have access to:
- The ODBC dll's ?
- The Database files ?
- Any other propriety dll's that CONNXOLEDB uses ?

Try setting your web.config file to use the administrator account as the

security context to run under as a test. That will eliminate security as a
problem if it still does not work.

Looking at the errors though, it seems to hint that the sql connection

handle / results handle is being prematurely destroyed - so this is probably
not security. Try creating and connecting the DB connection object before
the fill in a separate variable, and then disconnecting afterwards yourself
manually (i.e. Don't let .net handle it for you) - maybe the web service is
destroying the connection object before the results are processed, and the
odbc driver is automatically destroying the results when the connection is
destroyed.

 

[Dave Mc]

Careful of this one (Setting permissions and then removing the permissions and it still works)
The problem I am having is similar - to get my Web Service to work I change the permissions for the "everyone" login in my virtual directory. This work fine until the PC is rebooted, then I have to go and change them back - then it works - then I reboot, then I have to change the permissions again .... ad nauseam

i.e. Before you think that you have resolved the issue - reboot the machine and test. But this also does not mean that it will work when it's deployed. Then doing your deployment testing, remeber to reboot the machine being deployed on and re-test

p.s. Web.Config (Administrator
<configuration><system.web><identity impersonate=true userName="ComputerName\Administrator" password="whatever"/></system.web><configuration
i.e. Just add the "<identity" line to your config file
Source:http://msdn.microsoft.com/library/d...y/en-us/cpgenref/html/gngrfidentitysection.as
Gewd Luck

 

[Dave Mc]

Correction
<configuration><system.web><identity impersonate="true" userName="ComputerName\Administrator" password="whatever"/></system.web><configuration

I was missing the Quotes around true.