July 24, 2009 at 7:12 am
I'm in the process of migrating some SQL 2000 and 2005 jobs to a remote management system, rather than SQL Server Agent. While I've sorted through most of the security issues involved in SQL 2005, I am at an impasse for SQL 2000.
The application, called Control-M, essentially has an agent on the same machine as the SQL server. The central program sends the request to the agent to run a job (be it SQL, Windows related, Sharepoint, etc.) In order for me to do so, I have to create a Control-M agent account on my SQL instance and figure out the proper elevated permissions to run certain packages. Currently, they can run DTS packages via the command line dtsrun application so long as the account has the appropriate permissions in the affected databases and read access to msdb (standard DTS permission issues).
My plan is to have sqlcmd (or osql in the case of SQL 2000) log into the server and call exec msdb.dbo.sp_start_job 'Job Name'. SQL 2005 has fine grained permissions available via msdb roles, but for SQL 2000, BOL says only accounts with sysadmin level can do this. My question is: Is there another way to remotely execute sp_start_job without granting the account sysadmin role in SQL 2000?
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
July 24, 2009 at 9:15 am
Hello Gaby,
I'm using TWS, a work scheduler as well. All jobs are running out of the sql job scheduler, and working for 2000 and 2005. But so that It was possible, we had to create a default infrastructure to create these jobs.
Basically, like your plan, the TWS call a SQLCMD that call other CMD with the password configurations. Using the ISQL command to run SQL Scripts:
isql -S${server} -U${user} -P${passwd} -i"../sql/${JOB}.sql" -p -w8000 -n > ../log/${JOB}.log 2>&1
To run SQL 2000 DTS in the command, you must to get the command line in your SQL Job, something like this:
DTSRun /~Z0x49410B039614211C6C5953DEB6B2F344705DF54739E0677501B40E9771954F8BE6F8F4838A6EF7927EB4FFB0157CEB22D5625F0638BAA118F37BDC0A2572E860A880E4A15363B5B19D3C62CABC8EE922B1DBFED6D69579BA461AA555CD819EDF3630036D06A1378ADBB7D9C9F20217887F7371743994A48F8178D7
and then run it in the cmd.
You can create a .bat file with this code and schedule in the Control-M.
Look an example of the execution in the cmd:
****************************************************************************
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:\Documents and Settings\ftafarelo>DTSRun /~Z0x49410B039614211C6C5953DEB6B2F344
705DF54739E0677501B40E9771954F8BE6F8F4838A6EF7927EB4FFB0157CEB22D5625F0638BAA118
F37BDC0A2572E860A880E4A15363B5B19D3C62CABC8EE922B1DBFED6D69579BA461AA555CD819EDF
3630036D06A1378ADBB7D9C9F20217887F7371743994A48F8178D7
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2
In the other hand, you asked about execute cmdexec without granting sysadmin. You can configure the sql_proxy_account. Look this article, and if you have any question, feel free to ask: http://support.microsoft.com/kb/890775
There are many details in our enviroment ready to run these automated jobs, if you want to know more, or you have doubts, I can explain more details later.
I hope this helps you.
See u,
Tafarelo
http:\\dicasdeumdba.wordpress.com
Brazilian blog about database servers
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply