• I'm just getting into this myself, but here is what I understand:

    1) Yes, each instance has its own service master key, and each of them needs to get backed up separately, just like their system databases (master, msdb, model). Since this is basically for SQL Server internal use, leave each key different.

    If instances are in a cluster, I believe all members of the cluster need to use the same service master key.

    2) Apples and oranges. The applications have their connection strings that they should be encrypting on their own. The service master key is used to encrypt passwords used to create connection strings within the SQL Server Engine for Linked Servers; these connection strings are never seen by applications.

    As I understand it, with SQL 2005 the headers are encrypted when communicating with and between SQL Server engines. This includes the password when you use SQL authentication, making this more secure than with SQL 2000. [There is a connection option that allows you to have the entire packet encrypted, not just the header, if you need that.]

    3) I've been trying to find the answer to that question myself, since I'm working on a DR plan for our SQL Servers. I've seen that you should back up the Service Master Key and Database Master Keys for each database as soon as they are created. But I don't know when/if you need to restore them.

    I'm trying to find out if you need to restore the SMK if you ever have to restore the master database on another server (e.g., recovering from a seriously fried server). Also, do you need to OPEN MASTER KEY and ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY after the engine restarts, or does this happen automatically?

    Hopefully someone else can shed some light here.

    David Lathrop
    DBA
    WA Dept of Health