February 22, 2007 at 8:41 am
I am having one of those days where nothing seems to be working....must be the gremlins.......
I have a SSRS report that gets data from a stored procedure. This procedure queries several linked servers and reports back info on failed jobs. The sproc is intelligent enough to create the linked server if it doesn't already exist. The call to create the linked server is simply
sp_addlinkedserver @servername = 'RemoteServer'.
This has been working like a charm for the past couple of weeks...until this morning. Now, the sproc errors out on the first linkedserver query with the following:
OLE DB provider "SQLNCLI" for linked server "RemoteServer" 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 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
I am going crazy trying to figure out what could have changed. Now I am turning to you all for suggestions.
Thanks
Gordon
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
February 22, 2007 at 11:18 pm
Is the used in linked server configuration's password changed or expired?
And also check the following blog...
http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx
MohammedU
Microsoft SQL Server MVP
February 23, 2007 at 9:40 am
This message normally comes up when the integrated authentication is used the the remote computer can not authenticate your Windows credentials because of the domain trust (or the absence of the domain trust). If I log in as a domain user and both computers are in the same domain and appropriate firewall ports are open for Kerberos ports (or whatever you use for your Windows authentication) then it works. If you log in as a local login then remote computer can not get your login credentials and this error message will come up. Also it depends who is running the procedure, you personally logged as a domain user and using your Windows authentication all around or a job that authenticates as SQL Server login or you personally connected to your local server in the QA as a local user.
For example, I registered my local SQL Server using 1. Windows Authentication 2. As SA. The linked server to another SQL Server contains 2 entries in Security: one for my domain login and one for SA, in both cases, Impersonate is checked. I run the same query from the both query windows for both registration . In case 1 my query returns results, in the second case it returns the message similar to your error message.
Check how you login to the server, how you register your local server and what it is in the security for the linked server.
Regards,Yelena Varsha
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply