Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2006-06-21
9
Medium Priority
?
731 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 1000 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

604 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