Creating a Linked Server to DMZ server

Question

Creating a Linked Server to DMZ server

Laura Schmalzbauer

SSCommitted

Points: 1672
More actions
June 15, 2012 at 8:48 am

#263013

I am trying to create a linked server between a server in the DMZ and one not in the DMZ. I thought if there was a login with the same name and password on both servers, I could create the linked server and have the linked server use that login. However, that is not the case.
I run this code to add the linked server:
EXEC sp_addlinkedserver
@server='test',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='SERVER=xxx.xx.xx.xxx'
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'test',
@locallogin = NULL ,
@useself = N'False'
,@rmtuser = 'test'
,@rmtpassword = 'test' ;
when I try to run a query under the login name I receive the following error:
OLE DB provider "SQLNCLI10" for linked server "test" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "test" 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.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].
Named Pipes and TCP are enabled on both servers. I'm able to ping the servers.
I've read a couple other places that an exception needs to be made in the firewall in order to access the server, is that the case?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

yes, you'll need either a VPN or a firewall hole to get to the DMZ server;

the whole purpose of the DMZ was to isolate it from contact for security purposes. so you have to open up the network for you to get to it; then your connection will work.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 2

At the very least you'll have to have TCP 1433 opened up through the firewall for that communication to happen (that's assuming you are running a default instance of SQL server or have hard set the port for a clustered or named instance).

Shamless self promotion - read my blog http://sirsql.net

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

You'll need to ensure any ports are open and also, since the servername SERVER=xxx.xx.xx.xxx won't exist or be resolvable, when creating the linked server change this code portion

@datasrc='SERVER=xxx.xx.xx.xxx'

To this

@datasrc='xxx.xxx.xxx.xxx'

or this

@datasrc='myservername'

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Laura Schmalzbauer SSCommitted Points: 1672 More actions · Answer 4

ok thank you for the replies. This confirms my suspicions.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

Laura Schmalzbauer (6/15/2012)
ok thank you for the replies. This confirms my suspicions.

it's also vital to get the server name correct in the linked server. You may even have ports open but because the naming is incorrect that will fail.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉