DTS Security

Question

DTS Security

Luke L

SSC-Dedicated

Points: 35547
More actions
August 10, 2006 at 5:54 am

#173668

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]
August 14, 2006 at 8:00 am

This was removed by the editor as SPAM

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 2

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]

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

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

Shawn Wilson Ten Centuries Points: 1271 More actions · Answer 4

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.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 5

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]

Nita Reddy SSCarpal Tunnel Points: 4182 More actions · Answer 6

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 7

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

dragos_sv Default port Points: 1478 More actions · Answer 8

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