Solved

SSIS Package Execution Error

Posted on 2014-01-12
8
406 Views
Last Modified: 2016-02-10
I have a SSIS package that copies a file from one server to another.  The source server is on a workgroup, and the target server is on a domain (if it matters).    The package executes normally in debug mode, but after I deploy the package to sql server, it fails when trying to access the file on the source server.  I assume this is due to a different execution context when running on the server.

I have a mapped drive to the source server, which I am using as the source for the copy function.  The directory where the file exists has read/write for everybody.  I don't understand why access to this file is being blocked.

Since the user that runs the process (SQL Agent) doesn't exist on workgroup source server, I can't add it to security.

Sql Server 2012 R2.
0
Comment
Question by:No1Coder
  • 4
  • 4
8 Comments
 
LVL 18

Expert Comment

by:Matthew Kelly
Comment Utility
What is the error that gets added to the SQL Agent history for the error? Also just to check, have you verified you can access the server drives when you are logged into the SQL server? I am assuming based on your question you are running debug on your machine and then deploying it to a different machine (the sql server)?

For example, if your script is using lettered drives instead of full path the letter drive may not exist. Follow on questions would be, is the SQL Agent running as a custom user or as NETWORK SERVICE?
0
 

Author Comment

by:No1Coder
Comment Utility
I am using a lettered drive.  When debugging, I am running on the same server (where SQL Server runs).  

I changed the script to use \\ServerName\ShareName\path\file instead of lettered drive.

This works in debug mode, but fails in production mode.  Gets error "User Name or Password is incorrect".  The share in question has Everybody - full control.

I am not sure it this rund under sql server agen, or Integration Services.  For integration services, the user is NT Service\MsDtsServer110.
0
 
LVL 18

Expert Comment

by:Matthew Kelly
Comment Utility
If the SSIS package is running as a job in a scheduled Agent job step it runs as the agent user. Local accounts won't be able to access cross domain resources and it appears your account has access (i.e. why it works in debug).

So try changing the Agent to run as your account and test to see if the deployed package works (or what error it gives then). If that works, create a new domain account and use it as the agent service account. At that point there are other security items you will want to do (such as set the account in domain policy for service logon only), but at this point try your account for testing.

Integration services also has the ability to use "Proxy Accounts" if you needed to only do it for this single job, but usually just running every job as a particular service account is fine for most servers.
0
 

Author Comment

by:No1Coder
Comment Utility
I used SQL Configuration tool to change both SQL Server agent and SQL Server integration Services to use my domain account.  The agent restarted.  When I run it, gets same error "User name or password error".

Another job that doesn't access the other server runs fine using my credentials.

How do you setup proxy accounts?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 18

Expert Comment

by:Matthew Kelly
Comment Utility
0
 
LVL 18

Expert Comment

by:Matthew Kelly
Comment Utility
Although, the proxy just allows you to set different credentials for just a job step; so changing it to your credentials should do the same thing basically. Note they also needed to make sure the user existed on the workgroup server with the same name/password combination, but that doesn't explain why it works in debug for you...

You may also want to look in the Windows event logs to see if there is any additional information.
0
 

Accepted Solution

by:
No1Coder earned 0 total points
Comment Utility
I got this to work by creating a local account of the source (non-domain) server with credentials that match the domain credentials that the agent is running on.
0
 

Author Closing Comment

by:No1Coder
Comment Utility
fixed
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now