DTS Authentication problem

Question

DTS Authentication problem

Gunter Eifler

Grasshopper

Points: 24
More actions
March 22, 2007 at 5:23 am

#62363

I have a problem with my DTS packages scheduled through SQL Agent jobs. When a job runs it returns an error 'Login failed for user 'Null'. Not a trusted SQL connection'
This has been happening since the application of some windows patches.
When logged on to the server using RDP and connecting through QA using an account with sysadmin rights produces the same result!
I have found that the server has no entry for itself in the sysservers table and that @@servername returns NULL result. could this be the cause?
Microsoft has got around the issue by creating an Alias for the server in the Client NW utility, but i suspect this is not addressing the cause of the problem!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John McC Hall of Fame Points: 3410 More actions · Answer 1

You could try sp_addlinkedserver to restore the entry but I've no idea how it came to be deleted in the first case.

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:

Gunter Eifler Grasshopper Points: 24 More actions · Answer 2

Well that did occure to me, but all the DTS packages that are failing were local to the problematic server, not connecting to any other. Why should the server require a link to itself?

John McC Hall of Fame Points: 3410 More actions · Answer 3

It appears that your server has "lost" its server name. Using sp_addlinkedserver or addserver is just going to replace it in the sysservers table

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:

Gunter Eifler Grasshopper Points: 24 More actions · Answer 4

Well using sp_addserver has added the server to the syservers table but @@servername still brings a result of NULL. Does the service require restart to update @@servername?

John McC Hall of Fame Points: 3410 More actions · Answer 5

Yes, it does require a restart.

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:

happycat59 One Orange Chip Points: 29389 More actions · Answer 6

Did you specify the @local parameter - this is needed when adding the name of an instance to itself

sp_addserver 'myServerName', 'Local'

If you do not include this parameter, SQL Server assumes that the server you are adding is a completely separate instance of SQL

Gunter Eifler Grasshopper Points: 24 More actions · Answer 7

Yeah, I did include the @local parameter. this has added the servername to syservers but has not resloved the DTS auth problem unfortunately. beginning to suspect it may be a kerberos authentication problem or something in AD to do with account delegation. back to the the drawing board!.

thanks