Accessing SQL Server 2005 via Scheduled Task

I have a strange problem that I think deals with security on SQL 2005.
I have a scheduled task that runs on a Windows 2000 machine. It calls
a vb script which creates a connection to SQL Server.

We migrated a database from SQL 2000 to 2005 which is on a different
box. I changed the connection in the vb script to use the new sql
server. The original connection to SQL 2000 used the 'sa' account
coded into the connection string , which we don't want to use on the
new server, so I changed the connection string in the script to use
the below login information.

Const strConnection = "Provider=SQLOLEDB;Data
Source=SQLServer;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=database;

I created a domain user and gave it dbo rights on the new database on
SQL 2005 as well as administrative rights on the local machine and the
network. The task runs fine for a while and then it will fail to
start. I have looked in the event log as well as the SQL log and have
not found anything else that ran when my task failed. Once it has
failed, if I manually run the vb script on the 2000 machine, it runs
just fine, but the schedule won't work. If I change the name of the
user that is running the scheduled task, it will begin working again.
I have run the profiler on SQL 2005 and watched the scheduled task
login as the correct user and update the database. There is no
pattern to when the scheduled task will stop running. This has been
happening for a few days now.

This script and scheduled task worked fine for over a year on the
machine when it logged into SQL 2000 and nothing else has changed,
which makes me think it is related to the SQL 2005 server. Any ideas?

Re: Accessing SQL Server 2005 via Scheduled Task

Hi

"Myster Edd" wrote in message
news:1179861414.783713.3260@z24g2000prd.googlegroups.com...
>I have a strange problem that I think deals with security on SQL 2005.
> I have a scheduled task that runs on a Windows 2000 machine. It calls
> a vb script which creates a connection to SQL Server.
>
> We migrated a database from SQL 2000 to 2005 which is on a different
> box. I changed the connection in the vb script to use the new sql
> server. The original connection to SQL 2000 used the 'sa' account
> coded into the connection string , which we don't want to use on the
> new server, so I changed the connection string in the script to use
> the below login information.
>
> Const strConnection = "Provider=SQLOLEDB;Data
> Source=SQLServer;Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=database;
>
> I created a domain user and gave it dbo rights on the new database on
> SQL 2005 as well as administrative rights on the local machine and the
> network. The task runs fine for a while and then it will fail to
> start. I have looked in the event log as well as the SQL log and have
> not found anything else that ran when my task failed. Once it has
> failed, if I manually run the vb script on the 2000 machine, it runs
> just fine, but the schedule won't work. If I change the name of the
> user that is running the scheduled task, it will begin working again.
> I have run the profiler on SQL 2005 and watched the scheduled task
> login as the correct user and update the database. There is no
> pattern to when the scheduled task will stop running. This has been
> happening for a few days now.
>
> This script and scheduled task worked fine for over a year on the
> machine when it logged into SQL 2000 and nothing else has changed,
> which makes me think it is related to the SQL 2005 server. Any ideas?
>
I assume that the task worked fine when you profiled it on the SQL 2005
server? Have you run a server side trace to trap this being run when it
fails?
How often does this task run?
As this sounds like a windows scheduled task are you using SQL Express?
Have you installed any service packs on the SQL Server (SELECT @@VERSION)?
Does the schedule run on a different machine to the server?
Do you run AV or IDS software on the database server?

John