November 20, 2014 at 9:19 am
Is there any way I can deny connection from SSMS only from remote servers?
thanks!
November 20, 2014 at 10:51 am
November 20, 2014 at 10:56 am
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.
November 20, 2014 at 6:12 pm
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);
November 21, 2014 at 1:00 am
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.
November 21, 2014 at 1:51 am
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
November 21, 2014 at 4:53 am
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);
November 21, 2014 at 7:27 am
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.
🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply