Re: Encrypted database deployment

Hello Mike,

Agree with you that he shall be able to use ALTER SERVICE MASTER KEY
statment to change the master key of the server. However, changing SQL
server account might be more convenient to isolate the issue.

http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx

BOL 2005 states:
?The service master key is automatically generated the first time it is
needed to encrypt a linked server password, credential, or database master
key. The service master key is encrypted using the local machine key or the
Windows Data Protection API. This API uses a key that is derived from the
Windows credentials of the SQL Server service account.

The service master key can only be decrypted by the service account under
which it was created or by a principal that has access to the Windows
credentials of that service account. Therefore, if you change the Windows
account under which the SQL Server service runs, you must also enable
decryption of the service master key by the new account.

Regenerating or restoring the Service Master Key involves decrypting and
re-encrypting the complete encryption hierarchy. Unless the key has been
compromised, this resource-intensive operation should be scheduled during a
period of low demand.

Changing the SQL Server Service Account To change the SQL Server service
account, we recommend that you use SQL Server Configuration Manager. This
tool performs the required decryptions and encryptions, and configures
other settings that SQL Server requires. In situations in which SQL Server
Configuration Manager cannot be used to change the service account, you can
use the ALTER SERVICE MASTER KEY statement to make the service master key
available to the new account

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================


.

Relevant Pages

  • Re: An error occurred during decryption
    ... To confirm that the error happens with the service master key, ... ALTER SERVICE MASTER KEY REGENERATE. ... server login passwords, ...
    (microsoft.public.sqlserver.security)
  • Re: Linked Servers - setspn - domain account
    ... First of all I wouldn't use setspn, I tend to use ADSI Edit ... This is for a server called SQLNLB02 in the domain DOMSQL.COM ... I'm editing as it's SQL Service account). ... Jasper Smith (SQL Server MVP) ...
    (microsoft.public.sqlserver.security)
  • Re: SetSPN problem
    ... The SPN is on the service account object not the server ... Jasper Smith (SQL Server MVP) ...
    (microsoft.public.sqlserver.security)
  • Re: Kerberos w/ SQL and WIN2000
    ... I'm new to LDAP in general, but where would the ADSI key be located? ... > Win2000 will default to using kerberos to connect to the server anyway ... > For my server called sqlnlb01 in domain domsql.com using a service account ... > Jasper Smith (SQL Server MVP) ...
    (microsoft.public.sqlserver.security)
  • Service Account access fails
    ... After rebooting a windows 2000 server the SQL Server ... Access is prevent to the Ad-Ent Service Account, ...
    (microsoft.public.sqlserver.security)