Configuring SQL Audit using the Audit Dynamic Management Views

In SQL Audit we added 2 Dynamic Management Views (DMVs) for use with reporting and configuration that I thought could use some more explanation and examples of how we intended they be used. The first is sys.dm_audit_class_type_map. Unfortunately the terms "class" and "type" are very overloaded in SQL Server metadata. In our permission model, we… Read more

How to create a SQL trace using T-SQL

Some users want to know if there is a way to monitor events on SQL server without using SQL Profiler. Yes, there is: the engine support behind SQL Profiler is the feature called SQL Trace which is introduced in SQL 2005. SQL Trace provides a set of stored procedures to create traces on an instance… Read more

Caregroup CIO Blogs about using Auditing

John Halamka, Harvard CIO, has blogged about the Caregroup Auditing project that was the basis for the Auditing portion of the Compliance SDK mentioned in my previous post. They did a lot of great work and we learned a lot from their feedback. And now everyone can benefit from their work through the sample code… Read more

SQL Server 2008 Compliance Guide

Denny Lee and JC Cannon have been hard at work producing a Compliance Guide for SQL Server 2008, including scripts and policy files. Great resource for anyone working on compliance with SQL Server…. Read more

SQL Audit Buffering and Error Handling

I’ve had several questions about how exactly the buffering and error handling works in SQL Audit and thought it would help to give some more detail. For starters, let’s break down the event firing workflow into the following stages: 1. Permission Check/Audit Check 2. Filling out the event 3. Distribute event to Audit Extended Event… Read more

SQL Server 2008 Security Whitepapers

I just wanted to call attention to a few SQL Server 2008 related security papers written or reviewed by our team: Engine Separation of Duties for the Application Developer – discusses how to build applications that support role separation. Database Encryption in SQL Server 2008 Enterprise Edition – in depth treatise on Transparent Database Encryption. Cryptography in… Read more

Accessing the calling context in modules that use EXECUTE AS

  In many occasions, marking a module (i.e. SP, trigger, etc.) with execute as can be really useful as it allows a controlled impersonation during the module execution; but at the same time there are many cases that it is necessary to access information using the caller’s execution context (i.e. revert to the default behavior),… Read more

Microsoft ® Source Code Analyzer for SQL Injection – July 2008 CTP

Today we have released an updated Community Technology Preview of Microsoft Source Code Analyzer for SQL Injection.     We made the following improvements based on community feedback: Included a GUI to view warnings generated by the tool. Downgraded the requirements to Microsoft .NET Framework 2.0 from 3.0. Improved the ASP parser and analysis engine… Read more

SQL Server and the Windows Server 2008 Firewall

We’ve long recommended that customers use the Windows Firewall to protect SQL Server installations. Starting with Windows XP/SP2, and continuing with Windows Vista, the firewall has been enabled by default on Windows client operating systems. Windows Server 2008 marks the first time this protection has been extended to a Windows Server OS. For those of… Read more

Getting started with Microsoft ® Source Code Analyzer for SQL Injection

Two days ago, we released Microsoft ® Source Code Analyzer for SQL Injection, June 2008 CTP which can analyze SQL injection vulnerabilities in Active Server Pages (ASP) code. In this blog, we will describe simple steps to help you start using the tool quickly.   1. Download the tool from Msscasi_asp_pkg.exe is a self… Read more