Solved

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

Posted on 2006-06-21
9
640 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Activity Monitor detail 2 23
MS SQL: Create User Function to Remove Long Words 5 31
SSMS Opening Mode 9 18
MS SQL + group by time 4 11
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

789 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