SharePoint 2010 Configuration with PowerShell and Untrusted SQL domain (SQL Authentication)
This blog will provide step by step instruction for configuring SharePoint 2010 with SQL Authentication using PowerShell. Eric Kraus has provided very good article about configuring SharePoint 2010 with PowerShell. This blog is just extension of it using SQL Authentication and adding servers to the existing farm.
I am using SharePoint 2010 RC (4747), SQL Server 2008 SP1 +CU, and Windows Server 2008 R2.
Before going into the configuration, we need setup accounts (farm accounts, db accounts) and some housekeeping.
My architecture is SharePoint WFEs (2 server) are in DMZ connected to my domain. However, SQL server is on untrusted domain.
1. Configure SQL Server to use mixed authentication. You can configure SQL Server to used mixed authentication when you install SQL Server. For information about how to change the authentication mode after you install SQL Server, visit the following Microsoft Web site: https://msdn2.microsoft.com/en-us/library/ms188670.aspx (https://msdn2.microsoft.com/en-us/library/ms188670.aspx)
2. Add a new SQL Server account in Microsoft SQL Server 2008 SP1+CU. Then, grant the roles of security administrator and database creator to the account. To do this, follow these steps:
a. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
b. In SQL Server Management Studio, expand Security, right-click Logins, and then click New Login.
c. In the Login - New dialog box, type the name of the SQL Server account, click SQL Server Authentication, type the password, and then click Server Roles.
d. In the results pane, click to select the following check boxes, and then click OK:
§ dbcreator
§ securityadmin
3. Create a service domain account with a role “Needs to log on Batch job”.
Please refer to this blog https://www.cleverworkarounds.com/2008/09/16/sometimes-microsoft-bashing-is-justified/ for more on why we need log on batch job role in domain account.
I am NOT talking about configuring windows firewall for SQL in this blog.
If you are interested on configuring windows firewall please refer
Ø How to: Configure a Windows Firewall for Database Engine Access
o https://msdn.microsoft.com/en-us/library/ms175043.aspx
Ø How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)
o https://msdn.microsoft.com/en-us/library/ms177440.aspx
Ø SQL Server Browser Service
o https://msdn.microsoft.com/en-us/library/ms181087.aspx
Ø Configuring the Windows Firewall to Allow SQL Server Access
Now, we created accounts, will go to SharePoint installation and configuration.
After installing SharePoint 2010 pre-requisites, start SharePoint install.
I want to create a farm rather standalone so during the SharePoint Installation, i choose “Server Farm” and then “Complete” install.
After the install completes, the setup program will ask you if want to run the SharePoint Technologies configuration wizard – uncheck the box. We don’t want to run the wizard.
On StartàMicrosoft SharePoint 2010 ProductsàRight click on SharePoint 2010 Management Shell and choose “Run as administrator”
As we haven’t configure the farm yet, PowerShell will give the following error – it is ok and ignore it.
Figure 1
Now we need to run the script
· $dbcredential = New-Object –typename System.Management.Automation.PSCredential –argumentlist “Moss_SPAdmin”, (ConvertTo-secureString “password” –AsPlainText –Force)
· New-SPConfigurationDatabase –DatabaseName “SharePoint2010_Config” –DatabaseServer “<db server>” –AdministrationContentDatabaseName “SharePoint2010_Admin_Content” –Passphrase (ConvertTo-SecureString “pass@word1” –AsPlaintext –Force) –FarmCredentials (Get-Credential) –DatabaseCredentials $dbcredential
NOTE: Get-Credential will prompt for userid/password, where you can provide domain\userId and password. However, if you don’t provide domain name it will interrupt it as “\userid”, which will cause problem for my dbcredential as I don’t have domain. Hence, I created $dbcredential to hardcode userid/password.
Figure 2
Script may take couple of minutes as it has to create Dbs and stored procedure. After the process is run and you get prompt in PowerShell,
You can do either of the following option to verify farm creation
1. you can restart the PowerShell to verify the farm has been created (Now you should see any error or warning on the PowerShell)
2. #verifying farm creation
$spfarm = Get-SPFarm -ErrorAction SilentlyContinue -ErrorVariable err
if ($spfarm -eq $null -or $err) {
throw "Unable to verify farm creation."
}
Figure 3
Install-SPHelpCollection –ALL
Intialize-SPResourceSecurity
Install-SPService
Figure 4
Install-SPFeature –AllExistingFeatures
Figure 5
Figure 6
New-SPCentralAdministration –Port 1234 –WindowProvider “NTLM”
Install-SPApplicationContent
Figure 7
I did NOT run “DisableLoopbackCheck” as I am pretending this is as my production server. To learn more about “DisableLoopbackCheck”, please refer https://www.harbar.net/archive/2009/07/02/disableloopbackcheck-amp-sharepoint-what-every-admin-and-developer-should-know.aspx
DONE!!. SharePoint farm created with Central admin
Now, I need to add another server for the farm
After installing Pre-requisite and SharePoint 2010 (in my case RC -4747)
Run the following command
· $dbcredential = New-Object –typename System.Management.Automation.PSCredential –argumentlist “SPAdmin”, (ConvertTo-secureString “password” –AsPlainText –Force)
· Connect-SPConfigurationDatabase –DatabaseName “SharePoint2010_Config” –DatabaseServer “<db server>”–Passphrase (ConvertTo-SecureString “pass@word1” –AsPlaintext –Force) –FarmCredentials (Get-Credential) –DatabaseCredentials $dbcredential
Intialize-SPResourceSecurity
Install-SPService
Install-SPFeature –AllExistingFeatures
Install-SPApplicationContent
NOTE: There is a SPModule https://sharepoint.microsoft.com/blogs/zach/Script%20Library/Modules/SPModule/SPModule.zip available which do all the above work with script (“Install-SharePoint, New-SharePointFarm, Join-SharePointFarm, etc.”). However, if anyone wants to use the manual way of configuration, hope this will help them out. Zack Rosenfield has provided very detailed description about SPModule here (https://sharepoint.microsoft.com/blogs/zach/Lists/Posts/Post.aspx?ID=54)