Deny SSMS connections from remote servers

Question

Post reply

Deny SSMS connections from remote servers

will75

Mr or Mrs. 500

Points: 575
More actions
November 20, 2014 at 9:19 am

#397668

Is there any way I can deny connection from SSMS only from remote servers?
thanks!
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

SQL_Baby SSC Eights! Points: 827 More actions · Answer 1

see this link

http://iskrov.info/2013/11/how-to-disable-remote-access-microsoft-sql-server/

will75 Mr or Mrs. 500 Points: 575 More actions · Answer 2

This will deny all connection to SQL server regardless where they are from. I just want to deny the connection from SSMS from remote servers.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

will75 (11/20/2014)
Is there any way I can deny connection from SSMS only from remote servers?
thanks!

No, it is a normal connection like any other, and the only identifier within the connection is Application Name, which can easily be changed by someone wanting to bypass any restriction on that field.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

SQL_Baby SSC Eights! Points: 827 More actions · Answer 4

Ahh I see what you mean now. Maybe you can look into Server Logon Triggers which you can set to trigger on every connection to your server. There you might be able to filter on a combination of application name (APP_NAME()), host (HOST_NAME()) or IP Address (client_net_address from sys.dm_exec_connections) and kill the session if your filter does not match.

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 5

have a look at my little article:

Scope: The drastic caveat with Logon Triggers. [/url]

It will get you on the right track.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

SQL_Baby (11/21/2014)
Ahh I see what you mean now. Maybe you can look into Server Logon Triggers which you can set to trigger on every connection to your server. There you might be able to filter on a combination of application name (APP_NAME()), host (HOST_NAME()) or IP Address (client_net_address from sys.dm_exec_connections) and kill the session if your filter does not match.

Except the APP_NAME() comes from a free text part of the connection string on the client, so can be changed to anything you like.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

SQL_Baby SSC Eights! Points: 827 More actions · Answer 7

mister.magoo (11/21/2014)
SQL_Baby (11/21/2014)
Ahh I see what you mean now. Maybe you can look into Server Logon Triggers which you can set to trigger on every connection to your server. There you might be able to filter on a combination of application name (APP_NAME()), host (HOST_NAME()) or IP Address (client_net_address from sys.dm_exec_connections) and kill the session if your filter does not match.
Except the APP_NAME() comes from a free text part of the connection string on the client, so can be changed to anything you like.

I am just presenting this as an option hoping that employees of the company concerned wont go to such lengths simply to work around the security. You'd hope they value their jobs more! At the very least this should work for someone who is innocently connecting via SSMS with no malice intended.

But you are right its important to note this workaround.

🙂