Scheduling an Import

The only way I seem to be able to backup my shared online SQL databases to a local location is to import the data to local SQL copies. I can do this through the Import/Export Wizard and all works just fine. The problem comes when I try to use the local SQL Server Agent to schedule a job. If I manually run the dtsx packages all imports nicely. When I create a job that runs the package it always fails. The failure seems to be because no password is transmitted to successfully connect to the online database. I have a dozen shared online databases I need to back up on a regular basis. When I used Enterprise Manager on the older SQLs I never had this problem. Any and all help appreciated.
spiderbuzzAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi,

Are you using SQL Server Management Studio (SSMS)?

Can you open Object explorer (you may need to connect to a database first) and backup the database from there?

Cheers
  David
0
spiderbuzzAuthor Commented:
Yes I am using SQL Server Management Studio. I cannot backup a  shared SQL to my location though. I can only backup to the location of the shared online SQL.
0
Anthony PerkinsCommented:
>>The problem comes when I try to use the local SQL Server Agent to schedule a job.<<
Then it has to be a permissions problem.  More than likely the SQL Server Agent service start up account is not a domain account, but rather a Local account and therefore as no access to remote devices on the network.  Failing that the domain account does not have the appropriate permissions.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

spiderbuzzAuthor Commented:
The SQL Server Agent service start up account is a local account. It is trying to access a remote server on a different network in a different physical location. So, do I have to create a domain account on the local server that mimics the login for the remote server? Is there any way I can just supply the dtsx package the login so it can be scheuled as a job?
0
David ToddSenior DBACommented:
Hi,

Is SQL Server running in a domain account or local system?

If in a domain account, use that account for SQL Agent (for now if you're paranoid about services sharing accounts ... )

Cheers
  David
0
Anthony PerkinsCommented:
>>So, do I have to create a domain account on the local server that mimics the login for the remote server?<<
Yes.
0
Anthony PerkinsCommented:
Let me re-phrase that.  You cannot use a Local System abbout to access remote resources.  So you need to give the SQL Server Agent service startup account a domain account that has access to those reosources.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
spiderbuzzAuthor Commented:
Thank you all!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Storage Software

From novice to tech pro — start learning today.