Denali is packed with new features. A lot of features evolve around the engine, BI, Data Quality but there are also new features added to the installation process. In a previous post I already mentioned managed service accounts and in this post I will show how to install SQL Server Denali on Windows 2008 R2 Core Edition. As an added bonus I will be using managed service accounts again but instead of having the domain admin activate the managed service account I will delegate these permissions to Bob, our SQL administrator. I’m using the same setup as in my previous post.

I installed a new Windows Server 2008 R2 Core Edition and set a fixed ip (10.10.10.5/255.255.255.0). The computer was named SQL-02 and added to the domain CONTOSO. Again, Bob was added to the local administrators group using net localgroup Administrators CONTOSO\Bob /ADD.

Preparing the managed service account

First we will setup a new group for SQL Admins, add a new managed service account, sql2, and delegate permissions to the SQL Admins group. For Bob to be able to control the managed service account we must add him to the SQL Admins group.

• On the domain controller as the domain admin create a new group in AD:
  dsadd group cn=SqlAdmins,cn=users,dc=contoso,dc=com
• Now add Bob to that group:
  dsmod group cn=SqlAdmins,cn=users,dc=contoso,dc=com –addmbr cn=Bob,cn=Users,dc=contoso,dc=com
• Create a managed service account. First start PowerShell and import the AD cmdlets using Import-Module ActiveDirectory. Then create the account:
  New-ADServiceAccount sql2
• To verify the creation you can use Get-ADServiceAccount sql2.
• To delegate permissions to the SQL Admins group we must use dcals. Again, the command and procedure is documented in the Step by Step guide. Exit PowerShell and then run:
  dsacls "CN=sql2,CN=Managed Service Accounts,DC=contoso,DC=com" /G "CONTOSO\SqlAdmins:SDRCLCRPLOCA" "CONTOSO\SqlAdmins:WP;Logon Information" "CONTOSO\SqlAdmins:WP;Description" "CONTOSO\SqlAdmins:WP;DisplayName" "CONTOSO\SqlAdmins:WP;Account Restrictions" "CONTOSO\SqlAdmins:WS;Validated write to DNS host name" "CONTOSO\SqlAdmins:WS;Validated write to service principal name"
• Now Bob can log into SQL-02.
• First enable PowerShell using sconfig.cmd. Choose 4. Configure Remote Management followed by 2. Enable Windows PowerShell.
• Next we need to install a few pre-requirements before we can install SQL Server Denali. First install/enable .NET 3.5.1 using:
  dism.exe /online /enable-feature /featurename:NetFx3-ServerCore
• Next up we need the WOW64 .NET 2.0 bits. Run these two commands to get these:
  dism.exe /online /Enable-Feature /FeatureName:ServerCore-WOW64
  dism.exe /online /Enable-Feature /FeatureName:NetFx2-ServerCore-WOW64
• Now Bob needs to associate the managed service account with this machine. For that we need the Active Directory cmdlets. Run the following command to install the AD PowerShell cmdlets:
  dism /online /enable-feature /featurename:ActiveDirectory-PowerShell
• Next, start up PowerShell and import the AD cmdlets using Import-Module ActiveDirectory.
• Now we need to activate the managed service account at this machine. For this we run Get-ADServiceAccount sql2 to verify that the account is enabled, not associated with another computer and what the SamAccountName is.
  
• Now we install the managed service account on the computer. We need the SamAccountName here, sql2$ in this case.
  Install-ADServiceAccount -identity sql2$
• If everything is correctly configured you will succeed and when you run Get-ADServiceAccount sql2 you will see that HostComputers is filled now. And while the name HostComputers suggest otherwise, a managed service account can only be associated with one computer.
• Finally the time has come to install SQL Server “Denali” CTP3. There is no GUI on core so we need to resort to the command prompt interface. This is documented in Books Online; http://msdn.microsoft.com/en-us/library/ms144259(v=sql.110).aspx.
• Put in the installation media and run the following command (on multiple lines for readability):
  setup.exe /q /ACTION=Install /FEATURES=SQLENGINE /INSTANCENAME=DENALICTP3A /SQLSVCACCOUNT=CONTOSO\sql2$ /AGTSVCACCOUNT=CONTOSO\sql2$ /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS /AGTSVCSTARTUPTYPE=Automatic /SQLSYSADMINACCOUNTS=CONTOSO\SqlAdmins /TCPENABLED=1
• Afther the installation is finished you can check the summary.txt file in the Bootstrap folder, C:\Program Files\Microsoft SQL Server\110\Bootstrap\Log\Summary.txt.
• As with the machine in my previous post I wanted this instance to run on port 6000 as well. Start PowerShell and run this command:
  (gwmi –namespace root\Microsoft\SqlServer\ComputerManagement11 –class ServerNetworkProtocolProperty –filter “InstanceName=’DENALICTP3A’ AND ProtocolName=’Tcp’ AND IPAddressName=’IPALL’ AND PropertyName=’TcpPort’”).SetStringValue(6000)
• This will use WMI to set the tcp port for IPALL. For SQL pro’s the filter should look familiar. After that we need to restart the instance. We use Stop-Service MSSQL`$DENALICTP3A –force to stop the service. We need to escape the dollar sign and we need the force parameter because there are dependent services (SQL Agent). Now start the service with Start-Service MSSQL`$DENALICTP3A.
• To verify SQL is listening on we can search the errorlog for the text “Server is listening on [ ‘any’ <ipv4> 6000].” or you can use netstat –aon | findstr :6000. This will give you the process id that is listening on that port. Using tasklist /FI “PID eq <pid>” where replacing <pid> with the earlier found process id should give sqlserver.exe in return.
• Now SQL is listening on port 6000. We need to open up the appropriate ports in the firewall. use these two commands to open port 6000 and allow UDP traffic for the Browser service.
  netsh advfirewall firewall add rule name="SQL Server DENALICTP3A Instance" dir=in action=allow protocol=tcp localport=6000 profile=domain
  netsh advfirewall firewall add rule name="SQL Server Browser Service" dir=in action=allow protocol=udp localport=1434 profile=domain
• Now returning to the Windows 7 client and SSMS let’s see how we are connected to these instances.
  

With Windows Server 2008 R2 Core you get a smaller install base; 33 processes running on core vs 41 on full installation, 351MB vs 452MB memory in use after start and 4GB vs 9GB disk space. Of course your mileage may vary but you get the general idea. Not only did I show how easy it is to install SQL Server Denali CTP3 on a Windows Server Core edition but also how you permissions to control managed service accounts can be delegated to other administrators.

Give SQL Server Denali CTP3 a run and remember to provide your feedback. If you’re fast you might win something as well, http://blogs.technet.com/b/dataplatforminsider/archive/2011/08/19/announcing-the-sql-server-code-name-denali-feedback-challenge.aspx.

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.
  
• 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.
  
• 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.
  
• 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.