|
Home > Archive > MS SQL Server DTS > May 2005 > DTS package for remote internet SQL Server
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
DTS package for remote internet SQL Server
|
|
| karl.rhodes@keywave.com 2005-05-20, 7:23 am |
| I have a problem with a DTS package which needs to connect to a remote,
ISP hosted SQL Server and backup the live data to backup databases on
our local server.
I have created the package on our SQL Server using Enterprise Manager
on my workstation and when I run it it works fine. When I schedule the
package it fails whith a 'SQL Server cannot be found or access is
denied' error.
I have read that the SQL Server Agent service needs pemissions on a
local domain account which can access the server the package runs on.
This is the case as the package sits on out local server and pulls data
from the internet, but this account does not have permissions on the
ISP hosted SQL Server. Could this be why the package is failing?
If this is the case, how do I get the 'backups' to run at a scheduled
time as the ISP is not likely to give me permissions to the SQL Server
Agent details.
Windows 2000 Server
SQL Server 2000 Ent SP3
| |
| Helge C. Rutz 2005-05-20, 7:23 am |
| Hi Karl,
<karl.rhodes@keywave.com> wrote
>I have a problem with a DTS package which needs to connect to a remote,
> ISP hosted SQL Server and backup the live data to backup databases on
> our local server.
>
> I have created the package on our SQL Server using Enterprise Manager
> on my workstation and when I run it it works fine. When I schedule the
> package it fails whith a 'SQL Server cannot be found or access is
> denied' error.
>
> I have read that the SQL Server Agent service needs pemissions on a
> local domain account which can access the server the package runs on.
> This is the case as the package sits on out local server and pulls data
> from the internet, but this account does not have permissions on the
> ISP hosted SQL Server. Could this be why the package is failing?
do you use a connection inside the package to address the ISP Server?
A common problem is, that the sql server doesn't run on the default port,
so that you have to create an alias with the client network utility.
If you did this on your workstation but not on the server, the same package
must fail on the server, not knowing which port to contact.
Helge
| |
| Allan Mitchell 2005-05-20, 1:23 pm |
| Could very well be. In addition to Helge's answer have a look here
http://support.microsoft.com/?kbid=269074
"karl.rhodes@keywave.com" <karl.rhodes@keywave.com> wrote in message
news:karl.rhodes@keywave.com:
> I have a problem with a DTS package which needs to connect to a remote,
> ISP hosted SQL Server and backup the live data to backup databases on
> our local server.
>
> I have created the package on our SQL Server using Enterprise Manager
> on my workstation and when I run it it works fine. When I schedule the
> package it fails whith a 'SQL Server cannot be found or access is
> denied' error.
>
> I have read that the SQL Server Agent service needs pemissions on a
> local domain account which can access the server the package runs on.
> This is the case as the package sits on out local server and pulls data
> from the internet, but this account does not have permissions on the
> ISP hosted SQL Server. Could this be why the package is failing?
>
> If this is the case, how do I get the 'backups' to run at a scheduled
> time as the ISP is not likely to give me permissions to the SQL Server
> Agent details.
>
>
> Windows 2000 Server
> SQL Server 2000 Ent SP3
|
|
|
|
|