2

My SSIS package uses Script Task which calls File.Exists() to determine if file is present.

I have to servers on same domain, e.g. THISDOMAIN, SERVER1 and SERVER2

I have user THISDOMAIN\ADMIN which is on both servers.

SERVER1 needs to access SERVER2 folder C:\ which is mapped to SERVER1 as D:\

On SERVER1 there is SQL Server 2008R2 which has SQL Server Agent with Account THISDOMAIN\ADMIN

If I log on to SSIS on SERVER1 with windows authentication as THISDOMAIN\ADMIN package executes successfully.

But if I execute the same package with SQL Server Agent, it does not see the network drive D:\ on 'SERVER1`

What I tried:

Community
  • 1
  • 1
skmasq
  • 4,470
  • 6
  • 42
  • 77
  • 2
    Mapped drives get flaky when it comes to service accounts. The mapping via Windows Explorer generally only works when it's not running as a service. Is there a reason you don't use UNC paths, which is recommended for situations such as this? – Taegost Feb 10 '14 at 19:56
  • Just to ensure I have it correct, thisdomain\admin is the account for SQL Agent. You have the credentials to log in as this user and when you do so, you see the drive mapped as D. When you execute your package through SQL Agent it does not see the D drive? – billinkc Feb 10 '14 at 19:56
  • @billinkc correct, but if I execute manually trough SQL Integrations Server it does see the drive – skmasq Feb 10 '14 at 19:58
  • @Taegost I will try this but I'm not sure this is the real issue, because manually executing this package it does find this drive. – skmasq Feb 10 '14 at 19:58
  • And no stored credentials are involved with this, correct? – billinkc Feb 10 '14 at 19:59
  • Out of curiosity, if you run this from the command line as the admin account, does it work? `runas /netonly /user:thisdomain\admin "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\dtexec.exe" /file "C:\path\MyPackage.dtsx"` – billinkc Feb 10 '14 at 20:03
  • @skmasq - When you say "manually executing", you mean after you log into the server as the user, you can execute it from the admin controls? – Taegost Feb 10 '14 at 20:06
  • @Taegost Thank you, `UNC` path worked... Really silly thing tbh. – skmasq Feb 10 '14 at 20:45
  • @skmasq It is, it's kind of an abstract concept to wrap your head around. I saw it once before at a former job, when we switched to headless services from standard processes, and things stopped working. Since then, I always use a UNC path – Taegost Feb 10 '14 at 20:59

1 Answers1

3

When going across the network to access files/folders, it's always recommended to use the UNC path.

Especially in a situation like this where the drive may be mapped via Windows Explorer and works when you're logged in, but doesn't work when you're trying to access that same path from a service that isn't logged in.

Taegost
  • 1,208
  • 1
  • 17
  • 26