Disable Connect To Sqlserver From Ssms though internet.

  • mrsiro

    SSC Veteran

    Points: 273

    Hello guys, how to disable connect to sqlserver from SSMS that connect from some special computer or IP.

    My sqlserver can connect though internet, now i want ssms only can connect from lan network, don't allow connect from internet.

    And how to hide master database from special user. I create new user and deny permisson view any database, but this user can see master and temp database. I don't want that.

    • This topic was modified 2 months, 1 week ago by  mrsiro.
  • Erland Sommarskog

    SSC-Insane

    Points: 23843

    Exposing SQL Server on the internet is a bad idea, so that's where you start. As long as you have it exposed, connections through SSMS is possible. There is no way to (securely) control access by application.

    You cannot hide master or tempdb for any users. They are always exposed, and all users have access these databases, and they must have, or else things will not work.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Grant Fritchey

    SSC Guru

    Points: 396288

    You could set up your firewall (and please, tell me you have one) so that it only allows connections from specific IP addresses or IP address ranges, that would make exposing your SQL Server instance directly on the internet a little less scary. Also, change the default port to something else. That too would help. Generally though, as was already pointed out, this is a bad idea.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • mrsiro

    SSC Veteran

    Points: 273

    hello, my application with vb.net  connect to database though internet, that why ssms can connect though internet. So, i want increase security. I want to disable connect with ssms though internet (application can connect), ssms only connect in lan network with some specical IP. Can i do that ?

  • Thom A

    SSC Guru

    Points: 98515

    mrsiro wrote:

    hello, my application with vb.net  connect to database though internet, that why ssms can connect though internet. So, i want increase security. I want to disable connect with ssms though internet (application can connect), ssms only connect in lan network with some specical IP. Can i do that ?

    Instead set your Firewall so that only the IP that the VB.Net application is coming from can access the SQL Server port. Then  (yes) SSMS wil be able to connect from that IP too, but the Rest of the World won't be able to.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Erland Sommarskog

    SSC-Insane

    Points: 23843

    hello, my application with vb.net connect to database though internet, that why ssms can connect though internet. So, i want increase security. I want to disable connect with ssms though internet (application can connect), ssms only connect in lan network with some specical IP. Can i do that ?

    No, you cannot to that. The best you can do is to set up a login trigger that checks

    1. sys.dm_exec_connections to see whether the IP address comes from the Internet.
    2. app_name() that must be equal to what you set in the connection string of your application.

    But that is not going to stop someone from setting the connection string for SSMS to have the application name of the application, so it is only security by obscurity. And if you get the login trigger wrong, you may lock out yourself....

    A firewall as Thom suggested will be needed anyway.

     

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • mrsiro

    SSC Veteran

    Points: 273

    Use firewall for specical IP is not good, because department clients have no static IP which is change by ISP every week.

    And if application is asp.net mvc on web, use firewall as tom say that is not right.

  • as1981

    SSCrazy

    Points: 2617

    I am not a network security expert so please don't implement my suggestion without your own research. If the firewall provides VPN functionality then that maybe a suitable option? I think you can also run VPN's from Windows Server but I think that offers less protection than a separate firewall and is beyond my knowledge.

     

  • Erland Sommarskog

    SSC-Insane

    Points: 23843

    We don't know your exact context for this arrangements, but it sounds like you have users all over the place that connect to your database. Let's make this clear: this is a poor design.

    SQL Server should never be exposed on the internet, period!

    The best option would have been to design the application as a three-tier solution, with an application server that you expose on network. That application server could be a web server.

    At this point, that may not be a realistic option, since it is a complete redesign. But you could still consider requiring users to use a VPN connection.

    As a quick fix, you could use a logon trigger as I suggested, but it's not very secure, and I don't want help with a solution that I don't like, so I am not providing any example myself.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Thom A

    SSC Guru

    Points: 98515

    At this stage I have to agree; sounds like you need to invest in a VPN for your clients.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Grant Fritchey

    SSC Guru

    Points: 396288

    Total agreement with Erland & Thom.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply