Solved

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

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

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now