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.