Safe and secure implementation of logon trigger in SQL Server 2014 Express edition? I've to implement the following requirement:
Access to SQL Server instance shall be allowed only from a C# application.
Users shall not be able to access any database (even those in which they have access) via SQLCMD, SSMS.
Access using SSMS shall be allowed only for logins that are sysadmin, serveradmin.
Connection from one specific host machine shall be denied.
Connections from a specific C# application shall be allowed.
Is it possible, secure and safe to implement this via logon trigger?
If yes, how can I implement it in a reliable manner.
In order to apply the logon trigger, is it necessary to be done via SSMS, only once to be activated.
In case something goes wrong how can I disable it?
You can try to do this...
CREATE TRIGGER CSharpOnly ON ALL SERVER FOR LOGON
AS
BEGIN
     IF APP_NAME() NOT IN (N'Your C# app_name() according to SQL Server')
     BEGIN
          RAISERROR('Please use sea sharp.', 16, 1);
          ROLLBACK;
     END
END

...but, since application name and other connection properties are easily spoofed, it is very easy for your users to bypass them - they can just tell SSMS, for example, that it should present itself as the same APP_NAME(). So, it all depends on your definition of &quot;in a reliable manner.&quot;
When things go south
In order to fix a bad trigger, you can bypass logon triggers entirely by using the Dedicated Administrator Connection (DAC) to connect and modify / disable the trigger. You can also keep your initial connection established when you create / enable the trigger - existing connections won't go through the trigger again unless they get disconnected.