Typically I would create a domain-based service account and grant it access to any network resources required by the SSIS process. In this case the SSIS server needed to access files on servers on two other domains and these domains did not have a trust between them and the SSIS domain.
The solution was to dynamically create and delete the mapped drives as needed using Execute Process tasks and the NET USE command. Below is an example of this command.
NET USE Z: "\\someserver\someshare" /user:somedomain\someusername somepassword
Not the user parameter. This is where you can pass the credentials from the domain on which the share resides. If, for some reason, you want to use a mapped drive on the same domain (instead of a UNC path) you can exclude the username and password (assuming that the user under which your process is running already has access to the share).
We need to modify this a little bit to use in an Execute Process task. See below.
Note that the Executable property is set to cmd.exe. This is how we tell the task to run this command from a command prompt. Also not the /c switch. This tells the task not to display the command prompt window and to complete execution without any user intervention. If you wanted to debug the task you could replace the /c switch with /k which would display the command prompt window at the time of execution.
After this task runs our process now has the ability to use the newly mapped drive. This is where our Connection Manager comes into play. This could be any Connection Manager that requires network access such as FLATFILE, EXCEL, OLEDB Jet 4.0 for Access, etc. All we have to do is set the Connection Manager's ConnectionString property to a path using our mapped drive.
The key here is to set DelayValidation to True. If we don't do this then the package will fail validation each time because the mapped drive has not been created when SSIS attempts to validate the package. The drive is only mapped after validation is complete and the Execute Process task has run.
You may be thinking, why would validation fail each time? Isn't the mapped drive still there from the last time we ran the package? The answer is no. It's no because of the last step which is to delete the mapped drive once we are done using it. The command for deleting this drive would be the following.
NET USE Y: /delete
All we need here is the drive letter and the delete switch. Below is a screen shot of the Execute Process task.
That's all you need to access file shares with SSIS using dynamically created mapped drives. UNC paths are typically a more reliable method and what I consider to be a best practice when accessing a file system over a network. However, you might find yourself in a situation similar to mine where a mapped drive is necessary to pass credentials, other than the ones being used to run your process, to access a file system resource.
Excellent post, thank you so much! I really hope someone reads this because I am hoping for some assistance. I was able to get this to work, but now for some reason it's not anymore. The command prompt pops up, but it doesn't seem to be passing my argument. When I copy and paste the argument into the command prompt manually, it works like a charm. Any ideas how to fix/troubleshoot this?
ReplyDeleteHi, thanks for this post. In my case, my unc lacation is Already mapped (no need to map during runtime) ,I just need to set username and passwoed to set in configuration file.how can I do this?
ReplyDeleteEven if it's already mapped you still need to map at runtime unless your process is running under the same user profile. Even then I think there can be some issues. I initially went down the route you're suggesting but found it was not possible.
DeleteStill a great post, even in 2014 :)
ReplyDeleteI was trying this on a Windows Server 2012 machine, but I could not make it work. It seems that I had to take the following steps:
1. Start -> run -> secpol.msc
2. Local Policies -> Security Options -> Network Security: LAN Manager Authentication level
3. Change to Send LM & NTLM - use NTLMv2 session security if negociated
I also put this on my blog: http://bytes-and-cats.blogspot.ro/2014/07/access-network-share-outside-of-domain.html
Thank you David. I've been fighting with this same issue for two days now. Your solution worked for me.
ReplyDeleteDan Tuma
worked like a charm for me...thanks
ReplyDeletethanks, quite a few places where note is not... delete mapping example using Y though we mapped Z, be clearer if they were the same
ReplyDeleteThis was very useful and worked for me.. Thank you David..
ReplyDeleteHi, Thanks for the solution. But while executing delete statement "NET USE Y: /delete" command prompt pops up asking Yes or no how to pass parameter directly in the command.
ReplyDeleteHi did you find a solution for this?
DeleteTry the /y switch:
Delete/c "NET USE Y: /delete /y
Thank You very much. Great Post.
ReplyDeleteWon't storing credentials as plain text in the SSIS file be an issue?
ReplyDeleteStore it as a "sensitive" project param and it won't be plain text.
DeleteThanks David. This is really useful.
DeleteHow would you then pass the parameter back to the arguments in the Execute Process Task? As sensitive parameters are not supported in property expressions.
DeleteGood question. Unfortunately I don't have an answer for you. In general, this is not a very secure practice because, as far as I know, the credentials will be sent over the network in plain text anyway. The best I can suggest is to secure the parameter values in the SSISDB catalog by only allowing the appropriate people to access that database.
DeleteC# supports sensitive parameters. This will work, where JG01Password is the sensitive parameter:
Deletestring strPassword = Dts.Variables["$Package::JG01Password"].GetSensitiveValue().ToString();
string strCmdText;
strCmdText = "/c NET USE Y: \"\\\\192.168.2.26\\group\\Shared\\temp\" /user:justgiving01\\yourusername " + strPassword;
System.Diagnostics.Process.Start("CMD.exe", strCmdText);
Dts.TaskResult = (int)ScriptResults.Success;
Nice! Thanks for posting that.
DeleteHard to believe that SSIS still can't do something as simple as copy a file to a network path in 2020. I'm going to stop using SSIS altogether, since it's pretty much rebranded Azure crap data mover anyway. PowerShell makes SSIS look rather stupid these days.
ReplyDelete