Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

Run a local DTS package

I have set up a DTS package.  When I ran the package manually, it went well.  But when I schedule it, it had this error.  I read some postings and articles about SQL agent account.  So I have set SQL agent account as a local system admin in that Win2k3 box and system admin in SQL Server 2k.  It does not help as all.  By the way, I also tried agent proxy account also.

Please help.

Bin


Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0
0
Jinghui Li
Asked:
Jinghui Li
  • 9
  • 6
  • 3
  • +2
6 Solutions
 
Saqib KhanSenior DeveloperCommented:
Which Account Executes DTS (Within DTS)?
when you manually execute DTS, that Account is same as provided within the DTS?
0
 
lozzamooreCommented:

Best way to troubleshoot these kind of issues is by logging onto the server where the package is scheduled, and running the package manually through the GUI. (This way you see what is going on in the job interactively)

This should help pinpoint the problem.

The error message itself seems to suggest name resolution or some other reason why the SQL Server instance name cannot be contacted from the server.

Hope this helps,
0
 
NavicertsCommented:
I am new to SQL server as well, just got my DTS packages to run on jobs with success, while your error is diffrent than mine hopefuly something here helps you....



Your DTS package must be reaching out to a server other than the local one, to retrive some info from somewhere else....  Does the SQL Agent account have authority on the OTHER server as well as the local one?

Also, the package takes on the access of the user that it was created by, i assume, because you can run it manually, that the user that created the package has the authority on both ends.

Make sure that the connection in your DTS package properties has the correct username/password as well.


-Navicerts
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
lozzamooreCommented:

>>Also, the package takes on the access of the user that it was created by

This isn't strictly correct.

The package runs under the authority of the person running it. For SQL Agent, this will be the authority of the service account.

Obviously any connection objects within the package will connect using SQL authentication if an explicit username and password is supplied, or using the service authority if trusted is specified.

Hope this helps,
0
 
NavicertsCommented:
Sorry i was not clear, all the packages i have made thus far i have left a static username/password supplied within the connection properties in the DTS package, i was talking about this scenario.  Still new to this just trying to help :)  Thank you for the correction lozz.

-Navicerts
0
 
Jinghui LiAuthor Commented:
>> Which Account Executes DTS (Within DTS)?
when you manually execute DTS, that Account is same as provided within the DTS? <<

What do you mean by the account "within the DTS"?

>>running the package manually through the GUI<<
Could you be more specific?

Thanks a lot,

Biang
0
 
Jinghui LiAuthor Commented:
It just came to my mind that the package does need to contact other server on domain.  I need to give a domain account name for SQL Agent.  But the SQL server runs the DTS package is not in domain yet.  How should I proceed?

I still appreciate  if experts answer two questions I asked.


Thanks
0
 
Eugene ZCommented:
what is your DTS doing?
if it  is usingf connection to anothere sql server - on main server where DTS pack located creat alias in client Network utility for second server (TCP\IP ..)

If not make sure there is last MDAC on server.. (last Sql server sp too)
0
 
Jinghui LiAuthor Commented:
It copies data from other server to intermediarary tables on the local server.  Then run a stored procedure to insert or update data to final tables.

I will double check I have lastest MDAC on that Win2k3 box.
0
 
Eugene ZCommented:
can you create  alias in client Network utility for other server (TCP\IP ..) on the local server?

0
 
Eugene ZCommented:
or try to use IP address in connection of DTS pacjk for other sql server..
0
 
Eugene ZCommented:
BTW: can you check that the job runs as 'sa' or with sa privlg account..?
0
 
Jinghui LiAuthor Commented:
>>can you create  alias in client Network utility for other server (TCP\IP ..) on the local server?<<

I thought I did since the alias name was among the server list under DTS connection properties.  But I found out the alias is only on my development platform, not on the server box.  So I rename the alias on the server as the one used in DTS connection.  The error is still here.

>> BTW: can you check that the job runs as 'sa' or with sa privlg account..?<<
I ran the job as OS local system admin and SQL server system admin.  Is it same as 'sa' account?
0
 
Jinghui LiAuthor Commented:
The problem was very likely with the connection to another SQL server, which is in a domain and in Windows authentication only mode.  The local server is not in domain but my development platform is.

Does the local SQL Server has to be in that domain?
0
 
Jinghui LiAuthor Commented:
I can run the DTS package successfully only on the development platform, which is on that domain and have the right to access the remote SQL Server.

When I logged in as local account, I can not run the DTS package.  The error message is the same.
0
 
Eugene ZCommented:
>Does the local SQL Server has to be in that domain?
not required...
>I ran the job as OS local system admin and SQL server system admin.  Is it same as 'sa' account?
it has same rights

Can you login on the server and try to register (based on alias specification- what protocol did you use -TCP\IP with port...?) the second server in EM?

Can you run DTS on the local server in design mode?

Can you change owner of the job to 'sa' user in Sql agent?
What a result?

0
 
lozzamooreCommented:

>>running the package manually through the GUI<<
>>Could you be more specific?

To troubleshoot all issues of the package running ok when you run it manually (ie through the GUI) but not when scheduled, log onto the SQL server using the account the SQLAgent is running under, and then running the package manually. (Excecute the package from within Enterprise Manager tool-Data Transformation Services-Local Packages etc..)

If your SQL Server where the package resides is not on the domain, then this shouldn't be an issue if all your connections are using SQL authentication (ie provided an explicit username and password) However, I would recommend that the services are setup to run under a local (admin) account, rather than the local system account. Ideally, if you are connecting to remote machines, you should add the server into the domain (or a trusted domain) and use a domain account for the SQL services.

The error message you originally state of: SQL Server does not exist or access denied.
is a little misleading. It returns when the Server cannot be contacted, rather than when access is truly denied, when you are most likely to receive a login failed error message.

Ensure that name resolution of the name you are using in the remote connection within the package is working (from the server!)
This will all be apparent by logging onto the local server as mentioned and running the package from there.

Cheers,
0
 
Jinghui LiAuthor Commented:
When I logged into my development PC with a local account, which does not have acccess to the remote SQL server.  I could not run the DTS package manually any longer.  The error is at the end the posting.

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0
0
 
Eugene ZCommented:
Thus:
or create\use sql server login
or nt\account for trusted connection...

0
 
Jinghui LiAuthor Commented:
On that remote SQL server I guess.  That server is in Active Directory domain while the local server is not.  I guess I have to move the local server into AD.  Right, EugeneZ?

Thanks

0
 
Jinghui LiAuthor Commented:
Thanks for your input.  Problem was solved after the box was moved into domain and SQL service runs as a domain account.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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