0

I am trying to disable SQL Server 2012 Management Studio remote access from other servers. I have unchecked SQL Server Properties -> Connections -> Allow remote connections to this server. but still users can access SQL Server database through SQL Server Management Studio remotely.

I have a client app on all network computers which needs to access the database. I just want to block SQL Server 2012 Management Studio remote access.

Thanks.

migrated from stackoverflow.com Nov 14 '15 at 22:37

This question came from our site for professional and enthusiast programmers.

1

Management studio communicates with SQL Server using the same interfaces as normal clients. You can modify user permissions so they can only read from certain tables, and can't write or alter.

  • I just need to disable MS SQL 2012 management studio remote access. if MS SQL 2012 management studio and client communicate with DB using same interface, then why there is Allow remote connections to this server option in SQL. – Khan Nov 7 '15 at 17:04
  • 1
    Disabling remote connections kills all clients, not just SSMS. – Chet Nov 7 '15 at 17:07
  • No it does not. both communication works perfectly. i just need to disable management studio access. – Khan Nov 7 '15 at 17:15
  • @Khan, the allow remote connections server property is related to remote servers, not clients. SSMS is a client application like any other. If you were to disable connections from SSMS only, clients could still connect using PowerShell, custom code or any other client. Permissions are the only way to limit what can be done. – Dan Guzman Nov 7 '15 at 20:19
0

a couple of clarifications and answers:

  • The "Allow remote connections to this server" option under the Server properties dialog in SSMS disables remote connections from remote servers, not clients (like SSMS)
  • You can achieve the desired effect of restricting database access to only your client app using either Logon Triggers or Application Roles.
  • The simplest option is to use Logon Triggers to prevent unauthorized logins from accessing SQL Servers. The Logon triggers are fired every time a new connection is established to SQL Server.

Example below (using Logon Triggers for an application with login "MyApplicationUser")

IF EXISTS(
    SELECT * FROM master.sys.server_triggers
    WHERE parent_class_desc = 'SERVER' AND name = N'Allow_only_Application_Login_Trigger')
DROP TRIGGER [Allow_only_Application_Login_Trigger] ON ALL SERVER
GO

CREATE TRIGGER Allow_only_Application_Login_Trigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN

DECLARE @data XML
SET @data = EVENTDATA()

DECLARE @AppName sysname
       ,@LoginName sysname
       ,@LoginType sysname
       ,@LoginDomain sysname
       ,@HostName sysname
SELECT @AppName = [program_name]
FROM sys.dm_exec_sessions
WHERE session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')

SELECT @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')
      --,@LoginDomain = @data.value('(/EVENT_INSTANCE/LoginDomain', 'sysname')
      ,@LoginType = @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'sysname')
      ,@HostName = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'sysname')

IF @LoginName not in ('MyApplicationUser','sa') 
    BEGIN
        ROLLBACK; --Disconnect the session
        --Log the exception to our Auditing table
        INSERT INTO master.dbo.loginAuditTable(data, program_name)
        VALUES(@data, @AppName)
    END 
END; 

Source: http://thesqldude.com/2012/04/07/how-to-prevent-users-from-accessing-sql-server-from-any-application-or-any-login-expect-your-main-application-its-login/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy