Unable to access the Linked Server

Question

Unable to access the Linked Server

MuraliKrishnan1980

SSCrazy

Points: 2497
More actions
September 4, 2012 at 11:22 pm

#393100

[font="Tahoma"]Hi Friends,
I have been trying to create a Linked Server from my machine for the last few days and I am not able to do so successfully. My Machine is having SQL Server 2008 R2 Dev Edition and the machine that i am trying to connect is SQL Server 2008 Express Edition.
I have enabled the Named Pipes and TCP/IP in the Express Edition Machine. Please find below the code that I am using to create the linked server.
EXEC master.dbo.sp_addlinkedserver @server = N'PC068751\SQLEXPRESS', @srvproduct=N'',@datasrc = N'PC068751\SQLEXPRESS',@provider='SQLNCLI'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PC068751\SQLEXPRESS',@useself=N'False',@rmtuser='****',@rmtpassword='******'
I have masked the username and password for security purpose.
However I am getting an error while trying to test the connection or access the server using OPENROWSET. I have read numerous articles. However I am unable to resolve the issue. Your help would be appreciated..
[/font]

Viewing 8 posts - 1 through 7 (of 7 total)

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

SQL Surfer '66 SSCertifiable Points: 5161 More actions · Answer 1

Is the linked server using the default port 1433? Otherwise you need to specify the port: @server = N'PC068751\SQLEXPRESS,<port>'

MuraliKrishnan1980 SSCrazy Points: 2497 More actions · Answer 2

SQL Surfer '66 (9/5/2012)
Is the linked server using the default port 1433? Otherwise you need to specify the port: @server = N'PC068751\SQLEXPRESS,<port>'

Do i need to specify the port number in the @server or @datasrc

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

First thing to check: from the SQL Server, can you connect to the SQLExpress instance (with SSMS or SQLCMD)?

If not, there's no need to involve the linked server in your troubleshooting.

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

SQL Surfer '66 (9/5/2012)
Is the linked server using the default port 1433? Otherwise you need to specify the port: @server = N'PC068751\SQLEXPRESS,<port>'

True only if the port is static, which is not by default.

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

MuraliKrishnan1980 (9/5/2012)
SQL Surfer '66 (9/5/2012)
Is the linked server using the default port 1433? Otherwise you need to specify the port: @server = N'PC068751\SQLEXPRESS,<port>'
Do i need to specify the port number in the @server or @datasrc

You'd better use an alias in the configuration manager and keep the linked server simple.

-- Gianluca Sartori

MuraliKrishnan1980 SSCrazy Points: 2497 More actions · Answer 6

First thing to check: from the SQL Server, can you connect to the SQLExpress instance (with SSMS or SQLCMD)?
If not, there's no need to involve the linked server in your troubleshooting.

Hi,

I would go ahead and create an alias for the sql express instance. I enabled the remote connection, TCP/IP and Named Pipe in the Express machine and I am able to view the server name of the machine in the Netwerk Servers list while trying to connect to the Database Engine from SSMS. However, I am unable to connect to the same.

What could be the problem? Can someone provide me the steps to connect to an express instance from a Developer Instance..

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

Some things to check:

1) Remote connections enabled and service restarted on the Express instance (according to your reply, this should be ok)

2) TCP/IP enabled with a static port on the Express instance

3) Native Client alias created on the Dev instance machine, pointing to the host/port of the Express instance

4) Network traffic enabled in your firewall

-- Gianluca Sartori