spiderbuzz
asked on
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.
ASKER
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.
>>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.
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.
ASKER
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?
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
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
>>So, do I have to create a domain account on the local server that mimics the login for the remote server?<<
Yes.
Yes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you all!
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