locked
Disable TCP/IP protocol for the server instance to stop connecting from local PC but the SharePoint stop working RRS feed

  • Question

  • Hi,

    I need help that I am trying to Disable TCP/IP protocol for the server instance to stop connecting from local PC but the SharePoint stop working if I Disable TCP/IP protocol for the server instance. Can you please help me how to solve this issue.

    Thank you.

     


    Afzalkhan


    • Edited by AfzalKhanBH Monday, February 27, 2017 1:06 PM
    Monday, February 27, 2017 1:05 PM

All replies

  • Hello,

    by default for local connections the "Shared Memory" protocol is used, not TCP/IP. If you disable TCP/IP then remote connections won't work and application like e.g. SharePoint stop working, of course.

    Why do you want to disable TCP, don't make much sense for me?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Monday, February 27, 2017 1:32 PM
  • If you want to stop inbound connections to SQL Server, you can just disable 'remote access' with this TSQL command:

    EXEC sys.sp_configure N'remote access', N'0'
    GO
    RECONFIGURE WITH OVERRIDE
    GO

    I think that would be a safer option than disabling TCP/IP protocol on SQL Service.

    Then, if you want to enable remote connections again, just reset the 'remote access' value back to 1.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Monday, February 27, 2017 1:59 PM
    Monday, February 27, 2017 1:51 PM
  • Phil,

    I don't think that 'rempoe access' set to 0 will stop inbound connection. This is for the old "remote procedure calls" functionality (pre-linked servers). At least according to below, but I admit I haven't played with it for a long long time. :-)

    https://msdn.microsoft.com/en-us/library/ms191464.aspx


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, February 27, 2017 3:09 PM
  • You may be right Tibor. Thanks for pointing that out.

    Possibly, another alternative to stopping external connections might be to disable logins, like this:

    -- disable all logins except sa
    sp_configure 'allow updates', 1
    go
    Reconfigure with override
    go
    update master..syslogins
    set denylogin=1 where name not in('sa')
    sp_configure 'allow updates', 0
    go
    Reconfigure with override
    go

    ..and then, to re-enable logins

    -- enable logins
    sp_configure 'allow updates', 1
    go
    Reconfigure with override
    go
    update master..syslogins
    set denylogin=0 where name not in('sa')
    sp_configure 'allow updates', 0
    go
    Reconfigure with override
    go

    WARNING!: Be sure you are logged in as sa and know the sa password, before doing this.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Monday, February 27, 2017 3:55 PM
    Monday, February 27, 2017 3:22 PM
  • As Olaf says, local connections default to shared memory, and will then attempt both TCP and named pipes if they are available.

    You could make local connections difficult by using a logon trigger, or by disabling shared memory and named pipes and then using a firewall rule to block the local IP address. But all this sounds very odd. Perhaps you can explain why you do not want people to connect locally? Configure the permissions carefully, and you should be able to restrict users from inappropriate actions. After all, if someone can do something bad when connected with SSMS locally, they could do the same bad thing when connected with SSMS using TCP.

    (And of course, administrators of the local computer can always access SQL Server.)


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, February 27, 2017 6:59 PM
  • Hi Rick,

    Thank you for answer but still I am little bit confuse, I want that the user should not able to connect the SQL instance from their local PC.

    How can i configure?

    Thank you.


    Afzalkhan

    Tuesday, February 28, 2017 5:34 AM
  • Hi Afzalkhan,

    Based on my understanding, you want to restrict some users with valid logins to access SQL Server from their own computers, right? If that is the case, you can just create logon trigger in SQL Server, for more detailed steps, please review the following blogs.

    SQL Server: Restrict Login from Valid Machine IPs Only (Using Logon Trigger)
    How to prevent users from accessing SQL Server from any application or any login expect your main application & its login

    Thanks,
    Lydia Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, February 28, 2017 7:25 AM
  • Disable the protocol/port in the Clients FireWall.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, February 28, 2017 8:00 AM
  • But remember that administrators of the local computer can always connect to a SQL Server on that computer. For details, see Protecting Your SQL Server Intellectual Property https://msdn.microsoft.com/en-us/library/mt778968.aspx.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, March 1, 2017 3:40 PM