DTS security/permissions problem.

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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS security/permissions problem.

Author

Topic

SK
Starting Member

4 Posts

Posted - 2004-03-28 : 18:00:50

Hello

I have a problem with a DTS that maybe one of you may know the answer.

I have read everything on the net related and found some help on other articles but I found a dead end right now, so here's the situation.

I have a DTS that is run from an ASP.net page, this DTS receives several parameters from a temporal table, then pases it to a Transform object so a query runs on those parameters and then exports the results to an excel file.

The excel file is on a shared folder on the SQL server with 'everyone' with 'full control' as the user for that folder, the problem is that I have an error from the DataPumb that says that either the excel file is been used by another program or it can't be read.

The DTS was created on the SQL server with the administrator user logged so the owner will have sysadmin privilegies, the excel file is copied from another used as a template with a simple ActiveX Script that follows:

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

Function Main()

Dim oFSO
Dim sSourceFile
Dim sDestinationFile

Set oFSO = CreateObject("Scripting.FileSystemObject")

sSourceFile = "\\server\template.xls"
sDestinationFile = "\\server\report_x.xls"

oFSO.CopyFile sSourceFile, sDestinationFile

' Clean Up
Set oFSO = Nothing

Main = DTSTaskExecResult_Success

End Function

This script also gave trouble with a 'pemission denied' error, removing it led to the other error and I made sure the excel files existed for the Tramsform Object to work with.

The call to the DTS is from another server in the same domain using SQL authentication and the 'LoadfromSQL' function.

IF run locally on the server, the DTS works.

I think it has to do with security or the user/machine that calls the DTS from the asp.net page, however, I read the only related article I found about calling DTS from a job which lead me to create the DTS from the server with the administrator user logged on.

Let me know if you can help me out with this or if you require more information about the DTS.

Thank you in advance for any help.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-28 : 21:14:48

The only people who can execute DTS packages are the owner and sysadmin. If you made the owner the sysadmin or administrator, you need to redo the permissions and save it under the account you want to run it. You should be good to go then.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SK
Starting Member

4 Posts

Posted - 2004-03-28 : 23:06:37

Thanks, but in this case the DTS is to be started by an ASP.net webpage on an intranet. Should I create a new user and try to do a windows logon from the asp.net page to the DTS ? or there's another way ?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-29 : 16:32:01

You could use a service.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SK
Starting Member

4 Posts

Posted - 2004-03-29 : 17:52:04

Hi

After setting a group in the active directory with sysadmin role for the slq server and adding the users that will load the wepage the error changed to this:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. -System.Runtime.InteropServices.COMException (0x80040E4D): Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. at DTS.PackageClass.LoadFromSQLServer(String ServerName, String ServerUserName, String ServerPassword, DTSSQLServerStorageFlags Flags, String PackagePassword, String PackageGuid, String PackageVersionGuid, String PackageName, Object& pVarPersistStgOfHost)

the function 'loadfromsql' is set to 'use trusted connection' and the iis is set to basic authentication, the annonymous log on is disabled for the web application.. and of course, if run from the developer machine the dts runs.

it's definetely a problem with permission, just don't know where is the problem.

thanks

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-29 : 18:18:25

Take everything you just did and undo it. You DO NOT want your web users to have that kind of access. Call the function that calls DTS using SQL Server Authentication. Set up a user to just run the DTS package. Make that user the owner of the DTS package.

Make sure the DTS package can't do anything destructive and the user only has the permissions he needs.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SK
Starting Member

4 Posts

Posted - 2004-03-30 : 19:37:42

yes, it's a risk. Right no I'm thinking of taking another aproach since a DTS can only be run by it's owner or a sysadmin user. I have tried to impersonate a user, the group I mentioned above and it jsut doesn't work from asp.net page.

So, I'll try activating the DTS from a job on the same SQL Server.

Thanks for the help !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources