DTS Security

  • WHEN I execute the following a stored procedure from QA which contains...

    xp_cmdshell 'DTSRun /S "MyServer" /N "MyPackage" /G "{8679.....998uw4}" /W "0" /E'

    and my transform task exports data to a text file, what secuirty is the DTS Job trying to connect to my text file with?

    I'm guessing it's not mine even though I connected to QA with Windows Auth, and I specified DTSRUN /E to use a trusted connection.  This is because I receive the following error even though I have Full rights to the file, folder and share it resides in.

    Error:  5 (5); Provider Error:  5 (5)

       Error string:  Error opening datafile: Access is denied.

     

       Error source:  Microsoft Data Transformation Services Flat File Rowset Provider

       Help file:  DTSFFile.hlp

       Help context:  0

    Yes, I have write permissions to the folder, yes I have the rights to run the DTS job, yes I have the rights to call xp_cmdshell The first part of my package actually runs, it only errors when I try to copy data into the text file.  And yes it runs properly when I run it from the Package Designer.

    I imagine that since I’m using xp_cmdshell to call the package that it tries to make the connection with either the SQL Service rights or SQL Agent rights, both of which are set to local system, which by the way I can’t change… 

    Anyone have any Bright Ideas on how I can overcome this?

     

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • This was removed by the editor as SPAM

  • Luke,

    Is the output text file on the same server as the DTS package?  When you execute the package in DTS Designer, are you on the server where the package resides or on your workstation?  How about when you execute  it from QA?

    Greg

    Greg

  • Thanks for your reply.

    The text file resides on a network share not on the same server as the SQL server which my user account has admin rights to both on the share and file security. 

    When I execute the package I'm doing this from my machine both from DTS Designer and from QA. 

    I know that in DTS designer that it's executing with my Network permissions.  I was under the impression that when I executed XP_cmdshell and used the /E para that whatever I ran from the cmdshell would run with my permissions as well. 

    That's why I'm so perplexed.

     

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • Nope.  DTS is trying to access the text file as the login that runs SQL Server Service.  Here's the excerpt from BOL:

    When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account.

     

    Greg

    Greg

  • sysinternals has a tool called psexec which will allow you exec a process on another machine with alternate credentials.

    This worked for me but it's quite complicated.

    I created the dts package on the dbserver.

    Then created a batch file on a remote computer with the sqlserver client tools which had the dtsrun cmd tool.  In this batch file was the dtsrun command with the cmdline args.

    Then on the dbserver, I xp_cmshell 'psexec \\myremotecomputer -u myuserid -p mypwd mydtsbatchfile.bat

    You can put this in an encrypted stored proc if you want.

  • Thanks, Greg and Shawn.  Greg, I kind of figured as much, but I wasn't able to come up with anything that actually stated that.  Thanks a bunch. 

    I suppose I'll either put it on the local machine and then either FTP it or copy it out with a batch file where I can actually send credentials.

     

    Thanks again.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • Hi Luke,

     

    I am getting this similar stituation, How can I get the DTS I want to run. I want to change DTS pacakage in SQL agent with the new one how to do this.

    DTSRun /~Z0x839BDE3E93E78159ECDB35999C61B3B1EC42415EA69649E0C31DDE7D77CB96440935233C409E47EF6231674D2ED4BA5E97E46D334B068D185744A8405DC6810321B27847789AB48FE839C855CEC6CC85AD4B861F4D6DB3E16A4BAE81C3F3B1F464D48D

    How to get the above DTS ID?

    Nita

  • Nita,

    There's no way to decypher the encrypted DTSRun command.  If you know which package you want to execute, follow

    the instructions for generating a DTSRun command with dtsrunui given in this thread:

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=337322

    Greg

     

     

    Greg

  • Nita Reddy (1/17/2007)


    Hi Luke,

    I am getting this similar stituation, How can I get the DTS I want to run. I want to change DTS pacakage in SQL agent with the new one how to do this.

    DTSRun /~Z0x839BDE3E93E78159ECDB35999C61B3B1EC42415EA69649E0C31DDE7D77CB96440935233C409E47EF6231674D2ED4BA5E97E46D334B068D185744A8405DC6810321B27847789AB48FE839C855CEC6CC85AD4B861F4D6DB3E16A4BAE81C3F3B1F464D48D

    How to get the above DTS ID?

    Nita

    have you tried running DTSRun /~Z0x839BDE3E93E78159ECDB35999C61B3B1EC42415EA69649E0C31DDE7D77CB96440935233C409E47EF6231674D2ED4BA5E97E46D334B068D185744A8405DC6810321B27847789AB48FE839C855CEC6CC85AD4B861F4D6DB3E16A4BAE81C3F3B1F464D48D /!X /!C ?

    the /!X /!C should copy to clipboard the unencrypted cmd line

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply