Stored Procedure that creates and runs a job, called from .NET

I am trying to run a stored Procedure from a .NET page, which creates a job, that runs a DTS package.  It works fine when I run it from the server, but when I run the Stored Procedure from .net I get the following exception:

System.Data.SqlClient.SqlException: The specified '@owner_login_name' is invalid (valid values are returned by sp_helplogins [excluding Windows NT groups]).

Some permission issue I guess.  Can anyone help me get this running, I am so close!
drliebsAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
>>So, the answer is that the DTS package is done and working well, it could be better I'm sure but I don't want to try to rewrite it just yet. <<
Nobody is suggesting you do.  Just execute it directly, rather than creating a job and running the job.  See here:
http://www.sqldev.net/dts/ExecutePackage.htm#Visual%20Csharp
http://www.sqldev.net/dts/DotNETCookBook.htm
Or shell out to it directly using DTSRun.exe

>>What makes this a bigger security risk?  <<
The fact that in order to create a job the user has to have SysAdmin permissions.  In general, you do not give application users SysAdmin permissions.
0
 
Anthony PerkinsCommented:
>>but when I run the Stored Procedure from .net I get the following exception:<<
Does this user have SysAdmin permissions?
0
 
drliebsAuthor Commented:
Yes I have granted sysadmin rights to myself, domain admins, the account that the sql service is running and the network service for IIS.  I believe that when the SP is called from the web page it is the network service that is actually running the SP and the job it creates, but correct me if I am wrong because I am pretty new at this..
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Anthony PerkinsCommented:
So when you "run the Stored Procedure from .net" the user in the connection string has SysAdmin permissions?  If that is the case, than post your code.
0
 
drliebsAuthor Commented:
My Connection string:
server=sqlserver;Integrated Security =SSPI; Initial Catalog=monthlyReports

The Code used to create the SP, when run from the server the SP is created, and executing the SP works fine...

create procedure stp_ExecuteMonthlyDTS
as

-- Initialize command
declare @cmd varchar(4000)
set @cmd = '"C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\BINN\Dtsrun.exe" /S "(local)" /N "MonthlyDTS" /G "{08B900FD-8390-4CCC-AC3E-2567D01980F7}" /W "0" /E '

DECLARE @job_id_out UNIQUEIDENTIFIER
DECLARE @jname varchar(128)
SET @jname = 'Run MonthlyDTS DTS Package'


--Create a job
EXEC msdb.dbo.sp_add_job
      @job_name                   = @jname,
      @enabled                   = 1,
      @delete_level                  = 1,
      @description                   = 'This is a job used to run the MonthlyDTS Package',
      @category_name                   = 'ASP',
      @start_step_id                   = 1,
      @notify_level_eventlog             = 3,
      @job_id                   = @job_id_out OUTPUT

EXEC msdb.dbo.sp_add_jobserver
      @job_id                   = @job_id_out,
      @server_name                  = '(local)'

 -- Add a step to the job just created
EXEC msdb.dbo.sp_add_jobstep
      @job_id                   = @job_id_out,
      @step_id                   = 1,
      @step_name                   = 'Start MonthlyDTS Package',
      @subsystem                   = 'CMDEXEC',
      @command                   = @cmd

EXEC msdb.dbo.sp_help_job
      @job_name                   = @jname

--Start job
EXEC msdb.dbo.sp_start_job @jname
GO

execute stp_ExecuteMonthlyDTS

0
 
Anthony PerkinsCommented:
Just change your Connection string to use a user with SQL Server Authentication.
0
 
Anthony PerkinsCommented:
That is a user with SysAdmin right.

But the bigger question is why you are going to such extremes (not to mention the security risk) to execute a DTS Package, when you can execute the DTS Package directly.
0
 
drliebsAuthor Commented:
Just change your Connection string to use a user with SQL Server Authentication...

I will try that.

not to mention the security risk...

As I said before, I am new at this.  So, the answer is that the DTS package is done and working well, it could be better I'm sure but I don't want to try to rewrite it just yet.  It is complex enough that it seemed at the time an effective way to accomplish the task.  So why not run a SP that creates a job to run a DTS package?  

My net page sets a date range using calendar controls, and verifies that it is approrpriate.  Launches the SP that calls the job.  What makes this a bigger security risk?  
0
 
drliebsAuthor Commented:
I ended up assigning @owner under EXEC msdb.dbo.sp_add_job section in the stored procedure that creates the job and SP that is called from .net (stp_ExecuteMonthlyDTS).  The IIS service, and user who will run this .net page can retain their basic levels of authorization.  

That worked for me.  I don't know if this is the best way to accomplish this from a security standtpoint, but now my package can be run on a schedule, and the Stored Procedure which creates the job to run this package can be called from .net as needed.  Thanks for sticking with me and helping me understand some of the different aspects a little better. Well worth the points and desrveing of an A score.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.