Building Your SharePoint 2013 development machine on Windows Server 2012 – Part 5 – Configuring SQL 2012 Ready for SharePoint 2013

Posted on by

UPDATE: SharePoint 2016 development machine

I am doing a collection of blog posts, you can access the other parts of this post below.

When I started planning the 10 parts of the blog, the one bit I forgot about was Kerberos. So I have squeezed part of it in here. As I am a developer and not really an IT-Pro I had to look up information on the web to help me write this bit on Kerberos and speak to the IT Pro at my work. My references are blksthl blog and my work colleague Adam McEwen, who blogs you can access via these links.

Creating the Accounts required.

First we need to create 4 different accounts.

SP_Farm – SharePoint Farm Account

SP_Content – SharePoint Content account for the Content Databases.

SP_Services – SharePoint account for the Shared Services within SharePoint.

SP_SQL – The account to run SQL with.

  1. In the Start Menu, type Active Directory Users and Computers. Select the application.
  2. Expand the tree in the left hand pane to see the Users OU. Select the Users OU.
  3. Right Click on User and select New > User. Create a new user called SP_Farm. Set the Full Name and Log on name to SP_Farm. Click Next.
  4. In the password dialog screen, enter the following and click Next.
    1. Password and Confirm Password as: Pa55w0rd
    2. Untick User must change password at next logon.
    3. Leave User cannot change password as unticked.
    4. Tick Password never expires
    5. Leave Account is disabled as unticked.
    6. Click Next. Then click Finish.
  5. Repeat the steps 1-4 for the remaining accounts (SP_Content, SP_Services and SP_SQL).

Setting up Kerberos for SP_SQL account.

We will need to set up Kerberos for the other accounts, but this section of blog is getting your SQL ready for SharePoint 2013. Therefore at this point we will just set SQL up for Kerberos.

Name Resolution.

  1. In the Start Menu, type DNS. Open the application to the DNS Manager.
  2. Expand the forward lookup zones container in the left panel.

  3. Right click on the zone cannonfodder.local and click on a New Host (A or AAAA).

  4. Type in the name of the record. In this case we are going to name our SQL Server SQL2012. Set the IP address at the IP of the machine. We set this to a static IP in the last blog. (If you have forgotten you can find out by bringing up a command prompt and type ipconfig /all and look at the IP4 IP address.) Click on Add Host.

  5. Click on Done. You will then get a verification dialog, and after click OK to that, you will see the record has been created in the right pane of the DNS manager.
  6. To check that this is all working, open a command prompt, and type
    ipconfig –flusdns

    Then type

    Ping SQL2012

Service Principle Name

  1. Open a command prompt from the Start Menu by typing in cmd. Right click Command Prompt and along the bottom of the screen will be an option to Run as Administrator.
  2. Type the following in the command window. This will create the SPN for the service account paired with the host name.
    setspn –S MSSQLSvc/SQL2012:1433 CANNONFODDER\SP_SQL
  3. Type the following in the command window. This will create the SPN for the service account paired with the FQDN.
    setspn –S MSSQLSvc/SQL2012.cannonfodder.local:1433 CANNONFODDER\SP_SQL
  4. Now by typing in setspn –L CANNONFODDER\SP_SQL it will list all the SPN for that account.


Trust for delegation

As our server is a domain controller we do not need to set it up so that it is Trusted for Delegation. We can prove this by doing the following.

  1. From the Start Menu type Active Directory Users and Computers. Open the application.
  2. In the left hand panel, expand cannonfodder.local and select Domain Controllers.
  3. In the right hand panel you will see the computer CANNONFODDERSER.
  4. Right click this computer and select Properties. From the properties window, select the Delegation tab. As you can see this computer is already set to Trust this computer for delegation to any service (Kerberos only).

Configuring SQL Server 2012

As SQL was installed using a built in account, and also installed on the machine before it was sysprepped, SQL now needs to be configured to work properly on this server in preparation for SharePoint.

  1. From the start screen type SQL Server Configuration Manager and select the application.
  2. Click to expand SQL Server Network Configuration (not the 32 bit), and choose Protocols for MSSSQLServer, and ensure TCP/IP and Named Pipes are enabled. To enable them right click them and select Enable. Click OK at the warning.
  3. Now in the left hand pane, click SQL Server Services.
  4. For SQL Server (MSSQLSERVER) right click the service name and choose Properties.
  5. Select the Log On tab.
  6. Select this account: radio button and enter cannonfodder\SP_SQL and enter the password as Pa55w0rd. Click Apply, and Yes to restart the service, or Start the service if not already.
  7. Click OK and close SQL Server Configuration Manager.

Now we need to give cannonfodder\administrator access and update SQL Server 2012 local instance after the machine name has been changed.

  1. From the start screen type SQL Server Management Studio and select the application.
  2. Change the server name to SQL2012 and logon using SQL Authentication with the sa account using the password Pa55w0rd. If you try to log in at the moment as cannonfodder\administrator using windows authentication, you will fail to access SQL.
  3. Now give cannonfodder\administrator the correct security access.
    1. Expand Security and right click Logins to select New Login…
    2. Next to the Login Name click Search
    3. Type cannonfodder\administrator, and then click check names. Select the user administrator, not the group.
    4. On the left hand panel, select the Server Roles page, and tick sysadmin. Click OK
    5. Right click the database connection in Object Explorer and choose disconnect.
    6. Click Connect in the Object Explorer, choose database engine, and connect to SQL2012 using Windows Authentication, then confirm you can browse objects in the database server.
  4. Now we need to update the server name. If you Click New Query and type
    
Select @@SERVERNAME

    You will notice that the server name is incorrect.

    Click New Query and copy the script

    
sp_dropserver @@SERVERNAME
GO
sp_addserver 'CANNONFODDERSERVER', local
GO

    run the query.

  5. Restart the SQL server service, right click the SQL Server service, right click the database connection in Object Explorer, and choose Restart. You will then get a prompt asking if you want to restart the MSSQLSERVER service. Say Yes.

  6. Right click the database connection again, and choose disconnect.
  7. Click Connect to SQL2012 using Windows Authentication.
  8. Click New Query, and run the following script to confirm the instance name has been changed to reflect your chosen machine name:
    
Select @@SERVERNAME

  9. You can now close SQL Server Management Studio. No need to save the queries.

Now we are past the half way point, and we can now finally start installing SharePoint 2013!