Tatham Oddie

SQL 2005 and 2008 both have what’s referred to as an encryption hierarchy. The details of this are beyond the scope of this post, but in essence: we encrypt our data using a key. We need to protect our key somehow, and we don’t want to litter our stored procs with key passwords, so we use a certificate. We then protect the certificate with a database master key. This is in turn protected by the service master key which is finally protected by DPAPI, an operating system provided store.

The keys and certificates are stored within the database itself, but when we move the database to another server they can’t be accessed. This is because the new server doesn’t know how to decrypt the database master key and in turn can’t decrypt the keys and certificates we need to use.

Usually…

The database master key is always protected by a password. You would have had to provide this when you first created the key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mypassword'

When you create the key like this, SQL generates the key value and encrypts it with the supplied password before storing it. It also makes a second copy which is encrypted using the service master key, and this is the copy that is normally used. When you move your database to another server, the service master key protected copy can’t be loaded but the password protected copy can be.

 OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword' 

With the key now decrypted and loaded into memory, we can ask the new server to make a copy that is protected using the service master key.

 ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY 

Finally, we close the key to take it out of memory.

 CLOSE MASTER KEY 

Voila, you can now access your keys and certificates on the new server with automatic key management (that is, with SQL automatically opening and closing the database master key for you as required).

Read-only databases

The approach described above is dependent upon the database being in a writable state as it makes modifications of the database master key itself. What happens when we want to work with a read-only version of a database such as a snapshot or a mirror?

With automatic key management, SQL Server will first attempt to decrypt the database master key using the service master key. In a read-only database, we are unable to create a copy of the key that is protected in this way.

After attempting that, SQL Server will look in the credential store (sys.credentials) for any credentials related to the master key. It will attempt each credential it finds.

Adding our credential to the store is easy:

USE [master]
GO

EXEC sp_control_dbmasterkey_password
    @db_name   = N'mydatabase',
    @password  = N'mypassword',
    @action    = N'add';

You can see evidence of the new credential in both sys.master_key_passwords and sys.credentials:

SELECT  d.name as database_name,
        c.*,
        mkp.family_guid

FROM    master.sys.credentials c

        INNER JOIN master.sys.master_key_passwords mkp
            ON c.credential_id = mkp.credential_id

        INNER JOIN master.sys.database_recovery_status drs
            ON mkp.family_guid = drs.family_guid

        INNER JOIN master.sys.databases d
            ON drs.database_id = d.database_id

Voila, just like above, you can now access your keys and certificates on the new server with automatic key management (that is, with SQL automatically opening and closing the database master key for you as required).

You might also notice that the sys.master_key_passwords view ties a master key password to a family id as opposed to a database id. A family id is assigned when a database is first created and it stays the same even if the database is detached, moved, reattached, mirrored, etc. As a result of this behavior, you could have multiple databases on the one server that share the same family id. In contrast, a database id is created every time a database is attached and is therefore unique for every database instance on the server. In the context of our master keys, the outcome is that adding a credential against one database will actually add it for all of the databases which have come from the same original instance. Even if you detach and reattach your databases, or drop and restart replication, the credential will still be kept in the store and work with the new database instance. Attaching any new instances which share the same family id will also automatically inherit the credential. (Unless of course you change the database master key password in which case the credential will still be attempted but will fail and cause the next one to be attempted instead.)

As this is a suggestion which comes up quite regularly, I felt it valuable to document some of the research I have collected about the readability of light text on dark backgrounds.

The science of readability is by no means new, and some of the best research comes from advertising works in the early 80s. This information is still relevant today.

First up is this quote from a paper titled “Improving the legibility of visual display units through contrast reversal”. In present time we think of contrast reversal meaning black-on-white, but remember this paper is from 1980 when VDUs (monitors) where green-on-black. This paper formed part of the research that drove the push for this to change to the screen formats we use today.

However, most studies have shown that dark characters on a light background are superior to light characters on a dark background (when the refresh rate is fairly high). For example, Bauer and Cavonius (1980) found that participants were 26% more accurate in reading text when they read it with dark characters on a light background.

Reference: Bauer, D., & Cavonius, C., R. (1980). Improving the legibility of visual display units through contrast reversal. In E. Grandjean, E. Vigliani (Eds.), Ergonomic Aspects of Visual Display Terminals (pp. 137-142). London: Taylor & Francis

Ok, 26% improvement – but why?

People with astigmatism (aproximately 50% of the population) find it harder to read white text on black than black text on white. Part of this has to do with light levels: with a bright display (white background) the iris closes a bit more, decreasing the effect of the "deformed" lens; with a dark display (black background) the iris opens to receive more light and the deformation of the lens creates a much fuzzier focus at the eye.

Jason Harrison – Post Doctoral Fellow, Imager Lab Manager – Sensory Perception and Interaction Research Group, University of British Columbia

The "fuzzing” effect that Jason refers to is known as halation.

It might feel strange pushing your primary design goals based on the vision impaired, but when 50% of the population of have this “impairment” it’s actually closer to being the norm than an impairment.

The web is rife with research on the topic, but I think these two quotes provide a succinct justification for why light text on a dark background is a bad idea.

(Tip: If you want to be really good, use an offset grey on a light background like #222 on #fff as it’s a bit nicer on the eyes.)