As we all know we should run our SQL services with the least privileged principle. Most of the times this comes down to running with a domain account that’s not a member of any group. But we also want to use strong authentication mechanisms like Kerberos, so we need a domain administrator to set the proper SPNs for us. And our policies require us to change passwords every 90 days for all accounts, including service accounts. And to finish we don’t want the service account to be able to login in interactively so we set a Deny Local Login through Group Policies.
This seems like a lot of administration and handling just to be secure. And it is! That is, until now.
Windows Server 2008 R2 and Windows 7 introduce two new type of service accounts, managed and virtual. Both accounts eliminate password management by the administrator and managed accounts also eliminate the need for an administrator to manage SPNs. The full story can be found here: http://technet.microsoft.com/en-us/library/dd367859(WS.10).aspx
To demonstrate how this works I will create three machines, a domain controller, a SQL server and a Windows 7 client machine. All servers are running Windows Server 2008 R2 Sp1 and the client is on Windows 7 Sp1.
Installing the Domain Controller
To start off we need a domain controller. I’m using Hyper-V to create a virtual machine and install Windows Server 2008 R2 Core Edition. There is no GUI to create managed service accounts so the core edition will suit us well.
[DCInstall] InstallDNS=Yes DomainNetBiosName=contoso NewDomainDNSName=contoso.com ReplicaOrNewDomain=Domain NewDomain=Forest ForestLevel=4 DomainLevel=4 SafeModeAdminPassword=myverysecretp@ssw0rd! RebootOnCompletion=Yes
To summarize the benefit of managed service accounts:
With the combination of SQL Server Denali and Windows Server 2008 R2 you can leverage the use of managed service accounts to ease your user administration.