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/