August 22, 2002 at 11:50 am
We have a database sitting in a ISP server and I tried to do a daily refresh to copy the data from a SQL server in our network over there using our internet connection. I set up a DTS package in one of the SQL servers here and execute the package successfully. Next, I created a job in SQL agent from the same DTS package in the same SQL server and tried to execute the job and it failed! The error message is listed as follows:
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: Delete from Table [Advantek].[advantek].[device_lookuptemp] Step
DTSRun OnError: Delete from Table [Advantek].[advantek].[device_lookuptemp] Step, Error = -2147467259 (80004005)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 17 (11)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
DTSRun OnFinish: Delete from Table [Advantek].[advantek].[device_lookuptemp] Step
DTSRun: Package execution complete.
Based on the error message, the problem seems to be pointing to a connection or security problem to that remote server at ISP. Since I can execute the package in DTS successfully so I assume I don't have any connection problem and security authentication to each sql server. I thought it may be the security between remote server and the job in our local SQL agent causing the problem. I read some TechNet articles and I thought I have done everything accordingly. When the job was first created, the owner is under my NT login name but I changed it to sa. I also
make sure that the service account running the SQL agent is a member of sysadmin role in our local SQL server but I don't know why this would help.
I am connecting to that remote server using TCP/IP and connecting to it using its IP address instead of the server name. I also registered that remote server sucessfully in EM using its IP address so I don't have any problems connecting to it except the job failed in SQL Agent. Both SQL server is 2000 and ours is SQL 2000 sp1.
I have been trying to solve this in the past few days and drive me nut since I can't find out what's wrong. Any help is very much appreciated.
Wingman
August 23, 2002 at 5:42 am
Do you use a Specific Username and ID to connect to the ISP machine? How do you defien the connection in the DTS package? Do you have the ISP server defined in the Client Network utility or using the IP address?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
August 23, 2002 at 8:14 am
Yes, I do have a specific user name and ID to connect to the ISP machine. Here is how I defined the connection:
Server: 111.111.111.111 (Not the real IP)
Use SQL Server Authentication
User name: Whatever
Password: xxxxxxxxx
I also defined the ISP server in the SQL network client and even tried switching between TCP/IP and name pipe, it still doesn't work.
quote:
Do you use a Specific Username and ID to connect to the ISP machine? How do you defien the connection in the DTS package? Do you have the ISP server defined in the Client Network utility or using the IP address?"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply