Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2010-12-15 : 14:40:01
|
| Hi,I created a DTS package, which connects to a database and runs a stored procedure. The stored procedure is in the database, which is on the same SQL 2000 server as the DTS package.When I run the DTS package, it works fine. But when I schedule it as a job and either run it manually or schedule to run it, I get the following connection error:" DTSRun: Loading... 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. Process Exit Code 1. The step failed."SQL Server Agent runs under a domain account which is an administrator on the machine MSSQL is running. I tried using both internal ip and machine name and in both cases it worked when i run the dts package manually, but fails when it runs as a job.Any suggestions?Thanks a lot! |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-15 : 15:25:15
|
| If the domain account is local admin on the machine, then make sure (in SQL Server) that BUILTIN\Administrators are assigned to the server role sysadmin.Or, just add SQL Agent account to sysadmin server role. |
 |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-12-17 : 13:06:07
|
| Hello russell,Thank you for the suggestions. I tried both of these approaches, but am still getting the same error. Any other advice?Thanks a lot! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-17 : 13:13:53
|
| Well, it is a permissions issue. Is the stored procedure accessing a linked server?I'd double check the permissions on the service account. |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-12-17 : 13:55:02
|
| Russell,The stored procedure is running locally on the server [i.e. not accessing the linked server]. I just double checked the SQLAgent service account. It is running using the domain account. This domain account has also been added to Administrators group on the same machine locally.Is there anything else to check?Thank you! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-17 : 13:57:46
|
| Did you make sure the domain account is sysadmin on the SQL instance?Inside the DTS, how is the connection made? Using SQL credentials or Domain credentials? |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-12-17 : 14:26:02
|
| Russel,The problem has been solved!I'm not sure how these things are related, but since the DTS packages were ported from another server, the owner of the DTS package was the user on another domain. I changed it to be the admin on the current domain and re-scheduled it as a job and then it worked.Thanks a lot! |
|
|
|
|
|
|
|
|
Odd DTS job/security problem