C# Windows Service Problem

[DF Dev]

I have created a Windows Service using Visual Studio .Net 2005 and C# which
executes a series of database jobs on a time basis. I am using
SqlConnections and SqlCommand objects to connect to a SQL Server database
and execute stored procedures and query text commands. I am running into
this problem on some computers after a reboot is done. I get the following
error message when the service attempts to execute a SqlCommand:

Could not create an instance of OLE DB provider 'MSDASQL'.
OLE DB error trace [Non-interface error: CoCreate of DSO for MSDASQL
returned 0x80070005].

This seems to only be an issue on reboot because I can restart the service
and everything works fine after that.

Any help is greatly appreciated!

Thanks!

 

[Marc Gravell]

Does it connect to a database on the same machine? perhaps the
database hasn't awoken yet... perhaps add a service dependency, or
just set a delay in the top of your service... if neither helps, look
for other likely services that might still be waking up - RPC perhaps
(which drives a lot of DTC).

Marc

 

[Nicolas Noakes]

Could not create an instance of OLE DB provider 'MSDASQL'.
OLE DB error trace [Non-interface error: CoCreate of DSO for MSDASQL
returned 0x80070005].

This seems to only be an issue on reboot because I can restart the service
and everything works fine after that.

Have you configured your windows service to have a dependency for the SQL
Server service? If not, your own service may be starting before the SQL
Server service, in which case it is necessarily not available yet.

To see the service dependencies for a particular service, go to the services
applet in your control panel, right-click and select properties on the
service in question, and open the Dependencies tab.

Nicolas

 

[DF Dev]

Yes it connects to a database on the same machine. I have already added
code to wait for SQL at the top of the service and I also have a dependency
for SQL Server. I am not checking RPC but I will look into that.

 

[DF Dev]

Yes the SQL dependency is there.. Thanks.

Could not create an instance of OLE DB provider 'MSDASQL'.
OLE DB error trace [Non-interface error: CoCreate of DSO for MSDASQL
returned 0x80070005].

This seems to only be an issue on reboot because I can restart the
service and everything works fine after that.

Have you configured your windows service to have a dependency for the SQL
Server service? If not, your own service may be starting before the SQL
Server service, in which case it is necessarily not available yet.

To see the service dependencies for a particular service, go to the
services applet in your control panel, right-click and select properties
on the service in question, and open the Dependencies tab.

Nicolas

 

[Marc Gravell]

It might getas you simply have to keep trying (with pause) until you
can connect, and not reporting success until you have succeeded, or a
certain "give up" threshhold is met. Alternatively, attempt to connect
as part of start-up, and use the service recovery options (first
failure, second failure, etc) to keep trying every minute-or-so.

Marc

 

[DF Dev]

The wierd thing is Marc that I have already done that. When the service
starts, it tries to connect to SQL. If it can't, it sleeps for so long then
tries again. It goes through this process of trying to connect several
times and if it can't it stops. But the service is starting right up so it
is connecting to the database just fine. But it throws the "CoCreate" error
the first time it tries to execute a command.

 

[Willy Denoyette [MVP]]

The wierd thing is Marc that I have already done that. When the service
starts, it tries to connect to SQL. If it can't, it sleeps for so long
then tries again. It goes through this process of trying to connect
several times and if it can't it stops. But the service is starting right
up so it is connecting to the database just fine. But it throws the
"CoCreate" error the first time it tries to execute a command.

0x80070005 means Access Denied, that means that you are trying to
load/access the DSO provider library, but the current user has not the
appropriate privileges to do so.
Mind to post some code? Especially the connection string(s) might be
helpful. Note that connecting to SQL server and using DSO are different
things.

Willy.

 

[DF Dev]

0x80070005 means Access Denied, that means that you are trying to
load/access the DSO provider library, but the current user has not the
appropriate privileges to do so.
Mind to post some code? Especially the connection string(s) might be
helpful. Note that connecting to SQL server and using DSO are different
things.

Willy.

But shouldn't it do the same thing when you restart the service manually??
It works fine when you go into Services and restart it. It just doesn't
work on boot up.

Here is some sample code:

SqlConnection CurrentConnection = new SqlConnection();
strConnectionString = @"data source=MyServer;

initial catalog=MyDatabase;

integrated security=SSPI;

persist security info=False;

Trusted_Connection=Yes";

CurrentConnection.ConnectionString = strConnectionString;

CurrentConnection.Open();

JobsCommand = new SqlCommand();

JobsCommand.CommandText = "exec sp_Test"

JobsCommand.Connection = CurrentConnection;

JobsCommand.CommandTimeout = 0;

JobsCommand.ExecuteNonQuery();

 

[Willy Denoyette [MVP]]

But shouldn't it do the same thing when you restart the service manually??
It works fine when you go into Services and restart it. It just doesn't
work on boot up.

Here is some sample code:

SqlConnection CurrentConnection = new SqlConnection();
strConnectionString = @"data source=MyServer;

initial catalog=MyDatabase;

integrated security=SSPI;

persist security info=False;

Trusted_Connection=Yes";

CurrentConnection.ConnectionString = strConnectionString;

CurrentConnection.Open();

JobsCommand = new SqlCommand();

JobsCommand.CommandText = "exec sp_Test"

JobsCommand.Connection = CurrentConnection;

JobsCommand.CommandTimeout = 0;

JobsCommand.ExecuteNonQuery();

Above code cannot throw the exception as you initially posted....
<Could not create an instance of OLE DB provider 'MSDASQL'.
OLE DB error trace [Non-interface error: CoCreate of DSO for MSDASQL
returned 0x80070005].
/>
above code connects to SQL Server using the SQLClient provider, the error
message refers to the OLEDB provider 'MSDASQL' which is an unmanaged COM
provider.
What exactly is "sp_Test" doing here?
Does MyServer refers to a local instance?
What's the identity the service is running under?

Willy.

 

[DF Dev]

But shouldn't it do the same thing when you restart the service
manually?? It works fine when you go into Services and restart it. It
just doesn't work on boot up.

Here is some sample code:

SqlConnection CurrentConnection = new SqlConnection();
strConnectionString = @"data source=MyServer;

initial catalog=MyDatabase;

integrated security=SSPI;

persist security info=False;

Trusted_Connection=Yes";

CurrentConnection.ConnectionString = strConnectionString;

CurrentConnection.Open();

JobsCommand = new SqlCommand();

JobsCommand.CommandText = "exec sp_Test"

JobsCommand.Connection = CurrentConnection;

JobsCommand.CommandTimeout = 0;

JobsCommand.ExecuteNonQuery();

Above code cannot throw the exception as you initially posted....
<Could not create an instance of OLE DB provider 'MSDASQL'.
OLE DB error trace [Non-interface error: CoCreate of DSO for MSDASQL
returned 0x80070005].
/>
above code connects to SQL Server using the SQLClient provider, the error
message refers to the OLEDB provider 'MSDASQL' which is an unmanaged COM
provider.
What exactly is "sp_Test" doing here?
Does MyServer refers to a local instance?
What's the identity the service is running under?

Willy.

I don't use an OLEDB provider anywhere in the program.. I only use the
SQLClient provider so I don't know. And I just used "sp_Test" as an example
to show how it is executing stored procedures. This same code executes a
bunch of stored procedures stored in a datatable.
Here is the actual line of code: JobsCommand.CommandText = "exec " +
JobStepDataRow["Command"].ToString();

Yes, MyServer refers to a local instance.

I'm not sure if this answers your question about the identity the service is
running under or not but the service runs under the LocalSystem account.

 

[Willy Denoyette [MVP]]

The wierd thing is Marc that I have already done that. When the
service starts, it tries to connect to SQL. If it can't, it sleeps
for so long then tries again. It goes through this process of trying
to connect several times and if it can't it stops. But the service is
starting right up so it is connecting to the database just fine. But
it throws the "CoCreate" error the first time it tries to execute a
command.

It might getas you simply have to keep trying (with pause) until you
can connect, and not reporting success until you have succeeded, or a
certain "give up" threshhold is met. Alternatively, attempt to
connect
as part of start-up, and use the service recovery options (first
failure, second failure, etc) to keep trying every minute-or-so.

Marc




0x80070005 means Access Denied, that means that you are trying to
load/access the DSO provider library, but the current user has not the
appropriate privileges to do so.
Mind to post some code? Especially the connection string(s) might be
helpful. Note that connecting to SQL server and using DSO are different
things.

Willy.


But shouldn't it do the same thing when you restart the service
manually?? It works fine when you go into Services and restart it. It
just doesn't work on boot up.

Here is some sample code:

SqlConnection CurrentConnection = new SqlConnection();
strConnectionString = @"data source=MyServer;

initial catalog=MyDatabase;

integrated security=SSPI;

persist security info=False;

Trusted_Connection=Yes";

CurrentConnection.ConnectionString = strConnectionString;

CurrentConnection.Open();

JobsCommand = new SqlCommand();

JobsCommand.CommandText = "exec sp_Test"

JobsCommand.Connection = CurrentConnection;

JobsCommand.CommandTimeout = 0;

JobsCommand.ExecuteNonQuery();

Above code cannot throw the exception as you initially posted....
<Could not create an instance of OLE DB provider 'MSDASQL'.
OLE DB error trace [Non-interface error: CoCreate of DSO for MSDASQL
returned 0x80070005].
/>
above code connects to SQL Server using the SQLClient provider, the error
message refers to the OLEDB provider 'MSDASQL' which is an unmanaged COM
provider.
What exactly is "sp_Test" doing here?
Does MyServer refers to a local instance?
What's the identity the service is running under?

Willy.

I don't use an OLEDB provider anywhere in the program..

Something must use OLEDB, aren't you refering to Linked Servers in the SP's?

I only use the

SQLClient provider so I don't know.

The error message refers to the OLEDB provider for DSO, that's why I said
that the code you posted is not the source of the error message.

And I just used "sp_Test" as an example

to show how it is executing stored procedures.

So sp_Test is a fictive SP?

This same code executes a

bunch of stored procedures stored in a datatable.
Here is the actual line of code: JobsCommand.CommandText = "exec " +
JobStepDataRow["Command"].ToString();

Check the SP's, they must be connecting, using the OLEDB provider 'MSDASQL',
to some 'linked' server (local or remote).

Yes, MyServer refers to a local instance.
Yep.

I'm not sure if this answers your question about the identity the service
is running under or not but the service runs under the LocalSystem
account.

Keep in mind that "LocalSystem" has no network access privileges.

Willy.

 

[DF Dev]

The wierd thing is Marc that I have already done that. When the
service starts, it tries to connect to SQL. If it can't, it sleeps
for so long then tries again. It goes through this process of trying
to connect several times and if it can't it stops. But the service
is starting right up so it is connecting to the database just fine.
But it throws the "CoCreate" error the first time it tries to execute
a command.

It might getas you simply have to keep trying (with pause) until you
can connect, and not reporting success until you have succeeded, or
a
certain "give up" threshhold is met. Alternatively, attempt to
connect
as part of start-up, and use the service recovery options (first
failure, second failure, etc) to keep trying every minute-or-so.

Marc




0x80070005 means Access Denied, that means that you are trying to
load/access the DSO provider library, but the current user has not the
appropriate privileges to do so.
Mind to post some code? Especially the connection string(s) might be
helpful. Note that connecting to SQL server and using DSO are
different things.

Willy.


But shouldn't it do the same thing when you restart the service
manually?? It works fine when you go into Services and restart it. It
just doesn't work on boot up.

Here is some sample code:

SqlConnection CurrentConnection = new SqlConnection();
strConnectionString = @"data source=MyServer;

initial catalog=MyDatabase;

integrated security=SSPI;

persist security info=False;

Trusted_Connection=Yes";

CurrentConnection.ConnectionString = strConnectionString;

CurrentConnection.Open();

JobsCommand = new SqlCommand();

JobsCommand.CommandText = "exec sp_Test"

JobsCommand.Connection = CurrentConnection;

JobsCommand.CommandTimeout = 0;

JobsCommand.ExecuteNonQuery();




Above code cannot throw the exception as you initially posted....
<Could not create an instance of OLE DB provider 'MSDASQL'.
OLE DB error trace [Non-interface error: CoCreate of DSO for MSDASQL
returned 0x80070005].
/>
above code connects to SQL Server using the SQLClient provider, the
error message refers to the OLEDB provider 'MSDASQL' which is an
unmanaged COM provider.
What exactly is "sp_Test" doing here?
Does MyServer refers to a local instance?
What's the identity the service is running under?

Willy.

I don't use an OLEDB provider anywhere in the program..

Something must use OLEDB, aren't you refering to Linked Servers in the
SP's?

I only use the

SQLClient provider so I don't know.

The error message refers to the OLEDB provider for DSO, that's why I said
that the code you posted is not the source of the error message.

And I just used "sp_Test" as an example

to show how it is executing stored procedures.

So sp_Test is a fictive SP?

This same code executes a

bunch of stored procedures stored in a datatable.
Here is the actual line of code: JobsCommand.CommandText = "exec " +
JobStepDataRow["Command"].ToString();

Check the SP's, they must be connecting, using the OLEDB provider
'MSDASQL', to some 'linked' server (local or remote).

Yes, MyServer refers to a local instance.
Yep.

I'm not sure if this answers your question about the identity the service
is running under or not but the service runs under the LocalSystem
account.

Keep in mind that "LocalSystem" has no network access privileges.

Willy.

You are correct Willy, the stored procedure is using OLEDB to transfer data
to another database. Good job! I still don't understand why it works after
you restart the service though unless the issue is that another service is
not completely up and running on reboot. But I am going to play around with
those dependencies and see what I can come up with.

Thanks for your help!

 

[Willy Denoyette [MVP]]

You are correct Willy, the stored procedure is using OLEDB to transfer
data to another database. Good job! I still don't understand why it
works after you restart the service though unless the issue is that
another service is not completely up and running on reboot. But I am
going to play around with those dependencies and see what I can come up
with.

Thanks for your help!

I don't see why a restart could possibly solve this, the stored proc. is
trying to load the OLEDB provider (which one?) and fails to do so because
the caller has no privileges to load the provider DLL. The caller in this
case is, or the impersonated service identity (SP's are executed by SQL
server), or the SQL server's identity, or another identity as impersonated
by the SP.
So, IMO the identity is switched between the initial service start and the
restart.
Only way to catch this is by enabling authoring at the File level, that is,
you need to find out who's actually loading the provider dll.

Willy.

 

[DF Dev]

I looked in the stored procedure and I think this is the code that is
getting the error..

select @DSN = 'DSN=TestDSN;UID=sa;PWD=admin'

select @SQLText = "insert into #tempserver select *
from OPENROWSET('MSDASQL', " + @DSN + ", 'select @@servername')"
exec (@SQLText)

I have a System DSN set up (TestDSN) pointing to my local database and it
uses the SQL Server driver.

I don't understand why restarting the service solves the problem either but
trust me it does.. After looking at this code, do you still think it is an
identity issue?

Thanks!

 

[Willy Denoyette [MVP]]

I looked in the stored procedure and I think this is the code that is
getting the error..

select @DSN = 'DSN=TestDSN;UID=sa;PWD=admin'

select @SQLText = "insert into #tempserver select *
from OPENROWSET('MSDASQL', " + @DSN + ", 'select @@servername')"
exec (@SQLText)

I have a System DSN set up (TestDSN) pointing to my local database and it
uses the SQL Server driver.

I don't understand why restarting the service solves the problem either
but trust me it does.. After looking at this code, do you still think it
is an identity issue?

Thanks!

You aren't using the SQL Server driver in this SP, you are using 'MSDASQL'
as specified in the OPENROWSET. This aside, you have a security issue when
using an instance of this provider from within a SP, please read the
"Security Considerations" on this page
http://msdn2.microsoft.com/en-us/library/ms175915.aspx
Note that your issue has nothing to do with C# nor with your service, I
suggest you to post this to one of the SQL server NG's, you sure will get
better help there.

Willy.

 

[DF Dev]

You aren't using the SQL Server driver in this SP, you are using 'MSDASQL'
as specified in the OPENROWSET. This aside, you have a security issue when
using an instance of this provider from within a SP, please read the
"Security Considerations" on this page
http://msdn2.microsoft.com/en-us/library/ms175915.aspx
Note that your issue has nothing to do with C# nor with your service, I
suggest you to post this to one of the SQL server NG's, you sure will get
better help there.

Willy.

Ok, I will look into this and post to another group.. You have been a lot
of help! Thanks!!