468,135 Members | 1,465 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,135 developers. It's quick & easy.

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?

May 22 '07 #1
1 6840
Hi

"Myster Edd" <my*******@gmail.comwrote in message
news:11********************@z24g2000prd.googlegrou ps.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
May 27 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Colin Steadman | last post: by
5 posts views Thread by A. Lovhaug | last post: by
6 posts views Thread by John Bowman | last post: by
1 post views Thread by NiponW | last post: by
9 posts views Thread by Mantorok | last post: by
27 posts views Thread by didacticone | last post: by
1 post views Thread by gcdp | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.