Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Restrict Remote SQL Management

Author  Topic 

fernandezangel
Starting Member

4 Posts

Posted - 2013-08-30 : 11:13:08
Hi, is there a way to deny any remote sql management, not only using the sql management studio, i mean every remote administration tool like heidisql. We only want to allow management from the server itself.

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-08-30 : 13:22:57
Have you tried a logon trigger? http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f8f9a96e-df17-4669-a5f1-e94f11999d1e/a-logon-trigger-to-deny-the-use-of-sql-server-native-tools-for-a-functional-accountnon-interactive

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

fernandezangel
Starting Member

4 Posts

Posted - 2013-08-30 : 15:45:38
Do you know if this will block connections made by a client-server application?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-08-30 : 15:47:25
It depends how you've coded the logon trigger. Make sure you test it before deploying to production.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

fernandezangel
Starting Member

4 Posts

Posted - 2013-08-30 : 15:48:49
The idea is to block any management connection, unless it comes from the server itself, but allow a client-server application
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-08-30 : 15:58:47
Yes, please see the link I posted. You will need to change it and then test it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

fernandezangel
Starting Member

4 Posts

Posted - 2013-08-30 : 15:59:39
Thanks!
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-09-01 : 06:14:50
Combining Logon trigger and EVENTDATA you can restrict to only allow certain ip addresses. Check this post for code: http://www.sqlserver-dba.com/2012/08/sql-server-restrict-sql-server-logons-by-ip-with-eventdata-and-sql-logon-trigger.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -