SQL Azure - Auditing Choices

As I am digging more into SQL Azure, it seems choices for auditing will become a little bit more restricted.

Generally speaking there are four ways to audit SQL Server statements; these mechanisms are used by various software vendors to deliver auditing capabilities for compliance mandates and for security reviews. However as we will see, many of the products will stop from working for SQL Azure due to some limitations imposed by the database.  At a high level, the four auditing mechanisms are:

  1. Server-side Traces
    Uses server-side traces to trace and store statements executed on a database server
  2. Log File Auditing
    Uses backups of log files to discover which statements were previously executed
  3. Network Sniffing
    Sniffing of network packets and storing SQL Server packets' content, including statements
  4. Database Proxying
    Captures all incoming network packets directly before forwarding them to the database server

It turns out that option number 1 is no longer available. Many SQL Server auditing products rely on this mechanism. Since server-side tracing stored procedures are no longer available, these products will not be able to audit SQL Azure statements.

Regarding option number 2, it seems that no option is available to obtain a backup of the log files. The BACKUP and RESTORE operations are not supported since cloud computing takes care of high availability concerns.

Regarding option number 3, packet sniffing will no longer work either since all direct connections to SQL Azure require SSL. As a result, all packets are encrypted and cannot be analyzed.

The only remaining option is to use a database proxy that handles the SSL handshake on both ends and stored the database statements going through. However, if a database connection can be made around the proxy, those statements will not be captured. While SQL Azure allows firewall settings to limit connections (by IP), it would be difficult to prove from an auditing standpoint that the firewall settings were not altered over time.

At this point at least, there appears to be no real silver bullet for auditing a SQL Azure database; at least not yet...

Still, most applications using the SQL Azure platform will not likely store any sensitive data, initially. As the SQL Azure platform grows in its use, I would expect some of the options above to be enabled, or new options to become available.



Print | posted @ Saturday, November 7, 2009 4:24 PM

Comments on this entry:

Comments are closed.

Comments have been closed on this topic.