SQL Server 2005 introduced an interesting and long demanded feature, the LOGON triggers. First only defined via server event triggers, it has become more and more popular in same scenarios to prevent users with a certain machine environment accessing the server. I saw that implemented in forums as denying special users the access during certain times, enabling maintenance windows for the administrators without taking the database offline (More about that in an upcoming blog).

But often I saw solutions where people wanted to prevent a certain application from accessing the SQL Server using the system function APP_NAME() to check the application name on the server side. (implemented on SQL Server Relational engine as well as Analysis Services) But as the following short example (can be downloaded here) shows, that this is just a small level of obfuscation for users who are not familiar with accessing technologies. The APP_NAME() will be evaluated from the application name the which is passed from the client stack (defined within the connection string) and which can be changed easily from a client configuration side (If the connectionstring resides in a config / XML / registry etc. and the user has access to that).

Let´s consider the following. A server / database administrator has decided not to allow custom develop applications (or in general apps that were develop using the .Net client stack to make the sample easier) to access the server, thus preventing the access via the following logon trigger:

 TSQL Code Logon Trigger to prevent certain applications

 

Testing this shows indeed that the server is rejecting the connection attempt:

 Error message shown when non-allowed application accesses the server

(BTW: Not showing the error number is by design, not to expose too much information to the user, the error number and the generic text itself shows the cause of the problem https://connect.microsoft.com/SQLServer/feedback/Vote.aspx?FeedbackID=237008)

 

But changing the App_Name in the connection string is quite easy, the little app from the sample does that by choosing an app which should be spoofed to the server (in that case anything beside the .Net client apps):

 Chaging the app nam from within the application

 

Profiling the event on the server shows that for the first case no Audit Login event is generated, but the other three connections could be made successfully.

 Profiler showing the connection attempt (if successfull)

 So make sure that you do not rely on the fact that the application name and other configurable options are secured from any spoofing.

 

-Jens