Linked Server Issues - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Linked Server Issues

Author

Topic

smithygreg
Starting Member

37 Posts

Posted - 2008-03-12 : 17:55:09

Hi There...
I am having trouble creating getting my SQL SERVER linked server to work. I get this error.

OLE DB provider "SQLNCLI" for linked server "servername" returned message "Unable to complete login process due to delay in opening server connection".
Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "servername".

I have done a ton of searching around and not found a solution for my problem. Does anyone know what this error actually is saying?
I have my local user mapped to the correct account. I have set the default to the correct account as well..

Another post I found suggested runnign this query and posting the results, so thats what I am doing here...
QUERY:

select S.server_id, S.name, S.provider,S.data_source,S.provider_string, L.uses_self_credential
from sys.servers as S, sys.linked_logins as L
where S.server_id=L.server_id and S.name=N'servername'

RESULTSET:
serverid name provider datasource provider_string uses_self_credential
1 servername SQLNCLI servername NULL 0
1 servername SQLNCLI servername NULL 0

Any help would be greatly appreciated...I ave spent all day trying to figure this out and I am at my wits end.

Thanks in advance!
Greg

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-03-12 : 19:27:24

What happens when you test the connection to the server either by right clicking it in SSMS and choosing the 'Test Connection' option or running the script at this link - http://blogs.msdn.com/sqltips/archive/2005/06/07/426578.aspx.

Does this happen immediately or does the connection request hang for a certain time? If it's immediate you might want to see if the connection timeout on the linked server is set to something extremely low.

Also, as some other searches I encountered, they suggested making sure the security permissions are set correctly.

smithygreg
Starting Member

37 Posts

Posted - 2008-03-12 : 20:11:31

I tried right clicking and "Test Connection", but that option doesn't even exist on my SSMS. Then I ran that query that was on the page and it returned the error message.
It takes about 25 seconds for the error message to show up on all my query attempts.
Actually..It takes exactly 25 seconds every time

My connection timeout is set to 15. I was reading somewhere that Testing the connection will cause 10 seconds to be added before the results are returned which would add up to 25 seconds...But I don't remember where I read that and it may be totally unrelated and just a coincidence...

Any other tips?
My security credentials are OK..I have tested them numerous times.
Thanks again!
Greg

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-12 : 22:49:23

Did you link to another sql server? How did you create linked server? How did you map user to sql login on remote server?

smithygreg
Starting Member

37 Posts

Posted - 2008-03-13 : 11:40:36

Did you link to another sql server?
I only have these 2 servers. An interesting point..I can create a linked server on the other machine pointing this one and it works fine, but I can't go the other way...AND unfortunately, due to a bug in SQL Server Linked Servers and Identity Inserts, I have to pull the data, not push it.

How did you create linked server?
I created the LS using the wizard when I right-clicked on "Linked Servers"...I selected SQL Server and gave it the correct name.

How did you map user to sql login on remote server?
I added a local login with me as the user, and then for the remote login I chose an account which I am positive is a vald account/password. I also added the same username/password "for logins not defined above"

Oh this is driving me crazy.
Thanks again!
-Greg

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-13 : 22:56:07

Do those servers have same version and sp level?

smithygreg
Starting Member

37 Posts

Posted - 2008-03-14 : 14:58:49

Hey...
Just thought I should resolves this.
We had a firewall between the two machines that was blocking the traffic.
Thanks for all your help though...
-Greg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources