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.

  • After the installation is finished supply the password when requested and log in to the machine.
  • The easiest way to configure some basic items in core is sconfig.cmd. Use this to name the machine, set a fixed IP and if you like enable remote desktop and apply all windows updates.
    image
  • I used 10.10.10.1/255.255.255.0 as fixed ip and set the gateway and dns to 10.10.10.1 as well. I renamed the machine to DC-01 and rebooted when requested.
  • To turn this machine into a domain controller we need to run dcpromo. Since there is no GUI we need to supply it with a answer file and run it like this:
    dcpromo /unattend:c:\users\administrator\dccontoso.txt
  • The dccontoso.txt file can easily be created with notepad.exe. More info on the contents of the file can be found at http://support.microsoft.com/kb/947034. My text file looks like this:

[DCInstall]
InstallDNS=Yes
DomainNetBiosName=contoso
NewDomainDNSName=contoso.com
ReplicaOrNewDomain=Domain
NewDomain=Forest
ForestLevel=4
DomainLevel=4
SafeModeAdminPassword=myverysecretp@ssw0rd!
RebootOnCompletion=Yes

  • After a reboot you can log in as domain administrator. First we create a user. This user will be used to install SQL Server on the SQL machine. To create user Bob from the commandline I used the following command
    dsadd user cn=bob,cn=users,dc=contoso,dc=com –pwd p@ssw0rd –disabled no
  • Next up is creating a managed service account Bob can use to install SQL Server. The most preferred way to do this is use the PowerShell cmdlets. So we need to install these first.
  • There are two options to install PowerShell, a hard one and an easy one. The hard one is installing all components individually using DISM. The easy one is enable PowerShell with sconfig.cmd. Use whatever you like. More information can be found here: http://support.microsoft.com/kb/976736.
  • Next up is installing the AD PowerShell cmdlets. The following command (case sensitive!) will take care of that:
    dism.exe /online /enable-feature /featurename:ActiveDirectory-PowerShell
  • Now startup PowerShell by just typing in powershell followed by enter.
  • To make use of the AD PowerShell cmdlets we need to import these, running import-module ActiveDirectory will do just that.
  • Next up is creating a Managed Service Account. Step by step instructions can be found here: http://technet.microsoft.com/en-us/library/dd548356(WS.10).aspx.
  • I created an account sql1 using this syntax: New-ADServiceAccount sql1. Couldn’t be easier.
  • To verify the account creation you can run Get-ADServiceAccount sql1.
    image
  • That concludes work on the domain controller. Next up is the SQL machine. Create a new machine, this time I used a full edition.
  • I assigned the machine a static ip (10.10.10.2/255.255.255.0) and pointed gateway and DNS to the domain controller. For Bob to be able to install SQL Server he was added to the local administrators group.
  • After adding the machine to the domain we log back in as Bob, the administrator of this machine but a regular user beyond this machine.
  • Being logged in as Bob we can install SQL. Well, not quite yet. The domain admin needs to associate the managed service account with the computer. A managed service account can only be associated with one computer at a time so this solution will not work for clusters. The following command will associate the computer with the service account:
    Add-ADComputerServiceAccount –Identity SQL-01 –ServiceAccount sql1
  • Also the service account needs to be activated on the machine, either by a domain administrator or by a local administrator with delegated permissions, using the following command:
    Install-ADServiceAccount –Identity sql1
  • At this moment we are all set to install SQL Server. I’m only installing the Engine. I will be using a Windows 7 client to administer the instance. When specifying the service account you can search for the managed service account or type it in, but remember to put a $ after the name when you type it and leave the password field blank.
    image
  • After SQL is installed we need to open the ports in the firewall. I changed the port to 6000. Remember to also open UDP port 1434 if you want to make use of the browser service. If you do not open UDP 1434 clients cannot connect using the instancename.
  • From a Windows 7 client with only SSMS installed I connected to SQL-01. Open a new query window and check the result of this query:
    select auth_scheme from master.sys.dm_exec_connections where session_id=@@spid;
  • As you might see Kerberos is used. But we didn’t create a SPN. This was done by the managed service account itself. You can verify by running setspn.exe –L CONTOSO\sql1$. The result will show that SPNs are created for the instance and port. Go ahead and change the port number SQL server is listening on, restart the service and check the SPN again.

To summarize the benefit of managed service accounts:

  • Password management is done automatically by AD.
  • No users have knowledge of the password. As you might recall, Bob never typed in the password.
  • Managed service accounts behave like domain accounts and can be put in domain groups.
  • Managed service accounts cannot be used to log on to the machine.
  • SPN management is done automatically.

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.