Recently, I wrote a couple of posts around some of the best practices that should be followed when working with Connection Strings. In case you missed them earlier, they are linked below for your kind reference:

• Profiling Best Practice – Always append Application Name, Workstation ID
  
• How to test your Connection String using SSMS?

In these posts, I mentioned that the application name parameter (and connection strings in general) can be used to alter the behavior of an application. One of the follow-up questions I received was around this topic, and today, I would briefly demonstrate a practical use of this functionality.

How can you modify the application behavior by using the application name parameter of a connection string?

A common requirement that I have encountered in many home-grown enterprise systems which share the same database is to exhibit different auditing behavior based on which application or interface is inserting/updating a piece of data. Most legacy applications implement auditing via database triggers, and checking for the application name in these triggers is the solution to realizing this requirement. Today, I will explore with you how the APP_NAME() function can be used to alter the behavior of an application based on the application name set by the connection string.

Using APP_NAME()

Consider the following trigger that is executed whenever an Employee record is updated. The only purpose of this is to audit the changes made to the Employee record by applications other than the “MyEnterpriseProduct”. These updates could be through interfaces or through any other applications that the organization may be using.

This demo will walk you through using the APP_NAME() function, which returns the application name for the current session if set by the application, within a DDL trigger.

The following code creates an auditing table followed by an update trigger on the Employee table.

USE AdventureWorks2008R2

GO

–Step 01: Prepare the environment

—       : Create the auditing table

—       : Create the update trigger

IF OBJECT_ID(‘HumanResources.uEmployee’,’TR’) IS NOT NULL

BEGIN

    DROP TRIGGER [HumanResources].[uEmployee];

END

GO
IF OBJECT_ID(‘HumanResources.Audit’) IS NOT NULL

BEGIN

    DROP TABLE [HumanResources].[Audit];

END

GO
CREATE TABLE [HumanResources].[Audit] (AuditDate DATETIME,

                                       BeforeJobTitleValue VARCHAR(100),

                                       AfterJobTitleValue VARCHAR(100),

                                       ApplicationName VARCHAR(100)

                                      );

GO
CREATE TRIGGER [HumanResources].[uEmployee]

ON [HumanResources].[Employee]

FOR UPDATE

AS

BEGIN

    SET NOCOUNT ON;

    DECLARE @appName VARCHAR(100);

    SET @appName = APP_NAME();
    IF (@appName != ‘MyEnterpriseProduct’)

    BEGIN

        INSERT INTO [HumanResources].[Audit]

               (AuditDate, BeforeJobTitleValue, AfterJobTitleValue, ApplicationName)

        SELECT GETUTCDATE() AS AuditDate,

               deleted.JobTitle AS BeforeJobTitleValue,

               inserted.JobTitle AS AfterJobTitleValue,

               @appName AS ApplicationName

        FROM [HumanResources].[Employee] AS Employee

        INNER JOIN deleted ON deleted.BusinessEntityID = Employee.BusinessEntityID

        INNER JOIN inserted ON deleted.BusinessEntityID = inserted.BusinessEntityID

        WHERE Employee.BusinessEntityID = inserted.BusinessEntityID;

    END

END

GO

Now, using the methods described in the post “How to test your Connection String using SSMS?”, establish two connections from the SSMS to the test AdventureWorks database – one using the Application Name – “MyEnterpriseProduct” and other using “OtherApplication”. Execute the queries shown below using the respective connections:

–Step 02: Using a connection with Application Name = “MyEnterpriseProduct”,

—         update the Employee record

—         Confirm that no auditing has been performed

USE AdventureWorks2008R2

GO

–Chief Executive Officer

UPDATE HumanResources.Employee SET JobTitle = ‘CEO’ WHERE BusinessEntityID = 1;

GO

SELECT NULL AS AuditDate,

       NULL AS BeforeJobTitleValue,

       NULL AS AfterJobTitleValue,

       APP_NAME() AS ApplicationName

UNION

SELECT AuditDate,

       BeforeJobTitleValue,

       AfterJobTitleValue,

       ApplicationName

FROM [HumanResources].[Audit];

GO
–Step 03: Using a connection with Application Name = “OtherApplication”,

—         update the Employee record

—         Confirm that auditing has been performed

USE AdventureWorks2008R2

GO

–Chief Executive Officer

UPDATE HumanResources.Employee SET JobTitle = ‘Chief Executive Officer’ WHERE BusinessEntityID = 1;

GO
SELECT NULL AS AuditDate,

       NULL AS BeforeJobTitleValue,

       NULL AS AfterJobTitleValue,

       APP_NAME() AS ApplicationName

UNION

SELECT AuditDate,

       BeforeJobTitleValue,

       AfterJobTitleValue,

       ApplicationName

FROM [HumanResources].[Audit];

GO

Here’s what one would see in the two different connections:

Notice that in the second result set (in the connection with Application Name set to “OtherApplication”, the BeforeJobValue is set to “CEO”, indicating that the first query did actually update the data, but the activity was not logged.

Finally, it’s cleanup time!

–Step 04: Cleanup!

USE AdventureWorks2008R2

GO

IF OBJECT_ID(‘HumanResources.uEmployee’,’TR’) IS NOT NULL

BEGIN

    DROP TRIGGER [HumanResources].[uEmployee];

END

GO

IF OBJECT_ID(‘HumanResources.Audit’) IS NOT NULL

BEGIN

    DROP TABLE [HumanResources].[Audit];

END

GO

Conclusion

As can be seen in the demonstration above, we were able to change the behavior of the auditing mechanism built into the application based on the parameter (Application Name) supplied within the connection string. Connection strings are therefore very powerful, and I urge all readers to go back, explore and share the results of your experiments through your comments on this post.

Disclaimer: The above example is only intended to demonstrate the use of the system function APP_NAME() and should not be used as a guidance for designing an auditing solution. An auditing solution requires capturing many more parameters and is outside the scope of this discussion.

Until we meet next time,

Be courteous. Drive responsibly.