October 8, 2009 at 7:56 am
Hi,
I'm reading a book about SQL Server 2005 security, now i'm in the encryptation chapter.
I have some problems in understanding some concepts.
In the book i have read that each time i install a nwe instance of SQL Server , automaticaly a service Master Key is installed.
They say in the book that the master key encripts information like:
Linked server passowords
Connection strings
Account credentials
all database master keys.
They say too, that i need to backup the Service Master Key, when i install the SQL Server.
1) My first question is, for e.g, in my case i have several instances of SQL Server , so, each one should have it's own Service Master Key, even if they are in the same server?
(a default instance and 3 named instances)?
2) The second question is,i have read that this Service Master Key encrypts the connection strings. Do i need to specify anything in the connection string of my app, so that this connection can be encripted, or it's automatic?
I ask this, because in SQL Server 2000, when i connect , e.g, via SQL Server login , the informations goes in plain text through the wire.
I read that in SQL Server 2000 windows authentication was much more secure, because of this.
In SQL Server 2005, the SQL Server authentions does go anymore in text, throught the wire?
3) Other question that i have it's that i read in this same book that i need to do the backup of the Service Master Key.
If i do not use encriptation, do i really nead to do this backup?
Thank you
July 23, 2010 at 3:35 pm
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
July 5, 2011 at 10:25 am
Is there a way that we can automate this through maintenance task ?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply