Security Logging with Service Broker

Service Broker, which was a new component in SQL Server 2005, provides many new ways of thinking about database applications. When used in conjunction with Microsoft IIS and other SQL Server technologies, it can provide the core architecture needed for distributed server-side applications, reliable query processing and data collection, large-scale batch processing, and data consolidation for client applications. Let’s look at data consolidation to see how the Service Broker technology can be used to implement a security monitoring and logging solution for any organization.

Components of a Service Broker Solution

A Service Broker solution consists of message types, contracts, queues, and services. Let’s review each of these objects briefly before moving on to the actual Service Broker solution we’re focused on implementing in this article.

A message type is a message category that’s used by an application. Defining a message type ensures that only messages that can be understood by the processing service can be submitted. Message types are similar to email messages in that they have a subject (the message type itself) and a body (the payload of the message). Message types are used in conversations between an initiator service and a target service.

A contract is an agreement between the initiator and the target. The contract defines the message type that can be sent by the initiator and the valid return that can be sent by the target.

The queue is the storage location for the messages that are intended for processing by a specific application. When messages arrive for a specific queue, Service Broker is responsible for placing the messages in that queue. You can think of the queues as storage bins for the messages waiting to be processed.

Finally, the service is the endpoint that the application uses to access the queue. The service specifies the queue and contracts that can be used to store information in and retrieve information from that queue. A service that doesn’t specify a contract can only be an initiator; it can’t be a provider or target.

One additional component is needed: A service program is simply an external application or an internal stored procedure that’s used to process the messages stored in the queues. If you don’t implement a service program, the messages will simply sit in the queue and never be processed.

Implementing the Service

Now, I’ll walk you through the implementation of a centralized security-incident logging solution. You’ll learn how to create message types, contracts, queues, and services, all of which are needed in the solution. You’ll also learn how to create simple Active Server Pages (ASP) code that calls a stored procedure to place information in the security logging queue for automated processing. ASP.NET would be the preferred solution in a production environment; however, I’ll use ASP in this example because it lets me keep the code simple all the way through. Finally, you’ll learn how to read messages from the queue and use stored procedures to process them.

The first thing you need to do is create a database for your incident logs. Create a very basic database named seclog by using the following simple command:

CREATE DATABASE   seclog;

Next you need to enable Service Broker for this database, which you can do with the following command:

ALTER DATABASE seclog  SET ENABLE_BROKER;

Finally, you must create a table for storing the security incidents after they’re processed by the service program. These commands will work for the simple table you need to create:

USE seclog;CREATE TABLE incidents (id INT PRIMARY KEY IDENTITY(1,1),  incident varchar(50));

Now that you have the database and table in place, you can begin creating the Service Broker objects. You’ll create them in this order:

The first step is to define a valid message type. The security logging solution will need to receive a message that contains the incident description to be entered in the incidents table. Call this message RequestMessage. You also need to create another message type for the response, although the actual implementation won’t use it. Call this message ReplyMessage. Listing 1 shows the code to create the message types.

Notice that the code validates that the payload contains well-formed XML. This simply means that all nested elements are closed properly. It doesn’t require the use of an XML schema. That would require the statement VALIDATION=VALID_XML.

Now that the message types have been created, you can create a contract that uses them by typing the command in Listing 2. The contract, named IncContract, specifies the message type of RequestMessage for sending by a conversation initiator. Notice that the code specifies a Reply-Message that can be sent by the target. We won’t be using that message type in our solution, but adding it to the contract allows you to use it at a later time with little effort.

Now that you’ve created the message type and the contract, you can create the queue for storing the incoming security incidents. You also need to create the service, which you can accomplish with the first pair of CREATE QUEUE and CREATE SERVICE commands in Listing 3. Because SQL Server 2005 supports only dialogues and not monologues, you have to create another queue and service that you won’t actually use (i.e., the second CREATE QUEUE and CREATE SERVICE pair in Listing 3). This technique lets you implement a monologue scenario with SQL Server 2005’s dialogue structure.

The Service Broker is now set up, but you also need a stored procedure to process messages dropped into TargetQueue. Listing 4 shows the code for the stored procedure. You’ll notice that the code doesn’t actually use InitiatorQueue. As I mentioned earlier, it exists to set up the conversation appropriately because SQL Server 2005 lacks support for monologues. Ultimately, the stored procedure takes the input of an incident description and outputs it to a SQL Server table.

At this point, you have the infrastructure in place to begin entering data in the queues; however, I want to show you how to create a simple ASP page that can be called to place an entry in the queue. Listing 5 shows some sample code that you could save in an ASP file and place on an internal IIS server. You’ll notice the simple way this ASP page can be called. It looks for a parameter called Description to provide the information related to the incident. For example, incident.asp?Description=some_code would work just fine.

Utilizing the Solution

With the infrastructure in place, any device can now report a security incident with a simple HTTP connection. For example, assuming the previous ASP code was saved to a file named incident.asp at the root of a Web server, you could enter the following URL

http://webserver/incident.asp?Description=  Unusual Firewall Activity

into a Web browser to add a new security incident.

Many OSs support command-line tools that you can use to execute HTTP GET commands (which is what a Web browser does when you enter a URL). As long as you can run a batch file when a security incident occurs on a Windows computer, you can add information to the incident log. You need only a client computer with a full TCP/IP stack and an application that can submit an HTTP request. The valuable UnxUtils collection available at unxutils.sourceforge.net includes a wget executable for the Windows command line. With this free utility, you can execute the following or a similar command:

wget http://webserver.com:80/ incident.asp?description= 405:10.10.10.43

Take note of how the description is formatted in the wget example. Instead of a descriptive phrase such as “Unusual Firewall Activity,” there’s a code (405) and the IP address of the source device. You’ll probably want to develop a set of standard codes for security events that occur in your organization. For example, the code 304 could represent a possible malware infection and the code 305 could represent a possible spam attack. Mapping wordy attack descriptions to simple numeric codes keeps your incident tracking database small and the HTTP commands simple.

That’s it! No need to install a SQL Server client utility, because you’re communicating with Service Broker via a stored procedure that’s executed through an ASP page on the IIS server. For many network administrators and IT employees, this functionality is phenomenally helpful. You could expand it well beyond just security logging; it could easily become the framework for centralized logging of any event categories that you must report. It could even save you thousands of dollars in client agent licenses for third-party applications. Of course, you’d need the proper SQL Server 2005 or later licensing and the sweat and tears to fully develop your solution.

You could enhance this logging solution to provide automatic notification of specified events based on risk-level IDs or the point on the network at which the incident occurred. This could be accomplished by using the Notification Services architecture or a custom-built solution that simply monitors the incidents table for the relevant entries. Additionally, you could create modules so that more devices could communicate with the database. For example, a solution could be built that receives SNMP alerts and forwards them to the security incident database when applicable.

The most obvious enhancement to this solution would be to take even greater advantage of the Service Broker component. You could implement an activation scheme that fires a stored procedure as soon as an item is placed in TargetQueue. This would mean that the automatic processing currently employed would be removed from the uspIncident stored procedure. The new stored procedure could still add the data to the incidents table, but it could additionally look for specific codes and then take predetermined actions such as launching external scripts that would shut down sections of the network or individual computers that might be problematic. Once you have the infrastructure in place, the hooks and branches that can be implemented are practically endless.

The ultimate goal of this article is to provide you with a real-world example of using Service Broker to solve a problem. I hope it helps you to begin thinking of even more and better ways to use this technology. I’d love to hear from you if you have ideas about how to expand this example.