May 10, 2011 at 1:57 pm
I'm having issues creating a linked server across domains (RPDEV to RPBETA). Both are SQL Server 2008 R2 Enterprise 64bit (10.50.1600.1) editions. I can ping the fully qualified name and can authenticate to the target server on the RPBETA domain using RPDEV credentials. These are the scripts I'm using to create the linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'RPBETADB81', @srvproduct=N'sql_server', @provider=N'SQLNCLI10', @datasrc=N'RPBETADB81.RPBETA'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'RPBETADB81',@useself=N'False',@locallogin=NULL,@rmtuser=N'rplogin',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption....
This is the error I'm getting back:
Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI10" for linked server "RPBETADB81" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "RPBETADB81" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 53)
Also confirmed "ALLOW REMOTE CONNECTIONS" is enabled and windows firewall is off on the target server. Any ideas?
_____________________________________________________________________
- Nate
May 11, 2011 at 4:15 am
hmm tricky - I don't have cross domain linked servers. I do wonder if named pipes is an issue - I normally disable the named pipes library as a matter of course and only use tcp. I usually use the sqloledb provider and @srvproduct ='', other than that I can't see anything different in your scripts to mine.
Oh I link by ip addresses for servername.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 11, 2011 at 5:14 am
first test the client SSMS connectivity to the other server across the domain,is it working fine ?
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 11, 2011 at 6:53 am
Thanks for the responses.
colin.Leversuch-Roberts (5/11/2011)
I usually use the sqloledb provider and @srvproduct ='', other than that I can't see anything different in your scripts to mine.Oh I link by ip addresses for servername.
Tried this which creates the linked server w/ a successful "Test Connection" but is redirecting back to the local server (xxx.xx.x.28) when referenced. Will try disabling named pipes and see if that makes a difference.
EXEC master.dbo.sp_addlinkedserver @server = N'RPBETADB81', @srvproduct=N'', @provider=N'sqloledb', @datasrc=N'xxx.xx.x.38'
Syed Jahanzaib Bin hassan (5/11/2011)
first test the client SSMS connectivity to the other server across the domain,is it working fine ?
Yep, I can authenticate through SSMS (on and using RPDEV credentials) via the servername, fully qualified name and ip address.
_____________________________________________________________________
- Nate
May 11, 2011 at 11:48 pm
you can create the ODBC DSN connection and then create a linked Server with it
check the information regarding PROTOCOLS
http://aureus-salah.com/2010/02/13/sql-server-protocols/
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 12, 2011 at 1:40 pm
Thanks for your help but it seems like we have a name resolution issue. If I create the linked server named "BETADB" instead of the actual db server name, RPBETADB81, then it works fine.
_____________________________________________________________________
- Nate
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply