2

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

2
  • 1
    If you have disabled it but you haven't stopped the instance yet, try stopping it.
    – DaniSQL
    Feb 18, 2015 at 11:44
  • good point. sometimes the error is right in front of you in a small detail like that. on this occasion however, " I stopped and disabled the swlon1 sql server services related to the default instance" Feb 18, 2015 at 11:52

1 Answer 1

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.