Thursday 30 June 2011

Admin: Securing your data with TDE

As SQL Server has developed over the past few version, security has been a significant area of improved both in the ability to lock down a server and to encrypt your data. In saying that, despite all the obvious benefits its something that I have rarely seen implemented on production systems. My guess is that a lot of legacy systems have been built on previous versions without the fancy features and it would take a significant development effort to introduce it.

However, one feature that can be harnessed and will give you a level of encryption is Transparent Data Encryption (TDE) introduced in SQL2008. The benefits of using this is that, theoretically speaking, it can just be turned on and your data is encrypted. Sounds great doesn't it? Well, it is but its perhaps not quite what you'd expect.

Lets delve into an example:

USE MASTER;
GO
-- we need a master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'BryanRiggs';
GO
-- and a server certificate
CREATE CERTIFICATE TestTDECert WITH SUBJECT = 'A Test TDE Certificate'
GO
-- and this will be our securable DB
CREATE DATABASE MySecureDB
GO
-- and we'll put in some sensitive data
USE MySecureDB
GO
CREATE TABLE dbo.Person
(Nm NVARCHAR(255), Phone NVARCHAR(50))
GO
INSERT INTO dbo.Person SELECT 'Andy', '07733403304'
INSERT INTO dbo.Person SELECT 'Mandy', '07970402401'
INSERT INTO dbo.Person SELECT 'Gandhi', '07652123957'
GO
-- we can obviously view this data
SELECT * FROM dbo.Person
GO
-- now we just need a en encryption key for the securable database
USE MySecureDB
GO
CREATE DATABASE ENCRYPTION KEY
WITH
ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TestTDECert
GO
-- look out for this warning

Warning: The certificate used for encrypting the database encryption key has not been backed up.
You should immediately back up the certificate and the private key associated with the certificate.
If the certificate ever becomes unavailable or if you must restore or attach the database on another server,
you must have backups of both the certificate and the private key or you will not be able to open the database.

-- now turn on TDE
ALTER DATABASE MySecureDB
SET ENCRYPTION ON
GO
-- but we can still view the data
SELECT * FROM dbo.Person
GO


So whats happened? Well, for me, the main crux of TDE is that it protects your data at the OS level. In other words, if you are connected a database which has TDE turned enabled, you can view the data within the tables in the same way as you can in a database which has TDE turned OFF. However, the value comes in that a malicious user can't steal your data by copying a backup file and restoring it to their local machine.

Lets look at that:



Essentially, because the database is encrypted the new server will not even accept a restore of the database. A similar error will occur if you just try and copy the MDF/LDF files and attach them to another server.

But of course, there are valid reasons why you would want to move your database to a new server and fortunately this is not too much of an arduous process. Remember, the error we got when setting up TDE? Well, that gives us a hint that our certificates should be backed up and its with these backups that we can create a valid database on an alternative server. So lets go through the steps:

-- on the source server, lets backup that certificate
USE MASTER
GO
BACKUP CERTIFICATE TestTDECert TO FILE = 'C:\SQLBackups\TestTDECert.cert'
WITH PRIVATE KEY
  
(FILE = 'C:\SQLBackups\EncryptionPrivateKey.key', ENCRYPTION BY PASSWORD = 'SECUREME')
GO
-- now copy this server to the remote server

-- on the remote server
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S3cur£Me';
GO
-- create the certificate from the copied backup cert
CREATE CERTIFICATE MyTDECert
FROM FILE = 'c:\SQLBackup\TestTDECert.cert'
WITH PRIVATE KEY (FILE = 'C:\SQLBackup\EncryptionPrivateKey.key', DECRYPTION BY PASSWORD = 'SECUREME')
GO
-- now we should be good to go
RESTORE DATABASE MySecureDB
FROM DISK = 'c:\SQLBackup\MySecureDB.bak'
GO


And there you have it. A quick demonstration of TDE and how it helps secure your data. Roy Ernest has written a more thorough post outlining on TDE over at SQL Server Central and this is definitely worth a read.

Thursday 23 June 2011

Denali: Won't Run on Windows XP

I've just come across the fact that the next version of SQL Server (Denali) will not run on Windows XP. At first, I was a little shocked but I guess it shouldn't be a surprise. After all, lest we forget that XP is now the n-2 version of Windows.

I suppose its a credit to Microsoft that Windows XP is in still in such widespread use and its quality has meant that a lot of companies are reluctant to upgrade, particularly with the issues surround Vista.

Personally though, it doesn't bother me that MS won't be supporting Denali on XP as typically I would be running the software on a Server OS such as Windows 2008/R2 athough I guess this may be different for editions such as Express.

For me, i'd be much more interested in knowing whether Denali tools were able to be installed on XP as this is often where I manage by SQL Servers.

SSRS: Full report not rendered via ReportExecutionService

I've come across an issue when using the ReportExecutionService to render a report through a .Net App using code similar to that demonstrated here.

Essentially, I'm trying to render a report which contains 3 layers of nested subreports all using different datasets. When viewing the report through the SSRS report viewer, you get a question mark on the paging section of the toolbar:



This question mark is a result of the On-demand processing engine introduced in SSRS 2008 and basically means that the report is rendered in stages, giving a potential performance gain on large reports. The question mark indicates that it knows there is more to come, just not sure exactly how much.

So, as I understand it, if you have a report with 2 subreports accessing different datasets - only the first one will be run to minimise the data sent to the client. When user starts navigating through the report and comes to the end of the first report, the rest of the report will be rendered. Rob Bruckner explains more here.

All well and good from a performance perspective and typically doesn't cause any issues apart from the strange question mark appearing (even this can be worked around) and users just navigate through the report. However, when trying to access it through the webservice, the same behaviour is honoured and only the first section of the report is rendered - equivalent to the first page when viewing through the SSRS report viewer. This is incredibly frustrating as I know that the report is over 3 pages long but I only ever get the first page in my deliverable. Worse still, there doesn't seem to be a way to override this behaviour (its the same in R2 I believe) and something i'd like to see addressed by MS (on a report by report basis).

At first, I was stumped as even using the TotalPages workaround referenced above, I couldn't get the full report to render. But, then I took a closer look a the "master" reports and noticed that they all had orphaned data sources defined - a legacy from when the reports held definitions rather than subreports. As a last shot, I removed these from the reports as they weren't needed at all and was delighted to see that the full report now rendered correctly through the webservice.

I'm not clear exactly why these orphaned connection strings were causing such an issue but all I know is that clearing them did the job.

Tuesday 21 June 2011

SSMS: Intellisense Won't Let Me PARTITION BY!

Warning!

This post is really just a rant inspired from a throw away comment in this blog post.

I write a lot of CTEs (because they're great!), often in conjunction with the PATITION BY clause. I'm using SQL2008 (although the behaviour is the same in 2008R2 and Denali) and get incredibly frustrated with SSMS when writing this type of query becuase it inevitably ends up with intellisense selecting PARTITION_FRAGMENT_ID. This is because hitting space bar selects the highlighted keyword.



I haven't found a way of working around this effectively other than hitting ESC to close the dialog and then continuing. Ho hum.

Thursday 16 June 2011

T-SQL: Excel 2007 & OPENROWSET

I do a lot of work with Excel spreadsheets and often need to import data from them on an adhoc basis. I typically use the Import/Export wizard to do this, although depending on how i'm feeling and the requirement I may go with one of the functions which gets data from a remote source such as OPENROWSET.

On this occasion, I was working with an Excel 2007 (.xlsx) workbook so I went straight to my favourite search engine to get a site which would remind myself of the syntax i'd need (how the internet makes one lazy on remembering syntax!) and it didn't disappoint. The first thing I noticed was that this wasn't using the old trusty Jet driver that I would use when working with Excel 2003. Using the Jet provider gives a fairly unhelpful:

OLE DB provider "Microsoft.Jet.OLEDB.4.0"FOR linked server "(null)" returned message "Unspecified error"
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"


Instead, you need to use the Access Database Engine driver:

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\ExcelImport\MyData.xlsx', 'SELECT * FROM [Sheet1$]')



So we're all set to get that data. Or so I thought as on executing the query, I got an error:

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".


The errors here are a bit of a red herring as the directory in question is open to Everyone. Also, it says that it is unable to get the column information but strangely, the column set was returned (albeit empty) in the results tab. Odd.

This problem can be averted by simply running the following queries to set some OLEDB properties:

USE [master]
GO

EXEC dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO

EXEC dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO



NB: a simple gotcha is remember to have your spreadsheet closed when trying to access it using this method otherwise you'll get another access denied error.

A couple of things I noticed but was unable to reproduce on a consistent basis, is that after running these queries, the excel spreadsheets appear to become corrupt for a length of time. Also, the changes did not always appear to be instant and on my machine it sometimes took 10 minutes for the behaviour to change.

Thursday 9 June 2011

T-SQL: Version your database with Extended Properties

Recently i've had a requirement to version a database build in a simliar way to AdventureWorks which uses a version table called AWBuildVersion. Of course, that is a perfectly acceptable option but as with all things SQL, there is more than one way to skin a cat. Extended properties are not a new feature to SQL Server but they might as well be for the number of times i've used them but the thought came to me that they could well be suitable for keeping version properties of a database.

Here is the code to generate the same database version info that is kept in the AdventureWorks build table.

-- add a version to your database
EXECUTE sys.sp_addextendedproperty @name = N'SystemInformationID', @value = N'1';
EXECUTE sys.sp_addextendedproperty @name = N'Database Version', @value = N'10.00.80404.00';
EXECUTE sys.sp_addextendedproperty @name = N'VersionDate', @value = N'2008-04-04';
EXECUTE sys.sp_addextendedproperty @name = N'ModifiedDate', @value = N'2008-04-04';

-- read out the version
SELECT *
FROM fn_listextendedproperty (NULL, NULL, NULL, NULL, NULL, NULL, NULL);


This returns:


Will I use this going forward? The thing with extended properties is that they aren't a widely used feature and having the data in a table makes it much more accessible. As a result, its much easier to remember the syntax to update a database table than an extended property. Also, a database table is more "visible" so is less likely to be forgotten when you come to update the version.

Thursday 2 June 2011

T-SQL: Enforce uniqueness with Filtered Indexes

A common design I see is tables designed with an Is_Live flag. I don't have any problem with this in general although it does throw up a slight issue when trying to enforce uniqueness within the table e.g when you're trying to ensure that there is only one "Live" record across a unique set of columns. Previous to SQL 2008 your only option was to use a trigger to enforce the uniqueness of the data but now you can harness the power of Filtered Indexes to achieve this goal.

Allow me to demonstrate:

CREATE TABLE dbo.Marriages
(
  
Husband NVARCHAR(255),
  
Wife NVARCHAR(255),
  
Is_Current BIT NOT NULL DEFAULT(0)
)
GO
INSERT INTO dbo.Marriages
VALUES
  
('Liam Gallagher', 'Patsy Kensit', 0), ('Liam Gallagher', 'Nicole Appleton', 1),
   (
'Dan Donovan', 'Patsy Kensit', 0), ('Jim Kerr', 'Patsy Kensit', 0), ('Jeremy Healy', 'Patsy Kensit', 0),
   (
'Andre Agassi', 'Brooke Shields', 0), ('Andre Agassi', 'Steffi Graf', 1),
   (
'Peter Andre', 'Katie Price', 0), ('Alex Reid', 'Katie Price', 0),
   (
'Prince Charles', 'Diana Spencer', 0), ('Prince Charles', 'Camilla Parker Bowles', 1)
GO
-- this fails as some husbands have multiple marriages
ALTER TABLE dbo.Marriages ADD CONSTRAINT UQ_Marriages_HusbandIsCurrent UNIQUE (Is_Current)
GO
The CREATE UNIQUE INDEX statement terminated because a duplicate KEY was found FOR the object name 'dbo.Marriages' AND the INDEX name 'UQ_Marriages_HusbandIsCurrent'. The duplicate KEY >value IS (0).

-- this succeeds because we're only interested in ensuring someone only has 1 current wife
CREATE UNIQUE NONCLUSTERED INDEX UIDX_Marriages_WifeIsCurrent_Filt ON dbo.Marriages(Wife, Is_Current) WHERE Is_Current = 1
GO
-- so now we can insert a new marriage
INSERT INTO dbo.Marriages SELECT 'Jay-Z', 'Beyonce', 1

-- but if I try and marry Beyonce...
INSERT INTO dbo.Marriages SELECT 'Richard Brown', 'Beyonce', 1

Cannot INSERT duplicate KEY row IN object 'dbo.Marriages' WITH UNIQUE INDEX 'UIDX_Marriages_WifeIsCurrent_Filt'. The duplicate KEY value IS (Beyonce,1).

-- (although I can be a divorcee!!)
INSERT INTO dbo.Marriages SELECT 'Richard Brown', 'Beyonce', 0
/* add this crazy stuff in so i can use syntax highlighter