Solved

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

Posted on 2006-06-21
9
625 Views
Last Modified: 2008-01-09
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!
0
Comment
Question by:drliebs
  • 5
  • 4
9 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16956628
>>but when I run the Stored Procedure from .net I get the following exception:<<
Does this user have SysAdmin permissions?
0
 

Author Comment

by:drliebs
ID: 16959510
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16959603
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:drliebs
ID: 16960590
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16963590
Just change your Connection string to use a user with SQL Server Authentication.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16963608
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
 

Author Comment

by:drliebs
ID: 16965224
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 16965250
>>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
 

Author Comment

by:drliebs
ID: 16985679
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question