Share via

SharePoint 2010 Configuration with PowerShell and Untrusted SQL domain (SQL Authentication)

  • Article

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)

Comments

  • Anonymous
    January 01, 2003
    Hi, can also use psconfig.exe to create a farm with SQL Authentication ;-) check this: blogs.technet.com/.../install-sharepoint-2010-with-sql-authentication.aspx regards patrick

  • Anonymous
    January 01, 2003
    Hi, cand also use psconfig.exe to create a farm with SQL Authentication ;-) check this: blogs.technet.com/.../install-sharepoint-2010-with-sql-authentication.aspx regards patrick

  • Anonymous
    January 01, 2003
    Sameer,  Web Analytics service with SQL authentication is not working in current SP2010 RTM version. You should be able to configure Search service though. Thanks, Sundar

  • Anonymous
    January 01, 2003
    Sameer,  Web Analytics service with SQL authentication is not working in current SP2010 RTM version. You should be able to configure Search service though. Thanks, Sundar

  • Anonymous
    October 29, 2010
    Is there any guidance from MS on how to setup SharePoint 2010 including all service applications using SQL Authentication? I am using standard edition and cannot figure out how to configure Search Service and Web Analytics service to use SQL Authentications, rest of service applications has provision to be configured using SQL authentication. Sameer Dhoot | http://sharemypoint.in