locked
Disable remote login to sql server 2005 RRS feed

  • Question

  • Hi everyone !

    I installed SQL Sever Management studio 2005 in my computer. then I successfully accessed the database from another computer in Local Network (Using .Net ).Unfortunately the remote computer can login into the preinstalled Sql server in my computer once he/she installed SQL server in his Pc(using My Network IP Address) .

    Is there a method to disable any remote machine from login into my SqL server?

    thanks in advance

    Monday, February 11, 2013 6:19 AM

Answers

  • The SQL Server Database Engine doesn't really care who or what is connecting to it. If the connection provides a valid Windows authentication login or a valid SQL Server authentication login name and password, then SQL Server is happy. Your .Net client can connect. SQL Server Management Studio can connect. Microsoft Access, Excel, or Word can connect. An ODBC client can connect. The opportunity to enable/disable remote protocols (named pipes and TCP) is a single on/off configuration. It can't be configured to work for some clients and not others.

    So what do you do?

    • You can switch to 3-tier architecture.
    • You can configure the firewall to only allow certain connections.
    • You can restrict applications to only running stored procedures with valid parameters so you don't care what client connects.
    • You can create a login trigger to evaluate what program the client says it is connecting from (but the client can lie).
    • You can use application roles (described in Books Online).
    • You can use USERS WITHOUT LOGINS as described in SQL Server 2005 Security Best Practices - Operational and Administrative Tasks

    What you need, really depends upon your business need.


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

    • Marked as answer by wajeh3 Wednesday, February 20, 2013 9:35 AM
    Wednesday, February 13, 2013 7:17 PM

All replies

  • Hi,

    Try this, this will disable all remote access to local server

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



    Satheesh

    Monday, February 11, 2013 6:46 AM
  • Hi, Try this
    EXEC sys.sp_configure N'remote access', N'0'
    GO
    RECONFIGURE WITH OVERRIDE
    GO


    Satheesh

    Monday, February 11, 2013 6:47 AM
  • Thanks Mr Satheesh for your immediate reply

    this is the result statement after execution 

    "Configuration option 'remote access' changed from 0 to 0. Run the RECONFIGURE statement to install". As I understood I already unchecked "allow remote access to this server".Am I right?

    the problem is still exist. I can login from the other pc !!??

    the following captured frame is the login window in my pc , followed by login window in the remote pc 


    • Edited by wajeh3 Monday, February 11, 2013 7:03 AM
    Monday, February 11, 2013 6:56 AM
  • Hi,

    To disable clients from connecting to your SQL Server you need to disable TCP/IP protocol using SQL Server Configuration Manager.

    SQLServer Configuration Manager

    Make sure TCP/IP is disabled. You need to restart SQL Server for this change to take effect.

    Note: The 'remote access' setting is for Linked Servers and not client machines.


    - Vishal

    SqlAndMe.com

    Monday, February 11, 2013 10:59 AM
  • Note: The 'remote access' setting is for Linked Servers and not client machines.

    Not even that. It's related to an even older feature known as "remote servers".


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, February 11, 2013 10:42 PM
  • Thanks Mr. Visha and Mr. Erland for your useful information.

    I've tried to disable TCP\IP and restart the SQL server as you suggested but I couldn't access the database from the remote pc using the IP address of the my Pc where the database is hosted. What I want is to prevent login to the sql server where the database is hosted, while enabling access to the database from any client machine via .Net Application. The following captured frame show the error message after disabling the TCP/IP (this is in the remote PC where .Net application is installed and configured to access the data base via IP address as the second image shows)


    • Edited by wajeh3 Tuesday, February 12, 2013 10:15 AM
    Tuesday, February 12, 2013 10:15 AM
  • To connect to the Database Engine (on Server1) from a client (let's call it RemoteClient) you must enable either TCP/IP or named pipes on Server1, by using the SQL Server Configuration Manager. From your initial post, we thought you did not want to connect from RemoteClient, so you were told to disable TCP. But since you do want to connect from RemoteClient (I think) you should enable TCP/IP again. (I note that your error message mentions named pipes, so your client might need named pipes enabled instead.)

    You said you want to "prevent login to the sql server where the database is hosted". If you mean that SQL Server is installed on Server1 and you want to block access from Server1, that is not completely possible. Don't create logins for low privileged users on Server1 and they will not have access. But administrators of the Server1 computer will always have access.

    Sorry if I don't quite understand your scenario yet.


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

    Tuesday, February 12, 2013 4:54 PM
  • I'm so sorry if I mentioned something not clear. I have two PCs one is Server1 and the other is RemoteClient as you called them. In Server1, I installed SQL server where the db is hosted and  I already configured the SQL to allow access remotely. In addition the TCP/IP is enabled. In RemoteClient,  I installed .Net project aiming to access the db in Server1. Everything worked successfully even when I used Named Pipes. Up till now, everything goes without problems.

    The problem is that  once I installed the SQL server in RemoteClient, I was able to login to the SQL server In Server1 using its IP Address, which I think  is undesirable from  security's perspective ( any remote PC join the network wants to know only the IP address of Server1 to be able to login to the SQL Server and access db). I'm I right?

    Is it possible to prevent RemoteClient from Logging in to Server1 while enabling access to the db ?

    I appreciate your support and I'm Sorry again because my English is not that good.


    • Edited by wajeh3 Wednesday, February 13, 2013 10:07 AM
    Wednesday, February 13, 2013 10:05 AM
  • The problem is that  once I installed the SQL server in RemoteClient, I was able to login to the SQL server In Server1 using its IP Address, which I think  is undesirable from  security's perspective ( any remote PC join the network wants to know only the IP address of Server1 to be able to login to the SQL Server and access db). I'm I right?

    No - you are not right. It doesn't matter whether you connect by using the IP-Address or the name of server. Both is the same.
    The only thing you can do is..

    - start sql server browser
    - change the port for the default instance from 1433 to whatever
    http://msdn.microsoft.com/en-us/library/ms177440.aspx

    Than each client need to know the port of the sql server

    Your concerns about security are harmless because - and I think THAT is the missunderstanding - your user is not connecting to the server itself but only to the services of sql server and its databases. To configure database / server security look for this article:

    http://msdn.microsoft.com/en-us/library/bb669074.aspx


    Uwe Ricken

    MCSE - SQL Server 2012
    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)


    Wednesday, February 13, 2013 3:12 PM
  • The SQL Server Database Engine doesn't really care who or what is connecting to it. If the connection provides a valid Windows authentication login or a valid SQL Server authentication login name and password, then SQL Server is happy. Your .Net client can connect. SQL Server Management Studio can connect. Microsoft Access, Excel, or Word can connect. An ODBC client can connect. The opportunity to enable/disable remote protocols (named pipes and TCP) is a single on/off configuration. It can't be configured to work for some clients and not others.

    So what do you do?

    • You can switch to 3-tier architecture.
    • You can configure the firewall to only allow certain connections.
    • You can restrict applications to only running stored procedures with valid parameters so you don't care what client connects.
    • You can create a login trigger to evaluate what program the client says it is connecting from (but the client can lie).
    • You can use application roles (described in Books Online).
    • You can use USERS WITHOUT LOGINS as described in SQL Server 2005 Security Best Practices - Operational and Administrative Tasks

    What you need, really depends upon your business need.


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

    • Marked as answer by wajeh3 Wednesday, February 20, 2013 9:35 AM
    Wednesday, February 13, 2013 7:17 PM
  • your user is not connecting to the server itself but only to the services of sql server and its databases. To configure database / server security look for this article:

    http://msdn.microsoft.com/en-us/library/bb669074.aspx

      You'r the one who is right. thanks Uwe
    Wednesday, February 20, 2013 9:38 AM
  • Thank you all guys for you support

    Wednesday, February 20, 2013 9:41 AM