Disable SQL Server 2012 Management Studio remote access

Question

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.

Answer 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.

Answer 2

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/