SQL Server – How to Disable Connections to a Linked Server

linked-serverloginssql serversql-server-2005sql-server-2008-r2

SQL Server Linked Server to default instance – service is disabled but connection is still possible

I have a server called SWLON1 that has SQL Server 2005 and SQL Server 2008 r2 installed.

The SQL Server 2005 is running as the default instance, the SQL Server 2008 R2 is running as a named instance sql2008.

I have linked servers to swlon1.

I don't want the linked servers to the SQL Server 2005 to work anymore, so I stopped and disabled the swlon1 SQL Server services related to the default instance as you can see on the picture below.

Still people can browse the catalogs and see the tables on the SQL Server 2005 instance, even though the services are disabled.

I installed a named instance SQL2008 and I want that to be the only one available to the users.

Is there a way to achieve this, or I will have to drop all the linked servers to the default instance (SQL Server 2005)?

I could also uninstall the SQL Server 2005 from that machine.

enter image description here

enter image description here

Best Answer

I found the solution that I wanted:

-- how to disable a linked server

--check the existent linked servers
SELECT * FROM SYS.SERVERS
WHERE NAME = 'SWLON1'


-- Disable data access
EXEC sp_serveroption @server = 'SWLON1' 
      ,@optname =  'data access'     
      ,@optvalue =  'FALSE'

-- ABOUT procedure sp_serveroption
--https://msdn.microsoft.com/en-us/library/ms178532.aspx

-- the idea as how to disable a linked server
--http://www.sqlservercentral.com/Forums/Topic1038823-146-1.aspx

enter image description here

Related Question