Saturday, February 26, 2011

Auditing SELECT Statements in SQL Server 2008 Standard using SQL Trace

Background

This procedure walks you through the process of scripting and implementing a very basic stored procedure for auditing SELECT queries against a SQL Server database.  It does not implement any practical structured error trapping which is reserved for a future article.  A copy of the entire script used in this article is available in the Resources section.  You will use SQL Profiler to draft the framework for the script and then you will use SQL Query Editor to finish it up and implement it.  Let's get started.


Procedure
  1. Start SQL Server Management Studio
  2. From the Tools menu select SQL Server Profiler. A Trace Properties dialog will appear.
  3. Enter a name for the trace
  4. Check Save to file.  A file Save As dialog appears.  Navigate to the directory in which you want to store the output from this trace; enter a file name and then click Save.
  5. Check Server processes trace data.  This completes configuration of the General tab.
  6. On the Trace Properties dialog select the Events Selection tab.
  7. On the Events Selection tab uncheck everything.
  8. Check Show all events  and Show all columns.
  9. Scroll down the list of event groups until you come to the TSQL one
  10. Expand this event group to see its members
  11. Enter a check into SQL: StmtStarting.  You may need to uncheck and then recheck it in order to have all of this event's properties also checked.
  12. Scroll across the list of columns until you see the TextData column.  This property will contain the text of a SQL query triggering this event.
  13. On the Events column, select the SQL: StmtStarting event so that it is highlighted (as in the figure above).
  14. Now click Column Filters.  The Edit Filter dialog appears.  The left pane lists all of the properties associated with the event you selected. 
  15. Scroll down this list until you come to the TextData property.
  16. Select the TextData property.  The right pane updates to show you the filters that you can apply.

  17. Open up the Like filter and then enter: %SELECT%
  18. Click OK.  The Edit Filter dialog closes
  19. Click Run.  The Trace Properties dialog closes and a new dialog appears listing events associated with the trace you created.
  20. In Microsoft SQL Server Management Studio select a table from any database, and then view the top 1000 rows (right-click on the table...).  Now go back to the trace list.  Scroll across until you see the TextData property.  All of the events listed here involve the use of SELECT queries.
  21. On the SQL Server Profiler menu choose File > Export > Script Trace Definition > For SQL Server 2005 - 2008 R2.  A Save As dialog appears.
  22. Navigate to where you want to save this script give it a name and then click Save.
  23. Stop the trace and then exit SQL Server Profiler.  You're all done with this tool.  Now go back to SQL Server Management Studio.
  24. From the File menu select Open > File with New Connection... An Open File dialog appears.
    Navigate to where you saved your script (the file ends with .sql) select it.
  25. Click Open.  A new Connect to Database Engine prompt appears.
  26. Enter the appropriate authentication details and then click Connect.  The script appears in a new query tab. Hang in there - you're almost done.  Now let's do a little editing and cleanup.
  27. Remove the filter on SQL Server Profiler statements.  You'll see it right after the one for the SELECT statement.  This filter isn't really needed and serves no useful purpose.  Just delete the whole line.  Now let's build a better filename generator.
  28. Add the following script just after the BEGIN statement.

  29. Update the create trace stored procedure line with the new filename. 

  30. These few lines of script you added create a new trace file every time the trace is started (such as after a shutdown). Now let's turn this into a new stored procedure.
  31. In Object Explorer open up the master database tree then open up Programmability, and then Stored Procedures.
  32. Right click on Stored Procedures and then select New Stored Procedure.  A new Query Editor tab appears.  In this Query Editor is a template for creating a new stored procedure.  This needs a little editing.
  33. Delete the first comment block (it's going to be removed anyway).  You can remove the first SET statement here as our trace procedure won't be performing any SELECT queries; and you can remove the second SET statement as we'll be using standard identifiers (single quote). Then add a few more lines like those shown below.  The reason being that we want this stored procedure to be applied to SQL: StmtStarting events generated by any database, and thus you need to associate it with the master schema.  The next thing to add is a quick check to make sure this procedure hasn't already been created: Fill out the second comment block as appropriate. 
  34. Now copy everything from your trace script and then paste it in between the BEGIN and END statements in the CREATE PROCEDURE template.  You should have something like that illustrated in the figure below.
  35. Remove the comment block just after the BEGIN statement.  This was entered by SQL Profiler and serves no useful purpose.

  36. Click the Parse button, located on the toolbar.  The Results window appears below the Query Editor window.
  37. Double-click the first line that you see in the Results window.  The Query Editor window scrolls down to the location of the error associated with that line.
  38. Remove the go statement just after the finish: statement.  This is what's causing the parse error. Once you remove this go statement, click Parse again.  The parse will complete successfully.
  39. Click the Save button in the toolbar.  The Save As dialog appears.  Enter a name for the trace and then click Save.
  40. Click the Execute button on the Management Studio toolbar.  This creates the stored procedure.
  41. In Object Explorer, right click on any folder and then select Refresh.
  42. In Object Explorer, open up the tree to master/Programmability/Stored Procedures.  The new stored procedure you just created will be listed below the System Stored Procedures.  Good Job!  I bet you didn't think you could get this far.  Now, the last task is to have SQL Server run the stored procedure and configure SQL Server to automatically start this procedure on startup.
  43. Open a new Query Editor window.
  44. Enter the SQL statements shown below.
  45. Click the Execute button.  A new Results window appears below the script presenting the Trace ID of the trace that was started.  In this case the trace has an ID of 2.
  46. Go to the directory in which this trace file was created and you should see it listed there.

  47. Now let's test our script.  If you shutdown and restart your SQL Server instance, a new trace file should be created.
  48. Shutdown and restart SQL Server, and then refresh the directory window where the trace file is being written to.  A second trace file appears.
  49. The next and final test is to see what events are captured by the trace.  To do this simply execute a read against any table. 
  50. Right-click on a table, and then choose Select Top 1000 Rows.  This executes a SELECT query against the selected table.
  51. Double-click on the trace file to start SQL Server Profiler.  In a results window you will see listed all of the events that were captured.  All of these events should involve a SELECT query, as shown in the figure below.
  52. Congratulations!  You have successfully created a script that captures all events involving any reads of any database in your Microsoft SQL Server instance.
  53. A copy of the entire script is available below.
Notes
  • The method discussed here can be applied to any TSQL query that you may want to audit. 
  • I've used SQL Profiler as a tool to build some of the script for me but you can of course script everything out manually in SQL Query Editor.  SQL Profiler is a good tool to use if you're just starting out with scripting traces, since it scripts most of the significant parts for you.  The only thing it doesn't do for you is implement some good structured error trapping, but you can do this yourself using the Try... Catch structure.
References
Resources

USE master
GO
IF OBJECT_ID('dbo.csp_MyAuditTrace') IS NOT NULL
DROP PROC dbo.csp_MyAuditTrace
GO

-- =============================================
-- Author: Al's Tech Tips
-- Create date: 02/26/2011
-- Description: Audits use of SELECT queries
-- against any database
-- =============================================
CREATE PROCEDURE dbo.csp_MyAuditTrace

AS

BEGIN

-- Set the filename for trace data capture
DECLARE @FileName nvarchar(256)
DECLARE @MyDateTime varchar(30), @instanceroot nvarchar(256)
SET @MyDateTime = CONVERT (nvarchar (30) , GETDATE(), 20)
SET @MyDateTime = REPLACE(@MyDateTime, '-','')
SET @MyDateTime = REPLACE(@MyDateTime, ' ','')
SET @MyDateTime = REPLACE(@MyDateTime,':','')

-- Get the instance specific LOG directory
-- Get the instance specific root directory.
SET @instanceroot = ''
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @instanceroot OUTPUT

IF @instanceroot = '' OR @instanceroot = NULL
BEGIN
-- Exit the procedure
raiserror ('Could not obtain the instance root directory using xp_instance_regread.',
18,127)
return(1)
END

-- Prepare the Trace file.
IF SUBSTRING(@instanceroot, Len(@instanceroot)-1, 1) != '\'
SET @instanceroot = @instanceroot + '\'

SET @FileName = @instanceroot + 'startuptrace_' + @MyDateTime + '.TRC'

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
exec @rc = sp_trace_create @TraceID output, 2, @FileName, @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 40, 7, @on
exec sp_trace_setevent @TraceID, 40, 55, @on
exec sp_trace_setevent @TraceID, 40, 8, @on
exec sp_trace_setevent @TraceID, 40, 64, @on
exec sp_trace_setevent @TraceID, 40, 1, @on
exec sp_trace_setevent @TraceID, 40, 9, @on
exec sp_trace_setevent @TraceID, 40, 41, @on
exec sp_trace_setevent @TraceID, 40, 49, @on
exec sp_trace_setevent @TraceID, 40, 6, @on
exec sp_trace_setevent @TraceID, 40, 10, @on
exec sp_trace_setevent @TraceID, 40, 14, @on
exec sp_trace_setevent @TraceID, 40, 26, @on
exec sp_trace_setevent @TraceID, 40, 30, @on
exec sp_trace_setevent @TraceID, 40, 50, @on
exec sp_trace_setevent @TraceID, 40, 66, @on
exec sp_trace_setevent @TraceID, 40, 3, @on
exec sp_trace_setevent @TraceID, 40, 11, @on
exec sp_trace_setevent @TraceID, 40, 35, @on
exec sp_trace_setevent @TraceID, 40, 51, @on
exec sp_trace_setevent @TraceID, 40, 4, @on
exec sp_trace_setevent @TraceID, 40, 12, @on
exec sp_trace_setevent @TraceID, 40, 60, @on
exec sp_trace_setevent @TraceID, 40, 5, @on
exec sp_trace_setevent @TraceID, 40, 29, @on
exec sp_trace_setevent @TraceID, 40, 61, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%SELECT%'

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:



END
GO

Thursday, February 3, 2011

Windows SharePoint Services Search Failure and Errors 10036 and 17836

Introduction

This procedure provides one resolution to a failure in Windows SharePoint Server 2007 Enterprise Search along with the appearance of Errors 10036 and 17836 in the Windows Server 2003 Enterprise Edition Application Event log.  It provides some general background for the error and the steps that were taken to successfully resolve it.

Background

An unexpected power outage led to the abrupt shutdown of a blade server hosting a customer SharePoint 2007 instance.  Both the SharePoint 2007 instance and its SQL Server instance were co-located on this blade.  The outage occurred over a weekend.  The blade server was restarted the following business day, and the SharePoint instance restarted automatically, presenting all pages normally.  In all ways, the SharePoint instance appeared to be fully operational.  A few business days later, a user attempted to perform a search and immediately experienced an error page.  Further attention by technical support verified the user experience and the matter was placed under investigation.

Review

SharePoint Shared Services was found to be running.  The crawl record showed that crawls executed previous to the outage had completed successfully without unexpected errors.  Crawls were configured to execute daily.  No crawls were logged after the power outage.  A manual crawl was attempted but was unsuccessful.

A review of the Windows System Event log found no unexpected errors.  A review of the Windows Application Event log identified the appearance of several unexpected warnings and errors:
  • Error 17836: MSSQLSERVER
  • Error 10036: Office Server Search
  • Error 10036 Windows SharePoint Services 3 Search
  • Warning 2423: Windows SharePoint Services 3 Search
  • Warning 2423: Office Server Search
These errors and warnings appeared at the time of the outage but did not recur.

A review of Windows Component Services found the following:
  • Office SharePoint Server Search | Started | Automatic...
  • Windows SharePoint Services Search | [Stopped] | Manual...
These findings suggested that there was database corruption, specifically the search index database.  Given these findings, the following resolution steps were performed.

Method

The Office SharePoint Server Search service was restarted. The Windows SharePoint Services Search service was started and set to Automatic.  A search was then performed, in the SharePoint 2007 website, but was found to still be unsuccessful, returning an error page.

In SharePoint Shared Services, a Reset all crawled content was performed.  After the reset completed, a new crawl was initated manually. The crawl completed successfully.  A new search was then performed, in the SharePoint 2007 website, and this time it was completed successfully, returning expected results.  The issue was found to be resolved.

Summary

From review of the findings and resolution steps, the following is our best understanding of the sequence of events involved.  A power outage apparently led to a corruption in the SQL Server database servicing a SharePoint 2007 instance.  The specific database corrupted involved the SharePoint search index database.  As a result of the search index corruption, a routine scheduled crawl failed, leading to a shutdown of the Windows SharePoint Services Search service, which stopped further crawls being performed.  The stoppage of the SharePoint Services Search service caused users to experience an error whenever searches were performed.

References

Tuesday, February 1, 2011

SharePoint 2007 Updates and Versions Since SP2

Chronologically tabulated below are cumulative updates for SharePoint Server 2007
as of 2/1/2011. Please see important notes, listed after this table.  The table below updates an earlier effort made at SharePoint Musings.
Update NameWSS 3.0MOSS 2007Release DateVersion No.
December 2010 Cumulative Update (CU)KB245860612/30/201012.0.6550.5002
KB245860512/14/201012.0.6550.5000
October 2010 Cumulative Update (CU)KB241226810/26/201012.0.6548.5000
KB241226710/26/201012.0.6548.5000
August 2010 Cumulative UpdateKB22764748/31/201012.0.6545.5001
KB22764728/31/201012.0.6545.5001
June 2010 Cumulative UpdateKB9833116/29/201012.0.6539.5000
KB9833106/29/201012.0.6539.5000
April 2010 Cumulative UpdateKB9810434/27/201012.0.6535.5000
KB9810424/27/201012.0.6535.5000
February 2010 Cumulative UpdateKB9783962/23/201012.0.6529.5000
KB9783952/23/201012.0.6529.5000
December 2009 Cumulative UpdateKB97702712/15/200912.0.6524.5000
KB97702612/15/200912.0.6524.5001
October 2009 Cumulative UpdateKB97498910/27/200912.0.6520.5000
KB97498810/27/200912.0.6520.5000
August 2009 Cumulative UpdateKB9734008/25/200912.0.6514.5000
KB9733999/9/200912.0.6514.5002
June 2009 Cumulative UpdateKB9715386/30/200912.0.6510.5003
KB9715377/15/200912.0.6510.5003
April 2009 Cumulative UpdateKB9688504/30/200912.0.6504.5000
KB9688514/30/200912.0.6504.5002
Service Pack 2 KB9533384/24/200912.0.0.6421
KB9533347/29/2009?

Notes

  • The version numbers presented here are those found in the associated Microsoft
    Knowledge Base articles. Note that these differ somewhat from those actually
    displayed in SharePoint, which includes an additional "0", but does
    not include the last version number block. For example, version "12.0.6545.5001"
    shown above will actually be shown in SharePoint as "12.0.0.6545."
  • The updates are often available separately for the x86 and x64 platforms.  Be sure to click the link, "Show hotfixes for all platforms and languages (2)," to see both before entering a check and downloading.

References