Remote Network drive - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Remote Network drive

Author

Topic

ZachM
Starting Member

28 Posts

Posted - 2008-04-24 : 16:27:53

I have an SSIS package which accesses a map UNC path drive. It works fine when I run SSIS from Visual Studio. However, when I run this from a SQL Server job, it gives an error.

I am not allowed to use xp_cmdshell due to security reasons. If someone can point me to right direction, that would be greatly appricated.

Thanks

Z

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-24 : 16:42:59

Is the drive mapped on the database server?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-04-24 : 16:47:53

Does the account that runs the SQL Agent has access to that share you're trying to hit?

---
http://www.ssisdude.blogspot.com/

ZachM
Starting Member

28 Posts

Posted - 2008-04-24 : 16:49:09

Yes, I mapped a drive from the data server (source) to destination server. I am able to transfer file from source (data server) to destination (web server) using ssis package. However, when I execute the sql job, the job fails. What do I need to do in order for my sql job to work?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-24 : 16:52:46

What account is running the SQL Server services? If it's local system account, then that's the problem.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

ZachM
Starting Member

28 Posts

Posted - 2008-04-24 : 16:57:55

I am the admin on the server. I have no problem migrating data from one sql serve (source) to anothe (destination). Is just when I map a remote dirve, my sql job fails. FYI--I am also able to copy the file to remote drive using window explorer.

ZachM
Starting Member

28 Posts

Posted - 2008-04-24 : 16:58:43

Sql agent account running the job

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-24 : 17:03:53

But what account is running the services?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

ZachM
Starting Member

28 Posts

Posted - 2008-04-24 : 17:07:03

This particular package runs fine manually but NOT as job.
This package only has file tasks which copies file from a remote location to local disk. I have double-checked permissions. The account which has permission on the remote location is the same account under which SQL Server, and SQL Agent are running.

The source has the same account permission as the destination server.

I am log into the server as an admin.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-24 : 17:12:51

Log into the server with the same account that the services are running. Manually run the package, meaning not from a job. Post the error here.

Do not test with your own account. This must be tested with the account that the services are using.

You should also check for the mapped network drive while logged into the server as the service account. Drive mappings do not follow to other logged in accounts unless it's in their login script.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

ZachM
Starting Member

28 Posts

Posted - 2008-04-24 : 17:19:44

I am sorry, but I am little lost. When you say services, what services are you referring to? I have one sql job, that executes a ssis package.

If I run the ssis package manually, I do not get an errors. I think in sql server, the sql job is not able to handle map drive to remote server. And I am not sure how to go about solving that issue.

I hope I am explaining my self in a clear manner.

Thanks for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-24 : 17:23:45

Go to Administrative Tools and then Services. We need to know what account the SQL Server services are using.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

ZachM
Starting Member

28 Posts

Posted - 2008-04-24 : 17:33:42

ok, there is sql server(sql2005), sql serve agent, sql server analysis services. Is there a specific one I need to look at?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-24 : 17:37:21

The agent

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-24 : 19:36:54

Enable Xp_cmdshell in SQL surface area config. Looks like you have security issues with package. What protection level you use while deploying package to jobs? post error.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-24 : 21:43:12

Have to start sql agent with domain account that has permission to access related remote files, xp_cmdshell is not required.

ZachM
Starting Member

28 Posts

Posted - 2008-04-25 : 13:54:55

This is the error I get when I execute the ssis package from sql server job:

Message
Executed as user: PQBSNET\svc-sqlserver-backup. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:49:02 PM Error: 2008-04-25 13:50:32.68 Code: 0xC002F304 Source: File System Copy DB File System Task Description: An error occurred with the following error message: "Could not find a part of the path 'Y:\JCBBackup.bak'.". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:49:02 PM Finished: 1:50:32 PM Elapsed: 90.547 seconds. The package execution failed. The step failed.

Y is mapped to C:\temp on a remote server. The file JCBBackUp.bak is on the remote server.

The ProtectionLevel is EncryptSensitiveWithUserKey in the ssis package.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-25 : 14:01:05

Is Y mapped in the account that the agent service is using? Mapped network drives do not follow to other accounts. And it doesn't matter who is logged into the console. What matters is if Y is mapped to the same account that the agent service is using.

From the error message, you need to check PQBSNET\svc-sqlserver-backup. Log into the console of the database server with that account. Check Windows Explorer for the Y drive.

Let us know what you find.

And to avoid this issue altogether, just use a UNC path instead of a mapped network drive.

So if you are referring to Y:\SomeFolder\SomeFile.txt, then you can use \\remoteservername\sharename\SomeFile.txt instead. The key is to either get a share setup or use the admin share which would be c$ in your case. Walk the path after the share.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

ZachM
Starting Member

28 Posts

Posted - 2008-04-25 : 14:06:35

Y is not mapped in the account that the agent service is using. I am using UNC.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-25 : 14:12:07

I'm so confused then. Please describe in better detail what the package is doing. Show us the drives/paths that it is using.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-25 : 22:13:21

>> I am using UNC

But y:\JCBBackup.bak is not unc. Anyway, sql agent service account needs permission on that location.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources