Earlier this year, I posted a short feature on auditing in SQL 2005 using DDL triggers to capture events and log them. In SQL 2008, one of the less sexy features added, though highly important and underscored by this current eras reporting and traceability debacles in public documents, is SQL 2008's new auditing features (less sexy from a populist point of view, I do note that there is probably an avid community out there with an audit fetish, so no intention of diminishing this feature's importance to you and yours).

Quite simply, it is implemented by the following sample syntax (full reference here):

One of the interesting switches you can use for auditing is the ON_FAILURE argument accepts parameters of CONTINUE or SHUTDOWN. If auditing is paramount for this system, then any failure in the auditing mechanism will turn the SQL Server service off. As auditing is SQL instance specific, this can be useful for many types of compliance scenarios.

The audit is set up at two levels; first, as shown in the script above, at the server level, which captures audit events (more on those events here).

The second level that can be implemented is at the database level. The script below is run within the context of the database I wanted the audit on, while the first script needs to be run within the context of the master database (full reference here):

This particular audit specification will capture all DDL executed against my particular database and make it part of the log.

For more, you can reference books line here for the Auditing home page for SQL 2008.

.NET makes it incredibly simple to build a site, and for new programmers, it can sometimes provide an unwarranted warm feeling that they are coding to an enterprise standard just by implementing certain aspects of the Enterprise Library, or properly tier-ing their application.

However, all is not well with the web world, as most of well know; many, many sites are exposed to basic SQL injection attacks due to failure in following some basic development principles.

For anyone wanting to read the basics, the Security Development Lifecycle team blog has posted an introduction to software architecture that reduces your attack surface area. Highly recommended for anyone new to web programming and using a database to serve their website; also a good refresher for old hands as well.

In addition, Microsoft has posted a source code analyzer for anyone wanting to run their web application through the ringer. Incredibly useful and highly recommended for anyone needing to learn more about web application security.

Just a quick footnote to go with the SQL Server 2005 Proxies: An Intro posting. It is important to note that any SSIS package that you will use the proxy configuration on, you will need the underlying credential account to have access to the WINNT\temp folder on the SQL server -- if you have script tasks in the package. During execution, a temp compiled file is created that represents the run time executable for your code, and that file is created in the WINNT\temp folder.

Hope that saves some people time!

I received a couple of questions following my post: Extracting SharePoint Data using SSIS, about how to set the package to run properly given that many WSS lists are secured by NT Group or list of accounts. This led right into a discussion on security and what you could now do in SQL 2005 that was not available in SQL 2000. Both SQL 2000 and 2005 use a proxy account to access resource and run the T-SQL subsystem, however in 2005, you can break out permissions more granularly. The screenshot below shows the different subsystems that are available in a typical SQL 2005 server:

Why go over this? To achieve our goal of pulling the list data from our WSS site and saving it as an XML file, we needed our scheduled job to execute with the proper permissions; if you remember, the line in our script task that set our credentials will inherit the credentials of the executing account – which by default is the SQL Agent Service Account. In my case, the process account running the SQL Agent service does not have permission to our WSS list. To get our script task to perform properly, one of my choices would be to request access to the list for the SQL Agent process account – this would work fine in some situations, however in larger environments, you end up with one account that has access to many, many different bits of information and you have not tiered information access and security in any way. What I will walk through is setting up a proxy that an SSIS package can use during execution in a scheduled job. Our end state will be the Run as selection box below having more than one option:

Our first step will be to create Credentials that our proxy will use. To do this using SMS, under ServerName > Security > Credentials, right click and select New Credential. Fill in the information for the account you will be using. You can also create the credential from SQL script: CREATE CREDENTIAL MyNewCredential WITH IDENTITY = 'TestUser', SECRET = 'TestUserPassword'.

After you have create the credential, in SMS expand the options below SQL Server Agent > Proxies. You will see the list from Figure 1. We are going to create a proxy for SSIS Package Execution: right click and select New Proxy. Notice that in the New Proxy Account window that you can select multiple SQL subsystems for a given proxy.

After you create the proxy, we will now have our selection in our Job Step as we stated in our goal. Note that the selections you made for which subsystems the proxy is active for will also determine when it shows in the Run As box below; in this example, we gave our proxy access to the SQL Server Integration Services Package subsystem, and thus we get the proxy as an option below as they Type matches. If the proxy does not have access to a subsystem that you have chosen as your type in the Job Step, then it will not appear as an available option for you.

This covers the basics on how and why you might want to create a proxy; be sure to post if you have any more detailed questions.