• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

Linked Server - Login Failed

I've had a linked server created for the last few year and this morning it has stopped working.  It is now throwing an error login failed for user...

The setup is that I have a SQL Server 2005 setup where the linked server is created.  The linked server goes back to a SQL 2000 install where windows and SQL authentication is turned on (it's turned on for both servers).  The linked server has been setup with security of "Be made using this security context" with a login named BackupDB.  I have tried changing it to using the current security context and i get the same error message.

I have tried logging directly into SQL2000 using the BackupDB login, and it logs in just fine, so it's not a password or login problem.  I have tried restarting SQL Server 2000 in case something weird was happening there, but that didn't fix it either.  I also tried deleting the linked server and recreating it, but no good either.

This is the second server I've come across in the last month to start doing this, so I'm not sure what is going on to start causing this all of a sudden, they've been running fine for over a year now.

I also know nothing has changed on this server, I'm the only one that accesses it so no one else could have changed anything.
0
Drizzt95
Asked:
Drizzt95
  • 5
  • 5
1 Solution
 
MIKESoftware Solutions ConsultantCommented:

Is the server software being updated via auto updates?

Is anyone messing with the Active Directory logins?
0
 
Drizzt95Author Commented:
It is possible i guess, our sysadmin team do look after servers and push out patches every now and then, but servers are excluded from the general MS patches that are pushed out to PC's.  I'll certainly check that out.

It wouldn't be AD as the login that is failing is a SQL login, but it does fail with my AD login as well.
0
 
lundnakCommented:
The BackupDB  logon is a local user on the SQL 2000 server, correct?
When you logon to the SQL 2000 server as that user, are you able to access all of the necessary databases?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Drizzt95Author Commented:
That's right, It has been added as a sysadmin user.  I can browse everything, view data, update, etc.
0
 
lundnakCommented:
Could you post the exact error text?
0
 
Drizzt95Author Commented:
I have attached a screenshot.
SQLErro1.GIF
0
 
lundnakCommented:
I don't know if this will help or not, but we have received this SQLNCLI error before from client machines.
It was related to a DNS issue.  I had the developers configure the connection to use IP address only or I had them change the hosts file (C:\windows\system32\drivers\etc\hosts).  
Add a line that looks like
     10.1.0.1        SERVERNAME
0
 
lundnakCommented:
Also, when you have tested logging into the SQL 2000 server, were you on your client PC?  Have you tried connecting from the server hosting the SQL 2005 instance?
0
 
Drizzt95Author Commented:
ok, that's really interesting.  If I login to the SQLServer2005 server, I can connect to the instance ok.  I then tried adding the linked server while logged into the server and it worked.  I did a test connection on that linked server from my client PC (which failed before) and it worked as well.  I then deleted the linked server and tried adding it from my client PC again and it still failed.

So while I can't add from my local install, i can from the server which is good enough for me.  I've been meaning to upgrade the mgmt studio to 2008 so this just might be a good excuse and hopefully should fix the issue further.

Thanks a lot for your prompt assistance.
0
 
lundnakCommented:
That is very unusual.

One note with SSMS 2008, I just upgraded to it.  I ran into an issue where the install said "restart failed".

I had to run regedit on my computer and clear our the following registry key.
HKLM>System>CurrentControlSet>Control>Session Manager>PendingFileRenameOperations

Also, if you don't want to upgrade to SSMS 2008, make sure you have SQL 2005 SP2 installed on your PC.
0
 
Drizzt95Author Commented:
cool, thanks for the tip.  I'm pretty sure I haven't installed SP2 on my local PC yet, but I've been meaning to go to 2008 anyway.

Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now