I recently saw a post on StackExchange where a user was having a problem with tempdb filling up, which was causing their logon trigger to fail.
The logon trigger was attempting to prevent users from connecting with SQL Server Management Studio (SSMS). It seems that the user was not aware that it is trivial to connect using SSMS to SQL bypassing the app level restriction in place.
Let’s look at an example.
First we’ll create the logon trigger that explicitly looks to see if the application attempting to logon is management studio:
CREATE TRIGGER [DenySSMSLogin] ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN SET NOCOUNT ON; DECLARE @app SYSNAME = APP_NAME(); IF @app LIKE N'%Management Studio%' BEGIN THROW 51000, 'Connection not allowed.', 1; END; END; GO ENABLE TRIGGER [DenySSMSLogin] ON ALL SERVER; GO
Now we can test this by opening a new window. We should see an error:
Looks like it’s working just fine.
So let’s circumvent this, try connecting again, this time we just need to edit the Additional Connection Parameters in the connection dialog and pass along a new application name.
And now the connection will work just fine, completely bypassing the trigger (if trying this, don’t forget to disable your trigger when done).
SirSQL.net 
Yeah, app spoofing has always been a problem. I like to use this to weed out the largest population of my users, the ones who’ll probably be on there causing the issues to begin with. But because you never want to rely on the stupidity of your opponent, I also like to check against a list of approved servers too. So only let apps with this name connect if they’re coming from these servers.
I can’t always do that, but when I can, it makes it harder for them to connect if they can’t get to that server.
Better still, is to limit the apps that ARE allowed to connect. This way, they’d have to know exactly what the text in sysprocesses is and that could be harder for them to find out.
At one shop, we had devs putting custom app names in their strings, and they had to be prefixed with ‘Approved:’ and then the app name. We worked closely with them and this made it easy for us to see what was supposed to be there, which were apps, etc. Not enough people use app names to their advantage. Hell, I even do it in SSIS so I can tell when a given data pull is causing issues or taking too long. This way I can see it in the DMV and know where it’s coming from.
LikeLike