Database connection auditing for Continuous Delivery

This post was written by Matthew Skelton co-author of Team Guide to Software Operability.

Continuous Delivery of software systems can be a struggle if a central database is accessed by multiple applications directly, due to the (perceived) increased risk and the need for coordination across different teams. In this post, we demonstrate some techniques for discovering the applications connecting to a database, so you can start to untangle the applications and truly enable Continuous Delivery.

It is a commonly used pattern for multiple applications and services to integrate at the database. While this is known to have some benefits (such as simpler applications as they don’t require a layer of integration services), it also tends to increase the complexity of change, therefore increasing the cost of change while reducing the speed at which we can deliver. It is this reduction in speed that most clearly impacts Continuous Delivery (CD). We must be able to deliver small changes quickly to enable fast feedback and small batch sizes.

For applications that currently integrate at the database, this article explores some options for discovering the identity of these applications, as a starting point on the journey towards Continuous Delivery, perhaps eventually using separate applications and services (with microservices or SOA). In this article, we show techniques for SQL Server, Oracle, and MySQL/MariaDB.

Viewing active database connections per application in SQL Server

It is usually at the time when the applications are in production and there are issues (such as blocking queries or heavy load) that we urgently start attempting to discover which applications are accessing the database. This is frequently painful, as we find that the applications are all using the same credentials. But that’s okay, because we can find out which application is accessing the database:

select session_id, program_name, host_name,
client_interface_name, login_name, nt_user_name
from sys.dm_exec_sessions
where session_id > 50

Unfortunately, this shows all the interesting connections as coming from .Net SqlClient Data Provider. The simplest way to solve this one is a quick change to the application configuration. Here is a typical SQL Server connection string:

<add name="CustomersDatabase"
connectionString="Server=.;Database=Customers;Trusted_Connection=True;"
providerName="System.Data.SqlClient" />

In order to track connecting applications more easily, we simply add in an additional parameter Application Name, giving it a readable name that we can recognise as a calling application or service (in this case, ‘CustomerService‘):

<add name="CustomersDatabase"
connectionString="Application Name=CustomerService;Server=.;Database=Customers;Trusted_Connection=True;"
providerName="System.Data.SqlClient" />

Now, we can easily see which applications are connected:

 

While we are editing these connection strings, I would highly recommend creating a different user for each application, which gives you a lot of gains around security and permissions.

Here is a short video that demonstrates the Application Name connection string parameter in action:

 

One thing to note – it is common for people to use the sp_who2 system stored procedure with SQL Server. This is an unofficial, undocumented stored procedure, which is not recommended for use. The official, recommended approach is to use the Dynamic Views and Functions, which were first available in SQL Server 2005.

Detecting distinct applications via connection strings in config files

This approach (of using the dynamic view sys.dm_exec_requests) is great for seeing live traffic that is hitting the database. You could use some method of audit logging, such as Server Audits, or traces via SQL Server Profiler (both explained extremely well by Craig Efrein in this StackOverflow answer). However if you have a limited number of application servers, you could easily use grep to find connection strings that do not include the Application Name parameter:

$ grep -rH 'connectionString=.*' . | grep -i '.config' | grep -iv 'application name='

./OrderService/Web.config: <add name="CustomersDatabase" connectionString="Server=.;Database=Customers;Trusted_Connection=True;" />

Auditing connections to Oracle and MariaDB/MySQL

This ability to detect which applications are connecting into a database is not specific to just SQL Server, and we can use equivalent techniques for Oracle and MySQL/MariaDB.

For Oracle, you can easily determine the program that is executing the query (select program from v$session), but if that turns out to be java.exe, then that’s not so helpful, as you’re likely to have many different Java applications connecting in. You can also determine the process id from the same view (select process from v$session), which can help track it down, but its still not super simple. The best approach is to use either distinct service names in the tnsnames.ora or potential distinct user accounts (though that comes with its own challenges).

SQL< select service_name
from v$session
where service_name not like 'SYS$%';

SERVICE_NAME
----------------------------------------------------------------
AUTH
CUST
ORDER

For MySQL / MariaDB, the best approach is to use a different user per application to access the database – an approach that will allow you restrict permissions on an object level.

 

Wrap-up – detecting applications connected to a database as part of Continuous Delivery

We have seen how, with some easy configuration file modifications, we can easily determine which applications are connecting to a database; a step that can provide valuable insights into the real life topology of the applications in production. With this information we can start the process of understanding applications’ boundaries and splitting up the shared resource, simplifying application development and deployment – an important part of Continuous Delivery.

Thanks to Matt R for help with this post.

 

To find out more about Continuous Delivery and databases, check out the book Database Lifecycle Management by Grant Fritchey and Matthew Skelton: