Disable Connect To Sqlserver From Ssms though internet.

Question

Disable Connect To Sqlserver From Ssms though internet.

mrsiro

Old Hand

Points: 376
More actions
December 12, 2019 at 1:56 am

#3707225
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 4 years, 4 months ago by mrsiro.

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

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

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: 398690 More actions · Answer 2

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

mrsiro Old Hand Points: 376 More actions · Answer 3

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: 99126 More actions · Answer 4

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.
Larnu.uk

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

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

sys.dm_exec_connections to see whether the IP address comes from the Internet.
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 Old Hand Points: 376 More actions · Answer 6

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.

as_1234 SSCrazy Points: 2842 More actions · Answer 7

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: 24038 More actions · Answer 8

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: 99126 More actions · Answer 9

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.
Larnu.uk

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 10

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning