[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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