Using xp_cmdshell to copy files to a sql server on a different domain

Question

Post reply

Using xp_cmdshell to copy files to a sql server on a different domain

sunshine-587009

SSCrazy Eights

Points: 8981
More actions
July 28, 2011 at 11:26 am

#245129

Happy Thursday! 🙂
I wanted to know if there is a way to use sql server t-sql to copy files from one sql server domain to another. I've looked around and this is what I found but does not work for me.
exec master.dbo.xp_cmdshell 'net use q: COPY \\ProdSqlDomainServer\d$\MSSQL\Data\test.txt \\DevSQLDifferentDomainServer\MSSQL\data\ /user:DifferentDomainSErver\snoa /password:mypassword'
Any help is greatly appreciated!
¤ §unshine ¤

Viewing 15 posts - 1 through 14 (of 14 total)

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

xp_CmdShell is one way to do it from within T-SQL. Kicking off a SQL Agent job that contains a CmdExec step is another.

Is "from T-SQL" a requirement?

PowerShell is much better suited for file system operations like this. Resist the temptation to turn your SQL Server into a system scripting platform.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 2

I'm not familiar with PowerShell. Is there anywhere I can go to get instructions on how to do this via SQL Powershell?

¤ §unshine ¤

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

For the record, anything you can run from a CmdShell prompt you can run in a PowerShell session...so that can be your fallback. That said, there are better replacements for all the CmdShell commands. Take the old COPY program for example, it is replaced by the PowerShell Copy-Item CmdLet which offers all the functionality of COPY, plus more options for managing output, logging, errors, etc.

In your scenario I would:

1. Create a PowerShell script that can do the copy you want.

2. Create a new Credential in SQL Server using a Windows account that has the privilege necessary to do the copy.

3. Create new a SQL Agent Proxy of type "Operating Syatem (CmdExec)" mapped to your new Credential.

3. Create a SQL Agent Job with a Job Step of type CmdExec that uses the new Proxy Account to execute your PowerShell script.

Since you're on SQL Server 2005, if you have left things at their default levels and have not installed any SQL Server 2008 R2 components, you'll only have PS 1.0 installed on your servers and workstations. I recommend upgrading those to PS 2.0 from here: http://support.microsoft.com/kb/968929

On your workstation you can use the Windows PowerShell ISE that is bundled free within the PS 2.0 download above. It is a PS development app with an immediate window and an interactive debugger...very useful.

Windows PowerShell Getting Started Guide (Applies to PS 2.0)

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

Heh... and once you learn PowerShell, you can run that from xp_CmdShell in T-SQL. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

Jeff Moden (7/29/2011)
Heh... and once you learn PowerShell, you can run that from xp_CmdShell in T-SQL. 😉

This is me not taking the bait Jeff 😛

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

opc.three (7/29/2011)
Jeff Moden (7/29/2011)
Heh... and once you learn PowerShell, you can run that from xp_CmdShell in T-SQL. 😉
This is me not taking the bait Jeff 😛

😀 Not even if I say you could use OPENROWSET to run xp_CmdShell and build a table directly from the result set using SELECT/INTO? :-P;-)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

Jeff Moden (7/29/2011)
opc.three (7/29/2011)
Jeff Moden (7/29/2011)
Heh... and once you learn PowerShell, you can run that from xp_CmdShell in T-SQL. 😉
This is me not taking the bait Jeff 😛
😀 Not even if I say you could use OPENROWSET to run xp_CmdShell and build a table directly from the result set using SELECT/INTO? :-P;-)

No way, not even then! PowerShell has managed providers for backups, restores, SMO, Integration Services, and the list is gorwing. Why do you insist on using T-SQL to turn SQL Server into a sys admin scripting platform when there are better tools out there to do the job?

Now this is me agreeing to disagree with you on xp_CmdShell 😉

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 8

opc.three (7/29/2011)
Why do you insist on using T-SQL to turn SQL Server into a sys admin scripting platform when there are better tools out there to do the job?

Because it makes life real simple for me and it aggravates the hell out of just about everyone else. :-P:-D:hehe: Thanks for taking the "bait". 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

Aggravated, not so much, amused is more like it 😀

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 10

Heh... understood. Someday, we'll have to sit down, have a beer or two together, and I'll try to explain it all. It won't change your mind (nor do I intend to try) but you'll at least understand where I'm coming from. 🙂

Also, are you taking exception to running Powershell from SQL Server?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 11

Jeff Moden (7/30/2011)
Heh... understood. Someday, we'll have to sit down, have a beer or two together, and I'll try to explain it all. It won't change your mind (nor do I intend to try) but you'll at least understand where I'm coming from. 🙂

You never know...I try to keep an open mind. You may not get much farther than just inside the front door, but I'd listen to just about anything you had to say short of reading me a phone book.

Also, are you taking exception to running Powershell from SQL Server?

I would object to that as well. It's not only about choosing PowerShell over CmdShell for scripting in [almost] every conceivable case. I prefer to keep some separation of responsibility when it comes to these kinds of tasks. For me it's all about managing dependencies and preserving flexibility to interact with other services. Odd thing about this and most things in IT, specifically database, is it will depend on the person(s) running the show, the software environment, the org chart, the political climate even, lots of things. I could see your reasons for using xp_CmdShell being about the very same thing however, reducing dependencies 🙂

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 12

Isn't there something that needs to be done on the boxes themselves since they are different domains? Could there be a syntax to add the Server Name, User ID and password?

¤ §unshine ¤

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 13

sunshine-587009 (8/2/2011)
Isn't there something that needs to be done on the boxes themselves since they are different domains? Could there be a syntax to add the Server Name, User ID and password?

I will throw out a couple options, maybe others can comment or add more:

1. Run the PowerShell script as a Windows User that has permissions to do what is needed in both domains. Is there a Domain Trust in place? If so then one Domain User could be setup that can do all tasks. If a Trust is unavailable, is NTLM pass-through authentication an option?, i.e. can you setup a user in both domains with the same username and password (note: with this setup you'll have to manage each password separately to keep them in sync and Kerberos will not be possible, NTLM is less secure than Kerberos)?

2. Issue a NET USE command to map a drive letter as you have started to explore, supplying the username and password necessary to do the mapping, and use that drive letter to manage files on the file system on the server in the other domain.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 14

ok thanks! I'll try that out tomorrow or friday. 🙂

¤ §unshine ¤