|
|
Posted by John Bell on 05/27/07 08:56
Hi
"Myster Edd" <mysteredd@gmail.com> 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
Navigation:
[Reply to this message]
|