Wednesday, May 16, 2012

SSIS: Access Network Resources on a Server Not on the Same Domain

I recently ran into a situation where my SSIS server needed access to file shares that were not on the domain but were accessible over the network via UNC path.

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.



20 comments:

  1. 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?

    ReplyDelete
  2. Hi, 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?

    ReplyDelete
    Replies
    1. Even 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.

      Delete
  3. Still a great post, even in 2014 :)

    I 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

    ReplyDelete
  4. Thank you David. I've been fighting with this same issue for two days now. Your solution worked for me.
    Dan Tuma

    ReplyDelete
  5. worked like a charm for me...thanks

    ReplyDelete
  6. thanks, quite a few places where note is not... delete mapping example using Y though we mapped Z, be clearer if they were the same

    ReplyDelete
  7. This was very useful and worked for me.. Thank you David..

    ReplyDelete
  8. Hi, 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.

    ReplyDelete
    Replies
    1. Hi did you find a solution for this?

      Delete
    2. Try the /y switch:
      /c "NET USE Y: /delete /y

      Delete
  9. Thank You very much. Great Post.

    ReplyDelete
  10. Won't storing credentials as plain text in the SSIS file be an issue?

    ReplyDelete
    Replies
    1. Store it as a "sensitive" project param and it won't be plain text.

      Delete
    2. Thanks David. This is really useful.

      Delete
    3. How would you then pass the parameter back to the arguments in the Execute Process Task? As sensitive parameters are not supported in property expressions.

      Delete
    4. Good 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.

      Delete
    5. C# supports sensitive parameters. This will work, where JG01Password is the sensitive parameter:

      string 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;

      Delete
    6. Nice! Thanks for posting that.

      Delete
  11. Hard 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