Set up connection to remote server on another domain

Question

Set up connection to remote server on another domain

christina-518819

SSC Enthusiast

Points: 121
More actions
November 8, 2011 at 5:13 am

#250524

This may be either really easy or impossible!
I'm trying to loop over a set of directories in a share on a remote server using a different login. I need to connect to the remote machine with a username and password and to have the connection persist for the duration of the package runtime. I was looking at using a script task to set it up but have no idea what the right code to use is or whether that connection would close once the script task is complete. It would have to be written in VB .net.
Anyone able to help?

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

I'm not an SSIS guy, but i do know that the you can map a network drive with the NET USE command allows you to specidy a domain users and password for the security descriptor;

maybe not a script task but a command task?

NET USE Z: \\UNCPath\d$ /user:mydomain\lowell NotMyRealPassword

--do stuff and then later

NET USE Z: /DELETE

I've done this with xp_cmdshell to map a network drive .

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

christina-518819 SSC Enthusiast Points: 121 More actions · Answer 2

Thanks for your reply. I wanted to use a mapped drive but I've been given orders from on high that I'm not allowed to map any drives to the servers. Do you know if you can use the net use command to set up the connection without mapping it and if that command can be executed within ssis?

If not, I suppose it could be set as a step in an agent job to acquire the connection before running and to close after execution, although there could be security issues with that.

Lowell SSC Guru Points: 323518 More actions · Answer 3

christina-518819 (11/8/2011)
Thanks for your reply. I wanted to use a mapped drive but I've been given orders from on high that I'm not allowed to map any drives to the servers. Do you know if you can use the net use command to set up the connection without mapping it and if that command can be executed within ssis?
If not, I suppose it could be set as a step in an agent job to acquire the connection before running and to close after execution, although there could be security issues with that.

lol...you are allowed to access UNC paths but not use the map drive tool to make your life easier?

too often i've seen conflicting biz rules like that.

i have not done this, but googling "UNC path impersonation" seems to point to what you are after:

http://social.msdn.microsoft.com/Forums/en/vbgeneral/thread/57abd6a6-9601-44e1-8f2a-35b96dc8ffd7

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

You should be able to do everything with UNCs. You haven't said what operations you'll be carrying out on the folders, so I'm just guessing, but if you keep a table with the names of the folders, you can loop through the folders, you can carry out the first set of operations on \\MyServer\MyShare\Folder1, the second on \\MyServer\MyShare\Folder2, and so on.

John

christina-518819 SSC Enthusiast Points: 121 More actions · Answer 5

I know, I said it was stupid... I'm not even sure why this job landed in my lap!

I'll give that a go and post back the outcome. Thanks for your help.

christina-518819 SSC Enthusiast Points: 121 More actions · Answer 6

John Mitchell-245523 (11/8/2011)
You should be able to do everything with UNCs. You haven't said what operations you'll be carrying out on the folders, so I'm just guessing, but if you keep a table with the names of the folders, you can loop through the folders, you can carry out the first set of operations on \\MyServer\MyShare\Folder1, the second on \\MyServer\MyShare\Folder2, and so on.
John

Hi John,

I need to copy files matching specific naming conventions to an area on my local server and then move the original to an archive folder within the same folder. Ordinarily, that's easy but I'll be running this package through the sql agent and need to log on to the remote share with a domain account.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

I'm afraid I don't understand what the difficulty is. Just give the SQL Server Agent service account the necessary permissions on the share and everything should work.

John

christina-518819 SSC Enthusiast Points: 121 More actions · Answer 8

John Mitchell-245523 (11/8/2011)
I'm afraid I don't understand what the difficulty is. Just give the SQL Server Agent service account the necessary permissions on the share and everything should work.
John

Hi John,

All our sql server environments are on different domains to the remote server the files are located on. I didn't think you could grant access on a share to an account on a different domain. I'm also fairly sure at least one of those servers is still using Local System as the agent account despite repeated requests for it to be changed.

christina-518819 SSC Enthusiast Points: 121 More actions · Answer 9

Lowell (11/8/2011)
I'm not an SSIS guy, but i do know that the you can map a network drive with the NET USE command allows you to specidy a domain users and password for the security descriptor;
maybe not a script task but a command task?
NET USE Z: \\UNCPath\d$ /user:mydomain\lowell NotMyRealPassword
--do stuff and then later
NET USE Z: /DELETE
I've done this with xp_cmdshell to map a network drive .

Yeah, this did the trick. I used it in an Execute process task calling cmd with the "net use \\servername\share" plus authentication details command in the argument and then another execute process task to dispose of the connection when the files are copied. Thanks for your help