With the release of SQL 2008 Express I thought it would be helpful to add a note that it you try to use the ExpressMaint utility to perform database maintenance operations against SQL Server 2008 on a computer which has never had SQL Server 2005 installed you will encounter an error. This is because ExpressMaint references the SQL 2005 SMO assemblies. In order for it to work correctly, simply install the SMO 2005 redistributable on the SQL Server 2008 computer. I am currently working on a new version that will remove the SMO dependency and support all versions from SQL Server 2000 upwards as well as adding a new graphical UI and the concept of packages (think maintenance plans) to help simply maintenance operations. Download links below
A new build of SQL 2005 Service Manager is now available for XP, Windows 2003 and Windows 2008 (1.0.0.14) and Vista (2.0.0.6). This release adds a fix for duplicate SSIS services when both SQL 2005 and SQL 2008 are installed. Note that it is fully compatible with SQL 2008 but the name will stay the same.
Having successfully installed SQL 2008 on my laptop I was looking forward to a smooth install on my main PC however it was not to be. Having gotten pretty much to the end of setup it failed on the SQL 2005 Express Tools rule. Now I know I've never installed SSMS Express on this PC so I went digging through the logs. Once of the nice features of setup is an html log showing the results of all the checks carried out. In this case it merely confirmed that setup thought I had the Express Tools installed as shown below
After digging through the log files I came across one called Detail.txt which has details of the rules being run by setup. As can be seen from the log below, it was checking for the presence of the SQL Express Tools by querying the registry key SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM
Slp: Initializing rule : SQL Server 2005 Express tools
Slp: Rule applied features : SSMS;Adv_SSMS
Slp: Rule is will be executed : True
Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.Sql2005SsmsExpressFacet
Slp: Sco: Attempting to create base registry key HKEY_LOCAL_MACHINE, machine
Slp: Sco: Attempting to open registry subkey
Slp: Sco: Attempting to open registry subkey SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM
Slp: Rule 'Sql2005SsmsExpressFacet' detection result: SQL 2005 Ssms EE installed=True
Slp: Evaluating rule : Sql2005SsmsExpressFacet
Slp: Rule evaluation done : Failed
Slp: Rule evaluation message: The SQL Server 2005 Express Tools are installed. To continue, remove the SQL Server 2005 Express Tools.
Checking the registry I find that the key does indeed exist and in fact was added by the install of Redgate SQL Prompt 3.5 as shown below
Uninstalling SQL Prompt allowed the SQL 2008 installation to complete successfully (another option would probably have been to export the ShellSEM key, delete it, install SQL 2008 and then restore it).
So the lesson is, if you are getting tripped up by the Express Tools check and are sure you've never installed them, check the registry to see if some SSMS addin has added the key that setup is checking for.
Update Please note that this issue is only caused by older versions of SQL Prompt. If you have upgraded to SQL Prompt 3.9 you won't be affected by this issue. This is a free upgrade for users of SQL Prompt 3.x and is available from Redgate here with full support for SQL 2008
The basic version of SQL 2008 Express is not available for download, the other editions (SQL 2008 Express with Tools and SQL 2008 Express with Advanced Services) are coming soon. Don't forget you will need to have installed .NET 3.5 SP1 and Windows Installer 4.5 before installing SQL 2008 Express. Download links below
As mentioned in a previous post, parts of SQL 2008 require you to have Visual Studio 2008 SP1 RTM installed if you had previously installed Visual Studio 2008 prior to installing SQL 2008. Today, Visual Studio 2008 Service Pack 1 RTM is available for download so no more execuses for not installing SQL 2008! There is an excellent post by Bob Ward on the PSS SQL Engineers blog explaining all the various permutations of SQL 2008/VS 2008 here
Download links below
As you may have already seen elsewhere, SQL 2008 was released to manufacturing yesterday. View the official press release here. The good news is that all the editions except Express are available for download right now if you have an MSDN subscription (be warned each edition contains the x86,x64 and IA64 versions so they are 3 GB!). Happy downloading and congratulations to the whole SQL Team for shipping SQL 2008.
If you don't have a Technet or MSDN subscription you can download the 180 Day Trial Version here
Update Visual Studio 2008 SP1 RTM is now available (download links here)
Note Because parts of SQL 2008 requires VS 2008 SP1 (which has not yet been released) if you have VS 2008 installed you may encounter some issues during setup as explained in KB 956139. Since I have VS 2008 installed on all my normal PC's I'll be sticking with VM's for SQL 2008 until VS 2008 SP1 is released. For server installations this issue won't be a problem.
A new feature pack (August) is now available for the RTM release of SQL 2008. This allows you to download redistributable components of SQL 2008, add-on providers and backwards compatability components. No Report Builder 2.0 yet though
Microsoft have released a set of security updates for SQL 7, 2000 and 2005 to resolve four privately disclosed vulnerabilities. For details of the affected systems and vulnerabilities see Microsoft Security Bulletin MS08-040. Note that if you have already applied Cumulative Update 7 or higher for SQL Server 2005 SP2 then you already have these fixes as they were included in CU7 released in April.
An updated version (1.5.0.0) of the ExpressMaint utility for automating database maintenance operations for SQL 2005 Express Edition is now available. This new version includes the following additions and fixes
Download Latest Version and View Updated usage notes from sqldbatips.com
Download Source Code from Codeplex
A new build of SQL 2005 Service Manager is now available for Windows Vista (2.0.0.5). This releases adds compatability for SQL 2008 RC0 and filters the SQL Agent service from being managed for SQL 2008 Express Edition (see why here)
Now that Hyper-V is RTM (download update here) I figured I'd take the plunge and dual boot my laptop by installing a Windows 2008 x64 partition. All went well and I installed the Hyper-V role and the RTM update. When it came to starting up a VM however I found that it wouldn't start because the Intel-VT extensions were not enabled (they are Off by default). After quite a few scans through the BIOS settings I finally found the correct option (it's under POST Behavior which to me seems to be not very intuitive!). The instructions for enabling the required hardware settings for Hyper-V are below (Dell XPS M1730 with A06 BIOS)
Hope this helps, it took me a while to find this and searching google didn't help (in fact most of the dell forum posts seemed to indicate it required a BIOS update past A06 or wasn't possible).
A new build of SQL 2005 Service Manager is now available for XP, Windows 2003 and Windows 2008 (1.0.0.13). This releases adds compatability for SQL 2008 RC0 and filters the SQL Agent service from being managed for SQL 2008 Express Edition (see why here). Updated sourcecode is available from the Codeplex site. A new build for Vista will be available shortly.
If you have installed either of the Express Editions available in the SQL Server 2008 RC0 download you may have noticed that as well as the SQL Server service for the Express instance, setup also installs the SQL Agent service. Great news you might think but no, the service is installed in a disabled state and will error if you try and start it manually. This is a change in behaviour from SQL 2005 Express Edition which did not create the SQL Agent service (or even include sqlagent.exe) and is far from ideal and may be comfusing to customers as confirmed by Microsoft in my Connect Bug for this issue however it also won't be fixed in SQL 2008.
Why do I care (a) because I think its confusing for customers and (b) because I maintain the SQL 2005 Service Manager tool on Codeplex which despite its name does support SQL 2008 and this makes my life more difficult because I now have to check the instance editions and filter out SQL agent for 2008 Express instances (so for totally selfish reasons then!). Along with the reappearance of the FTS service under a new name in RC0 it's making adding SQL 2008 compatability more painful than I had hoped :-)
In RC0, the registry entry that specifies the message file for audit events that are written to the security event log is incorrect (Connect Item 352787). This means that when viewing audit events written to the security log in Event Viewer you will see the following text in the description (I'm sure you've seen these type of messages before!)
The description for Event ID ( 33205 ) in Source ( MSSQLSERVER$AUDIT ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event:
This should be fixed for RTM however to correct this in RC0, open regedit and browse to the following key (the example below is for a default instance, for a named instance the key format is MSSQL$<INSTANCE NAME>$AUDIT)
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Security\MSSQLSERVER$AUDIT
If you examine the EventMessageFile value you will notice that the path is similar to the one below (this is for a default instance in the default location, if you installed in a different location or have a named instance then the path will be different)
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\1033\Resources\sqlevn70.rll
The highlighted section is actually the wrong way round i.e. the path should actually be
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\Resources\1033\sqlevn70.rll
Once you have corrected this and restarted the SQL Server service then the spurious messages will no longer be displayed when viewing the audit events in the security event log.
In this post I'll cover how to create Database Audit Specifications to audit database level events. This post is part of a series so if you missed Part I you can find it here and Part II here.
Database Audit Specification
Database Audit Specifications define the audit action groups, individual audit actions and filters that you use to audit events at the database level and the Server Audit it belongs to. There can be a maximum of 1 Database Audit Specification per Database per Server Audit. You can create multiple Database Audit Specifications for a database as long as each one uses a separate Server Audit.
At the instance level you can specify one or more audit action groups (for Database Audit Specifications you can specify individual audit actions and filters as well). Note that actions that modify the audit itself (e.g. disabling or altering audit objects) are automatically audited. There are a large number of audit action groups, to find details on all of them check Books Online.
Example Walkthrough
In the following examples we will create Database Audit Specifications for the Adventureworks database using a new file based Server Audits (I won't cover creating the Server Audits in any detail, if you want more details on Server Audits see Part I). You will need the latest version of Adventureworks which can be downloaded from Codeplex here.
-
For the first example, we will create a Database Audit Specification to audit all DML on the Sales.CreditCard and HumanResources.EmployeePayHistory tables. First we will create a new file based Server Audit that we will use for our Database Audit Specification
CREATE SERVER AUDIT [AWDMLAudit]
TO FILE
( FILEPATH = 'C:\Audit\DB\Adventureworks\AWDMLAudit'
,MAXSIZE = 100 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 2000
,ON_FAILURE = CONTINUE
)
-
We will now create a new Database Audit Specification to audit the DML events for the Sales.CreditCard and HumanResources.EmployeePayHistory tables. Notice that as well as the audit action groups available in Server Audit Specifications, we can also specify individual audit actions and filter them based on individual objects and the user or role accessing them. In this example we will specify the public database role so that DML for all users is captured.
CREATE DATABASE AUDIT SPECIFICATION AWSensitiveDMLAudit
FOR SERVER AUDIT AWDMLAudit
ADD (SELECT , INSERT , UPDATE, DELETE
ON HumanResources.EmployeePayHistory
BY PUBLIC),
ADD (SELECT , INSERT , UPDATE, DELETE
ON Sales.CreditCard
BY PUBLIC)
WITH (STATE = ON)
GO
-
Now lets generate some events and then view the audit file to ensure that the events have been audited
USE AdventureWorks
GO
SELECT TOP 10 * FROM Sales.CreditCard
GO
BEGIN TRAN
UPDATE Sales.CreditCard
SET ExpYear = 2009,ModifiedDate = GETDATE()
WHERE CreditCardID = 6
ROLLBACK TRAN
GO
SELECT * FROM HumanResources.EmployeePayHistory
GO USE master
GO
-- get the audit file
DECLARE @filepattern VARCHAR(300)
DECLARE @folder VARCHAR(255)
DECLARE @auditguid VARCHAR(36)
SELECT @auditguid = audit_guid,@folder = log_file_path
FROM sys.server_file_audits WHERE name = 'AWDMLAudit'
SELECT @filepattern = @folder + '*_' + @auditguid + '*'
-- view the results
SELECT a.name AS Action,c.class_type_desc AS ObjectType,
f.server_principal_name,f.schema_name,f.OBJECT_NAME,f.statement
FROM fn_get_audit_file(@filepattern,NULL,NULL) AS f
JOIN sys.dm_audit_class_type_map c ON f.class_type = c.class_type
JOIN sys.dm_audit_actions a ON f.action_id = a.action_id
AND c.securable_class_desc = a.class_desc
WHERE f.action_id <> 'AUSC'
ORDER BY event_time DESC,sequence_number 
-
There are a couple of interesting points raised by the results. As you can see, the update statement is present (twice in fact since the table needs to be read to be able to qualify rows for the update which is why poth the SELECT and UPDATE appear for the UPDATE statement) even though the transaction it was in was rolled back. Also notice that the statement for the update is the auto parameterised version rather than the actual statement text. Database audits do not capture any before or after data images for DML actions.
-
Here are a few more examples demonstrating the additional filtering available for Database Audit Specifications. Note that if you try and create these without deleting the previous Database Audit Specification they will fail because of the limit of 1 Database Audit Specification per Database per Server Audit. Also note that in order to DROP or ALTER a Database Audit Specification if must first be disabled. You can of course simly alter the existing Database Audit Specification by adding the new audit actions and groups.
-- some more examples of database audit specifications
USE AdventureWorks
GO
-- audit all execution of stored procedures
CREATE DATABASE AUDIT SPECIFICATION Example1
FOR SERVER AUDIT AWDMLAudit
ADD (EXECUTE
ON DATABASE::AdventureWorks
BY PUBLIC)
WITH (STATE = ON)
GO
-- audit all updates in the Sales schema
CREATE DATABASE AUDIT SPECIFICATION Example2
FOR SERVER AUDIT AWDMLAudit
ADD (UPDATE
ON SCHEMA::Sales
BY PUBLIC)
WITH (STATE = ON)
GO
-- audit all schema changes in the database
CREATE DATABASE AUDIT SPECIFICATION Example3
FOR SERVER AUDIT AWDMLAudit
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO
-
As for Server Audit Specifications, there is built in tool support for Database Audit Specifications in SSMS. If you expand a database node in Object Explorer you will see the new Database Audit Specifications node. To create a new Database Audit Specification, right click on the Database Audit Specifications node and choose New Database Audit Specification as shown below
This will bring up the Create Database Audit Specification dialog which allows you to select the audit actions and groups and any applicable filtering
That brings us to the end of this introduction to the new built in auditing in SQL Server 2008. As you have seen, it is extremely easy to configure and administer via TSQL or SSMS. It is also designed to be extremely lightweight and have less overhead than the existing mechanisms (SQL Trace, DDL Triggers, Event Notifications).
All the code examples are attached to this post.
More Posts
Next page »