Permission Setup to Run DTS from Web

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Permission Setup to Run DTS from Web

Author

Topic

besadmin
Posting Yak Master

116 Posts

Posted - 2008-12-09 : 10:25:23

Hey guys,

I need help with what permissions to change/add, so my DTS package will run successfully from the web. I finally get all the way to execution and it fails. I am like 95% sure it is a permission issue.

Any help is greatly appreciated.

Thanks!

-S

besadmin
Posting Yak Master

116 Posts

Posted - 2008-12-10 : 18:29:44

If this helps any i turned on some logging and this is the error i get when trying to run it from the asp page. also note, this does run successfully from Enterprise Manager.

****************************************************************************************************
The execution of the following DTS Package succeeded:

Package Name: TEST_DTS_SMM
Package Description: SMM DTS From Web Test
Package ID: {00B1C08B-E46C-438E-9089-BF44955B0A06}
Package Version: {45849106-3157-41E7-A17A-7EE7586D3351}
Package Execution Lineage: {2A0E82B1-AFE0-4FA8-B44A-74B5DF405B55}
Executed On: WILBUR
Executed By: administrator
Execution Started: 12/10/2008 5:30:36 PM
Execution Completed: 12/10/2008 5:30:36 PM
Total Execution Time: 0.015 seconds

Package Steps execution information:

Step 'DTSStep_DTSActiveScriptTask_1' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied

Error on Line 8

Step Error code: 800403FE
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100

Step Execution Started: 12/10/2008 5:30:36 PM
Step Execution Completed: 12/10/2008 5:30:36 PM
Total Step Execution Time: 0.015 seconds
Progress count in Step: 0

besadmin
Posting Yak Master

116 Posts

Posted - 2008-12-17 : 15:43:54

Can someone please help me?!

I always get Permission Denies trying to run dts no matter what i do!
I can run from enterprise manager but thats it!

From web / stored proc using sp_OA / stored proc using xp_cmdshell dtsrun - all give me permission denied.

Can anyone please tell me what i am missing?

Thanks a ton!

-S

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-17 : 16:02:21

If it runs successfully from Enterprise Manager, then it's a problem with the SQL Server service account. What account are you using for the SQL Server service account?

Without being too detailed, please describe what the DTS package does for instance each step and if any of them are using remote resources.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

besadmin
Posting Yak Master

116 Posts

Posted - 2008-12-18 : 12:10:42

hey thanks for responding!

i am not sure which account is used for sql server service account. how can i tell?

The job is just a sample i made up to test this. it is just an active x script that writes text to a file...here is the script.


'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()

dim filesys, filetxt, getname, path, myTime
Set filesys = CreateObject("Scripting.FileSystemObject")
Set filetxt = filesys.CreateTextFile("\\Server\C$\\DTSTextTest.txt", True)
path = filesys.GetAbsolutePathName("\\Server\C$\C:\")
myTime = Time ()
getname = filesys.GetFileName(path)
filetxt.WriteLine("This Text File Was Created Successfully by an ActiveX Script in a SQL DTS Job at: " & myTime)
filetxt.Close

Main = DTSTaskExecResult_Success
End Function

also, do i need anything else at all in the DTS job itself? all i have is the activex script, and i tried it with and without a sql ole db connection.

Thanks for any help!

let me know if you need anything else.

-S

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-18 : 12:18:09

Check the MSSQL service in the Services applet which can be found in Administrative Tools.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

besadmin
Posting Yak Master

116 Posts

Posted - 2008-12-18 : 16:56:58

The sql service account MSSQLSERVER says log on as...

OUR_DOMAIN\SQLAgent

THanks for your help!!

-S

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-18 : 16:58:51

Does that user have the permissions that are needed in the DTS package? To test this out, login directly to the SQL Server using the console, Remote Desktop, Terminal Services, ..., make sure to login with the OUR_DOMAIN\SQLAgent account. Now run the DTS Package in Enterprise Manager in the designer. From here, you should see it fail and it should show on what step.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources