I work for a large university, but oversee a small department with our own servers and infrastructure. I have an MS SQL 2005 database that I run locally (we'll call tihs SQLDB1), but I actually have an application that needs to link to another SQL database elsewhere in the university (let's call this SQLDB2). I've been given permissions to the SQL server and I can connect directly using the SQL Server Management Studio. I can open the database of interest on that server and can run a query on a table within that DB. No problems there.
HOWEVER, this is not my objective. I need to access these data from an MS Access database application. So, the first logical step seemed to be to create an ODBC connection to the SQL database. That didn't seem to work at all. Kept getting an error message saying something along the lines of "Database does not exists or invalid login". I think it could be a firewall issue blocking ODBC connectivity. Not sure, but I've inquired with our IT department, which will take awhile to respond. Assuming that it was a firewall issue that they would not be able to circumvent, I tried plan B.
Plan B: Seeing that I could connect from the SQL Server Management Studio, I figured I'd try to leverage that functionality. I used sp_linkedserver to link to SQLDB2 from SQLDB1. It seemed to work just fine (got the 'Successfully Completed' message). But when I try to run an actual query using the linked server, I get an error message.
From SQLDB1, I ran the following:
Next, again from SQLDB1, I run the following query to test connection:
select * from SQLDB2..DBName.TableName
But I get this message:
OLE DB provider "SQLNCLI" for linked server "SQLDB2" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
After doing a little digging, I tried to force it to use NT Authentication (which is used for SQLDB1) by running this command:
EXEC sp_addlinkedsrvlogin 'SQLDB2', 'true'
But that didn't seem to help at all. For whatever reason, it seems to want to connect to the remote DB with an anonymous login.
Any ideas or direction would be appreciated.