Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2005
    Posts
    10

    Unanswered: Refuse connections

    Hi guys,

    I would like to know how could I configure my SQL Server (2000) to refuse every connection except localhost or server-name ?

    I am sure this can be acomplished, but I've looked everywhere and can't find it.

    I use asp.net only and I want to configure the server to accept connections from local computer and that's it. Any thoughts?

    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Disable all network protocols except shared memory.

  3. #3
    Join Date
    Jul 2005
    Posts
    10
    Thanks.

    Also, I have two instances of MSSQL installed, one is SQL 2000 & other is Desktop Edition, will the client configurations apply to both or just to SQL 2000 and if just to SQL 2000 how can I configure it also for the Desktop Edition?

  4. #4
    Join Date
    Jul 2005
    Posts
    10
    It doesn't work. Tested it today, disabled all protocols except shared memory and I still can connect from another box to that specific box with Enterprise Manager (for example)

    Any other ideas?

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by bugsoftware
    It doesn't work. Tested it today, disabled all protocols except shared memory and I still can connect from another box to that specific box with Enterprise Manager (for example)

    Any other ideas?
    Did you restart SQL after changing the network configuration? Look at the error log for SQL Server when it restarts, there should be entries in there to indicate what protocols/ports SQL is listening on.

    Regards,

    hmscott
    Have you hugged your backup today?

  6. #6
    Join Date
    Jul 2005
    Posts
    10
    Restarted, still same thing. I can connect from one box to the other. Log file:

    2005-12-06 04:11:31.10 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
    Dec 17 2002 14:22:05
    Copyright (c) 1988-2003 Microsoft Corporation
    Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

    2005-12-06 04:11:31.10 server Copyright (C) 1988-2002 Microsoft Corporation.
    2005-12-06 04:11:31.10 server All rights reserved.
    2005-12-06 04:11:31.10 server Server Process ID is 3336.
    2005-12-06 04:11:31.10 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
    2005-12-06 04:11:31.20 server SQL Server is starting at priority class 'normal'(1 CPU detected).
    2005-12-06 04:11:32.78 server SQL Server configured for thread mode processing.
    2005-12-06 04:11:32.79 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
    2005-12-06 04:11:32.85 server Attempting to initialize Distributed Transaction Coordinator.

    2005-12-06 04:11:35.71 spid3 Starting up database 'master'.
    2005-12-06 04:11:36.10 spid3 Server name is 'SERVER9'.
    2005-12-06 04:11:36.10 spid3 Skipping startup of clean database id 44
    2005-12-06 04:11:36.26 server Using 'SSNETLIB.DLL' version '8.0.766'.
    2005-12-06 04:11:36.26 spid5 Starting up database 'model'.
    2005-12-06 04:11:36.35 spid8 Starting up database 'msdb'.
    2005-12-06 04:11:36.35 spid9 Starting up database 'pubs'.
    2005-12-06 04:11:36.37 spid10 Starting up database 'Northwind'.
    2005-12-06 04:11:36.37 spid11 Starting up database 'ahsweb'.
    2005-12-06 04:11:38.68 spid5 Clearing tempdb database.
    2005-12-06 04:11:39.42 server SQL server listening on 69.56.147.XXX: 1433.
    2005-12-06 04:11:39.42 server SQL server listening on 69.56.147.XXX: 1433.
    2005-12-06 04:11:39.42 server SQL server listening on 69.56.147.XXX: 1433.
    2005-12-06 04:11:39.42 server SQL server listening on 69.56.147.XXX: 1433.
    2005-12-06 04:11:39.42 server SQL server listening on 69.93.178.XXX: 2005-12-06 04:11:39.42 server SQL server listening on 69.93.178.XXX: 1433.
    2005-12-06 04:11:39.42 server SQL server listening on 127.0.0.1: 1433.
    2005-12-06 04:11:40.45 server SQL server listening on TCP, Named Pipes.
    2005-12-06 04:11:40.48 server SQL Server is ready for client connections
    2005-12-06 04:11:39.95 spid9 Starting up database 'ahl'.
    2005-12-06 04:11:40.06 spid10 Starting up database 'afp'.
    2005-12-06 04:11:40.31 spid8 Starting up database 'act'.
    2005-12-06 04:12:19.15 spid8 Recovery is checkpointing database 'eah' (73)
    2005-12-06 04:12:29.78 spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
    2005-12-06 04:12:55.46 spid3 Recovery complete.
    2005-12-06 04:12:55.46 spid3 SQL global counter collection task is created.

  7. #7
    Join Date
    Jul 2005
    Posts
    10
    The Enterprise Manager says TCP is disabled but it still shows up on the logs

    Any work arround?
    Last edited by bugsoftware; 12-06-05 at 06:34.

  8. #8
    Join Date
    Jul 2005
    Posts
    10
    2005-12-06 05:37:02.30 server SQL server listening on 69.9.44.XXX 1433.
    2005-12-06 05:37:02.30 server SQL server listening on 69.9.44.XXX: 1433.
    2005-12-06 05:37:02.30 server SQL server listening on 69.9.44.XXX: 1433.
    2005-12-06 05:37:02.30 server SQL server listening on 69.9.44.XXX: 1433.
    2005-12-06 05:37:02.30 server SQL server listening on 69.9.44.XXX: 1433.
    2005-12-06 05:37:02.30 server SQL server listening on 127.0.0.1: 1433.
    2005-12-06 05:37:02.30 server SuperSocket Info: Bind failed on TCP port 1433.
    2005-12-06 05:37:02.30 server SuperSocket Info: Bind failed on TCP port 1433.
    2005-12-06 05:37:02.32 server SQL server listening on TCP, Shared Memory.
    2005-12-06 05:37:02.32 server SQL Server is ready for client connections

    Even thought TCP is disabled

  9. #9
    Join Date
    Jul 2005
    Posts
    10

  10. #10
    Join Date
    Dec 2002
    Posts
    1,245
    You're sure you are diabling TCP in the Server Network Parameters (or Server Network Utility)? The Client network utility looks the same, but it has a different function. Also, why the multiple IP addresses? I don't usually multi-home a database server (except for a cluster), so I don't know how SQL will behave with multiple IP addresses.

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •