?
Solved

Run a local DTS package

Posted on 2005-03-07
21
Medium Priority
?
241 Views
Last Modified: 2013-11-30
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
Comment
Question by:Jinghui Li
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 3
  • +2
21 Comments
 
LVL 23

Expert Comment

by:Saqib Khan
ID: 13481096
Which Account Executes DTS (Within DTS)?
when you manually execute DTS, that Account is same as provided within the DTS?
0
 
LVL 7

Assisted Solution

by:lozzamoore
lozzamoore earned 800 total points
ID: 13481106

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
 
LVL 7

Accepted Solution

by:
Navicerts earned 800 total points
ID: 13481127
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
Industry Leaders: 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!

 
LVL 7

Assisted Solution

by:lozzamoore
lozzamoore earned 800 total points
ID: 13481248

>>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
 
LVL 7

Assisted Solution

by:Navicerts
Navicerts earned 800 total points
ID: 13481292
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
 

Author Comment

by:Jinghui Li
ID: 13481966
>> 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
 

Author Comment

by:Jinghui Li
ID: 13482105
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13482115
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
 

Author Comment

by:Jinghui Li
ID: 13482683
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13482718
can you create  alias in client Network utility for other server (TCP\IP ..) on the local server?

0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13482721
or try to use IP address in connection of DTS pacjk for other sql server..
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13482730
BTW: can you check that the job runs as 'sa' or with sa privlg account..?
0
 

Author Comment

by:Jinghui Li
ID: 13482975
>>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
 

Author Comment

by:Jinghui Li
ID: 13483805
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
 

Author Comment

by:Jinghui Li
ID: 13483840
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
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 400 total points
ID: 13485404
>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
 
LVL 7

Assisted Solution

by:lozzamoore
lozzamoore earned 800 total points
ID: 13486061

>>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
 

Author Comment

by:Jinghui Li
ID: 13487949
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13488011
Thus:
or create\use sql server login
or nt\account for trusted connection...

0
 

Author Comment

by:Jinghui Li
ID: 13488046
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
 

Author Comment

by:Jinghui Li
ID: 13502971
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question