SQL Linked Server Authentication
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:
EXEC sp_addlinkedserver
'SQLDB2',
N'SQL Server'
"Successfully Completed"
------------------
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'
"Completed Successfully"
--------------------------
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.
Scott
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:
EXEC sp_addlinkedserver
'SQLDB2',
N'SQL Server'
"Successfully Completed"
------------------
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'
"Completed Successfully"
--------------------------
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.
Scott
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SOLVED!!!
GDG_DBA encouraged me to try and go back to the original objective (ODBC connectivity from MS Access). This time it worked! I didn't change any of the original settings that I had tried, but now it's working. I had forwarded several of the comments to the remote DB Manager for consideration (including some info found online about possibly not being setup to accept ODBC connections. Because this is a medical records DB behind a firewall, I also forwarded some info about Port 1433 possibly blocking it. I haven't heard back from him yet so I don't know what he changed.
Anyway, as I said, I forwarded several of the comments to the DB Manager for the remote db. Not sure what the actual issue was, but it's working and I'm pleased.
Thanks to ALL of you for the many insights you've provided. Even though the solution was ODBC, I'll be using the linked db model for other projects at some point and I've received quite an education in troubleshooting it.
Have a GREAT night folks. And thanks again.
GDG_DBA encouraged me to try and go back to the original objective (ODBC connectivity from MS Access). This time it worked! I didn't change any of the original settings that I had tried, but now it's working. I had forwarded several of the comments to the remote DB Manager for consideration (including some info found online about possibly not being setup to accept ODBC connections. Because this is a medical records DB behind a firewall, I also forwarded some info about Port 1433 possibly blocking it. I haven't heard back from him yet so I don't know what he changed.
Anyway, as I said, I forwarded several of the comments to the DB Manager for the remote db. Not sure what the actual issue was, but it's working and I'm pleased.
Thanks to ALL of you for the many insights you've provided. Even though the solution was ODBC, I'll be using the linked db model for other projects at some point and I've received quite an education in troubleshooting it.
Have a GREAT night folks. And thanks again.
ASKER
Thanks again for taking the time to help me work through the issue. If I ever hear back on what they changed, I'll pass it along.
ASKER
I will follow-up once I hear back from them.
Thanks for all of the input.
Scott