Run As Command

I need to copy a backup file from server to server in seperate domains.
Unfortunately in my company they do not have trusted connections. So I need
to use a seperate logon for the destination server. I have tried a mapped
drive that is mapped as a valid logon in thatdomain with no luck, I get
"cannot find the drive specified." Is there a way thorugh sql server to run
a
job or a copy as something different than the SQL Agent account. FYI, the
logon that I use to logon to the other domain cannot be registered as a logo
n
to SQL server bacause SQL Server cannot see that domain. Any suggestions?Not sure of all the details on the mapped drive - you had no
luck where? Can you create a mapped drive and access it from
your domain with a particular login and password?
If you have that, you could execute net use to map a drive
and specify a domain\user and password. Then copy the file
to that mapped drive. Then delete the mapped drive when
done.
-Sue
On Mon, 21 May 2007 09:11:00 -0700, Rick
<Rick@.discussions.microsoft.com> wrote:

>I need to copy a backup file from server to server in seperate domains.
>Unfortunately in my company they do not have trusted connections. So I need
>to use a seperate logon for the destination server. I have tried a mapped
>drive that is mapped as a valid logon in thatdomain with no luck, I get
>"cannot find the drive specified." Is there a way thorugh sql server to run
a
>job or a copy as something different than the SQL Agent account. FYI, the
>logon that I use to logon to the other domain cannot be registered as a log
on
>to SQL server bacause SQL Server cannot see that domain. Any suggestions?|||Rick
here is how you can do it
SELECT @.VCHCOMMAND = 'net use v: \\10.1.97.150\sqlbackups$\SRDEV dbbackup
/user:XXXX\srdev_db_backup'
EXEC @.IRESULT = MASTER..XP_CMDSHELL @.VCHCOMMAND, NO_OUTPUT
where v: is the share name
dbbackup : password to connect \\10.1.97.150\sqlbackups$\SRDEV
XXXX\srdev_db_backup : is the user login on the destination server
if you are using BACKUP DATABASE command to backup the database then
issue the above command first and then backup up the database on to v:
hope this helps you
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:042AEE4F-6267-4EA8-8BB6-4609B32409DB@.microsoft.com...
>I need to copy a backup file from server to server in seperate domains.
> Unfortunately in my company they do not have trusted connections. So I
> need
> to use a seperate logon for the destination server. I have tried a mapped
> drive that is mapped as a valid logon in thatdomain with no luck, I get
> "cannot find the drive specified." Is there a way thorugh sql server to
> run a
> job or a copy as something different than the SQL Agent account. FYI, the
> logon that I use to logon to the other domain cannot be registered as a
> logon
> to SQL server bacause SQL Server cannot see that domain. Any suggestions?|||This is what I came up with after a lot of trial and error
Declare @.VCHCOMMAND Char (150),
@.CMD Char (50),
@.COPY Char (50)
Set @.VCHCOMMAND = 'net use L: *\\172.9.17.99\Backup peoples123
/user:sigueqa\rpeoples'
Set @.COPY = 'copy \\server2\Backup\DB\SIRETGUE.Bak L:\DB'
Set @.CMD = @.VCHCOMMAND + ' Y ' + @.COPY
EXEC MASTER..XP_CMDSHELL @.CMD
, but I get the following error:
L: has a remembered connection to \\172.17.9.67\backup. Do you
want to overwrite the remembered connection? (Y/N) [Y]:
No valid response was provided.
NULL
Any Suggestions?
"vt" wrote:

> Rick
> here is how you can do it
> SELECT @.VCHCOMMAND = 'net use v: \\10.1.97.150\sqlbackups$\SRDEV dbbackup
> /user:XXXX\srdev_db_backup'
> EXEC @.IRESULT = MASTER..XP_CMDSHELL @.VCHCOMMAND, NO_OUTPUT
> where v: is the share name
> dbbackup : password to connect \\10.1.97.150\sqlbackups$\SRDEV
> XXXX\srdev_db_backup : is the user login on the destination server
> if you are using BACKUP DATABASE command to backup the database then
> issue the above command first and then backup up the database on to v:
> hope this helps you
>
>
> Regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
>
> "Rick" <Rick@.discussions.microsoft.com> wrote in message
> news:042AEE4F-6267-4EA8-8BB6-4609B32409DB@.microsoft.com...
>
>|||> L: has a remembered connection to \\172.17.9.67\backup. Do you
> want to overwrite the remembered connection? (Y/N) [Y]:
> No valid response was provided.
> NULL
> Any Suggestions?
First, remove the persistent connection:
EXEC master..xp_cmdshell 'net use L: /delete'
Then try:
--map drive
EXEC master..xp_cmdshell 'net use L: \\172.9.17.99\Backup peoples123
/user:sigueqa\rpeoples /persistent:no'
--copy
EXEC master..xp_cmdshell 'copy \\server2\Backup\DB\SIRETGUE.Bak L:\DB'
--unmap drive
EXEC master..xp_cmdshell 'net use L: /delete'
I might be wrong but I think you could also do without the drive letter:
--establish security credentials
EXEC master..xp_cmdshell 'net use \\172.9.17.99\Backup peoples123
/user:sigueqa\rpeoples /persistent:no'
--copy
EXEC master..xp_cmdshell 'copy \\server2\Backup\DB\SIRETGUE.Bak
\\172.9.17.99\Backup\DB'
--unmap
EXEC master..xp_cmdshell 'net use \\172.9.17.99\Backup /delete'
Hope this helps.
Dan Guzman
SQL Server MVP
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:6D7669E9-390B-46D4-8EAB-F03196899C3B@.microsoft.com...[vbcol=seagreen]
> This is what I came up with after a lot of trial and error
> Declare @.VCHCOMMAND Char (150),
> @.CMD Char (50),
> @.COPY Char (50)
> Set @.VCHCOMMAND = 'net use L: *\\172.9.17.99\Backup peoples123
> /user:sigueqa\rpeoples'
> Set @.COPY = 'copy \\server2\Backup\DB\SIRETGUE.Bak L:\DB'
> Set @.CMD = @.VCHCOMMAND + ' Y ' + @.COPY
> EXEC MASTER..XP_CMDSHELL @.CMD
>
> , but I get the following error:
> L: has a remembered connection to \\172.17.9.67\backup. Do you
> want to overwrite the remembered connection? (Y/N) [Y]:
> No valid response was provided.
> NULL
> Any Suggestions?
> "vt" wrote:
>