• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

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.
0
spiderbuzz
Asked:
spiderbuzz
  • 3
  • 3
  • 2
1 Solution
 
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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
 
spiderbuzzAuthor Commented:
Thank you all!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now