Installing and Securing SQL Server 2012
This tutorial is the third part of the Series "Installing my SharePoint Server 2013 Farm".
The series is as follow :
Installing SQL Server 2012
We have to create 3 domain accounts and the others will be virtual accounts. Bellow the accounts that we will create :
Service Account name
Database engine svcSQLAcc01
SQL Server Agent svcAGSQLAcc01
To create these accounts follow the steps bellow
- Connect to the domain controller machine
- Open the “Active Directory Users and Computers” management console
- Create a new organization unit called “SQLAccounts
A particularly useful type of directory object contained within domains is the organizational unit. Organizational units are Active Directory containers into which you can place users, groups, computers, and other organizational units. An organizational unit cannot contain objects from other domains. (see technet)
- Now create the users within the newly created Organization Unit “SQLACcounts”. Right client on the "OU" and the click “Add” the “User”
- When you finish you will have the 3 domain accounts :
Before we start installing SQL Server 2012 we must install the “Microsoft Framework 3.5”. The framework is available as a “Windows Feature”.
To begin SQL Server 2012 installation follow the steps bellow:
- Connect to the SQL Server machine
- Insert the SQL Server 2012 image file or DVD in the right drive.
- Start the installation wizard
- Click on “Installation” and then on “New SQL Server standalone installation”
- The “Setup Support Rules” screen appears. Click “OK”
- In the next screen, uncheck the “Include SQL Server product updates” and click “Next”
- The “Install Setup Files” screen appears. Click “Install”
- The “Setup Support Rules” screen appears. The setup support rules identify problems that may occur when you install SQL Server Setup Support Files. Failures must be fixed before setup can continue. When no failure detected click “Next”
- In the “Setup Role” screen , select “SQL Server feature installation” and click “Next” Check all features in the “Feature Selection” wizard step.
- Click “Next” to go to the next screen. In the “Installation Rules” screen, the wizard will execute rules to determine if the installation process will be blocked. Make sure everything is green.
- In the “Instance Configuration Screen”, Click on “Named Instance” radio button. In the SQL Instance name textbox, type the name you want. In this tutorial I’ve used “SQL01”. Give also an instance ID in the textbox “Instance ID”. I’ve used the same as instance name “SQL01”.
- Click “Next” to go to the “Server Configuration” screen.
- In the Configuration Screen and under the “Service Accounts” tab, set the right account for the right service as bellow. Click “Next”.
- The “Database Engine Configuration” screen appears. Set authentication mode to Windows authentication mode. Add the “svcSQLAcc01” and “Administrator” accounts as administrator accounts. Click “Next”. (KB2028697)
- In the Analysis Services Configuration screen, add the “svcSQLAcc01” and “Administrator” accounts as analysis service administrator accounts. Select the “Multidimensional Mode” instead of “Tabular mode”. Click “Next”
- The “Reporting services configuration” screen appears. Under “Reporting services Native Mode” check the “install and configure”. Under “Reporting Services SharePoint integrated mode” check the “Install Only”. Click “Next”
- In the “Distributed Replay Controller” screen, add “svcSQLAcc01” and “Administrator” accounts as users that have permissions to DRC.
- Click “Next” to open the “Distributed Replay Client” screen. In the controller name type the name of the current machine “SQL-VM-SV01”
Similar to SQL Server Profiler, you can use Distributed Replay to replay a captured trace against an upgraded test environment. Unlike SQL Server Profiler, Distributed Replay is not limited to replaying the workload from a single computer.Distributed Replay offers a more scalable solution than SQL Server Profiler. With Distributed Replay, you can replay a workload from multiple computers and better simulate a mission-critical workload.The Microsoft SQL Server Distributed Replay feature can use multiple computers to replay trace data and simulate a mission-critical workload. Use Distributed Replay for application compatibility testing, performance testing, or capacity planning. (see msdn)
- Click “Next” and then begin the installation process.
Now you have successfully installed your SQL Server 2012.
Securing SQL Server
To secure SQL Server you have to follow the steps bellow:
- In SQL Server , configure the SQL Service to listen to a non default port (See msdn)
- Block SQL Server default listening ports (See msdn)
- Open manually assigned port in windows firewall (See msdn)
- In order to add a new SQL Server Alias in the SharePoint machine, you have to install the "SQL Server Native client"
- Open a powershell console and type “cliconfg”. Press enter
- The “SQL Server Client Network Utility” screen will appear.
o Click on the “Alias” tab
o Click on the “Add” button
o In the “Network libraries” click on “TCP/IP”
o In the Server Alias textbox, type the Alias you want to use “SharePoint_Alias”
o In the Server name type the SQL server name
You can use "SQL Server Configuration Manager" to create the Server Alias (see msdn)
You've just installed and secured your SQL Server