Solved

dts login failure

Posted on 2007-04-10
11
782 Views
Last Modified: 2008-01-09
I have a DTS package which truly has been working error free for more than a year.  Last night it fails, wit this error:  

DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_1
DTSRun OnError:  DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005)
   Error string:  Cannot open database requested in login 'DBNAME'. Login fails.
   Error source:  Microsoft OLE DB Provider for SQL Server
   Help file:  
   Help context:  0
Error Detail Records:
Error:  -2147467259 (80004005); Provider Error:  4060 (FDC)
   Error string:  Cannot open database requested in login 'DBNAME'. Login fails.
   Error source:  Microsoft OLE DB Provider for SQL Server
   Help file:  
   Help context:  0
DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_1
Error:  -2147220440 (80040428); Provider Error:  0 (0)
   Error string:  Package failed because Step 'DTSStep_DTSExecuteSQLTask_1' failed.
   Error source:  Microsoft Data Transformation Services (DTS) Package
   Help file:  sqldts80.hlp
   Help context:  700

Absolutely nothing has been changed in the package, or in any fo the sql config / settings. the job is just a basic loop and load...going to a remote destination, retrieving files and loading them.   i'm wondering if something's been changed on one of the resources to which sql is connecting to retrieve the files that are being loaded.  i am checking that, but i'd also like to know, where is this login specified?  surely i did it, i just don't know where, and i'd like to see if there's a problem there.  the login reported in the error log...where is this actually specified to the package?


this i very important, i do appreciate any insight
thank you much
0
Comment
Question by:dbaSQL
[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
  • 5
  • 3
  • 3
11 Comments
 
LVL 18

Expert Comment

by:Sham Haque
ID: 18881906
can you run the package successfully manually, rather than as a schedule?
if so, then the finger of blame is pointing to the RunAs user specified in the scheduled job
0
 
LVL 18

Accepted Solution

by:
Sham Haque earned 350 total points
ID: 18881931
>>the login reported in the error log...where is this actually specified to the package?

if you open your package in Design mode: Enterprise Manager > (your db) > Data Transformation Services > Local Packages, and right-click your package and choose "Design Package"

From there, your server connection will be a grey server box - right-click and choose Properties, and the login details are specified there...
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 18881985
no, gbshahaq, i cannot run it manually or via the agent secheduler
and, the server connection specified there is windoes authentication -- my account and the login which sql server svc is running under, are admins on the box.
i guess i am going to change it to run as sa, but still, what puzzles me is the fact that nothing has been changed on the sql config, system security and/or dts package configuration.
what do you think could cause this?
0
 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

 
LVL 17

Author Comment

by:dbaSQL
ID: 18882013
and, having changed it to sa, the package still fails at the same step, with the same failure message:

output                                                                                                                                                              ---------------------------------------------------------------------------------------------------------------------------
DTSRun:  Loading...
DTSRun:  Executing...
DTSRun OnStart:  DTSStep_DTSActiveScriptTask_1
DTSRun OnFinish:  DTSStep_DTSActiveScriptTask_1
DTSRun OnStart:  DTSStep_DTSActiveScriptTask_2
DTSRun OnFinish:  DTSStep_DTSActiveScriptTask_2
DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_1
DTSRun OnError:  DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005)
   Error string:  Cannot open database requested in login 'DBNAME'. Login fails.
   Error source:  Microsoft OLE DB Provider for SQL Server
   Help file:  
   Help context:  0
Error Detail Records:
Error:  -2147467259 (80004005); Provider Error:  4060 (FDC)
   Error string:  Cannot open database requested in login 'DBNAME'. Login fails.
   Error source:  Microsoft OLE DB Provider for SQL Server
   Help file:  
   Help context:  0
DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_1
Error:  -2147220440 (80040428); Provider Error:  0 (0)
   Error string:  Package failed because Step 'DTSStep_DTSExecuteSQLTask_1' failed.
   Error source:  Microsoft Data Transformation Services (DTS) Package
   Help file:  sqldts80.hlp
   Help context:  700
(null)
0
 
LVL 18

Expert Comment

by:Sham Haque
ID: 18882016
you say "going to a remote destination" - is this set up as a linked server?
everything is all SQL Server - or other data sources?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 18882052
naah.... it's all sql.  my files are found at a location on a sql box, then loaded to another sql box
and, the sql server svc account, and my login, are admins across the board
this really puzzles me, as nothing has been changed at all.  or, at least nothing that i am aware of.  obviously, something has been altered somewhere.  why else would i suddently start failing w/login failures?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18884070
>>this really puzzles me, as nothing has been changed at all.<<
You have mentioned this a number of times.  You need to get out of that rut.  Accept the fact that something has in fact changed or you would not have the problem, let alone posting a question here.  Not start thinking where it could have changed.  Execute one task after the other until you find the culprit.  You can then find out what permissions are missing.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18884076
"Not start thinking... " -- > "Now start thinking... "
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 18884400
very, very odd.  way back when i first created this task, i had two other connections in there, basically idle now, but they were created on my workstation where the task orginated.  when i deployed to prod, i was only using the two prod connections (one sql and one text file).  anyway, so last friday i do some cleanup on my workstations.  dropping a few dbs, bringing all the code up to speed...you know, that sort of thing.  that is what caused this problem.  i find it very odd, as those two cxns were not part of the file transfer/load process at all.  but, i guess maybe they were active connections and possibly the connection was made maybe upon each invokation of the task.  hard to say.  but, when i blew out those two un-used/old connections, the package is now working just fine.

you two ok if i split the points between you both ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18886547
Yes.  Even though they are not in any workflow they will be used.  Always make a habit of deleting any orphaned connections, especially before deploying to a production environment.

>>you two ok if i split the points between you both ?<<
Just award points to gbshahaq
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 18888647
Alright, AC, and yes, I agree w/you.  I need to make it a point to ensure there are no orphaned connections.... well before i deploy to production.

Thank you both very much.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

627 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