SQL Linked Server Authentication

careybroph
careybroph used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

This was a bug in SQL Server 2005 RTM and applying SP1 should fix it out..
But apply SP3 at both the servers since SP3 is available with several other bug fixes..

Ref here:

http://support.microsoft.com/kb/925001

And hope you have enabled remote connections correctly as per

http://support.microsoft.com/kb/914277

And you have enabled MSDTC correctly as per

http://support.microsoft.com/kb/839279

If you still face some more issues, kindly revert so that we can dig into it..
When you look at the properties of the linked server in Management Studio, what do you see for security?  Try using the 3rd (Be made using the login's current security context) or 4th option (Be made using this security context".

Also, when you run your query, keep in mind that you can't use dot dot - 2 consecutive dots.  You need to specify all four parts like this:
SELECT *
FROM SQLDB2.DBName.SchemaName.TableName
G GodwinDatabase Administrator
Commented:
It is quite possible your server and/or your user accounts are not set up properly for delegation.  That would be a network security issue.
Also, I see that "select * from SQLDB2..DBName.TableName" is not quite correct...
it should be LinkedServerName.DatabaseName.SchemaName.TableName.  So, if your schema is using the default (DBO), try:
select * from SQLDB2.DBName.DBO.TableName

Best Regards
-G
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

G GodwinDatabase Administrator
Commented:
So, if it is not working with Integrated security (i.e. NT account delegation), the quick work-around is to set up a SQL user account on the remote server, and then set the linked server security to use the remote account.  
-G
G GodwinDatabase Administrator
Commented:
Here's an example of setting up remote connections through your linked server to use a remote SQL login without O/S delegation.
-gg

Linked-server-sample-security2.JPG

Author

Commented:
Unfortunately, I do not manage the remote server.  And I'm dependent on our IT department to help troubleshoot.  I've passed on several of the suggestions and I'm waiting to hear back from someone about any/all of the suggestions.  I'm hoping it's a service pack issue, as that would be the easiest to address (I think).

I will follow-up once I hear back from them.
Thanks for all of the input.
Scott
G GodwinDatabase Administrator
Commented:
Having re-read your question;
What you really want to do is connect via MS Access. Right?
If so, I'd go back to that plan.  Using the linked server is going to add layers of complexity to the problem.  
They say you have been granted rights to the database with your network (domain) account. Correct?
Can you ping the server from your PC? If so, you should be able to connect.  Try setting up the ODBC DSN again.  Once you get a successfull test of the connection, you will be able to add LINKED tables to your Access database.  
Trust me, this will be easier to resolve, even though you will still be at the mercy of the support staff.
-G

Author

Commented:
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.

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial