Firewall settings for SharePoint Farm

Some times in SharePoint when we access web sites, search and configure alerts we see network related errors even though we configured everything is good. There could be so many reasons for that. One could be Firewall. In this article I am providing the steps how and where to check for the firewall issue.

On each SharePoint 2013 Server, we will need to set a firewall rule to allow SharePoint intra-farm traffic and HTTP/HTTPS traffic. Alternatively, you can disable the Windows Firewall if you choose and if you have another firewall solution.

You can set the Windows Firewall rules by navigating to the Control Panel, then click System and Security, then click Windows Firewall, and finally click Advanced settings. In the Inbound Rules area, ensure that the server allows connections on port 80 (HTTP) and port 443 (HTTPS). Add the ports listed in the table below for the SharePoint 2013 inter-farm communication by following these steps: 

   

1.    In the Windows Firewall with Advanced Security window, click Inbound Rules.

2.    In the Actions panel, click New rule…

3.    In the New Inbound Rule Wizard window, select Ports as the Rule Type and click Next.

4.    Select the appropriate choice between TCP and UDP, and enter the desired port(s) for the Specific local ports. Click Next.

5.    Click Next. On the Profile screen, click Next.

6.    On the Name screen, enter the desired name and click Finish.

                    

                      

Rule Name	TCP or UDP	Ports
SharePoint Web Traffic	TCP	80,443
SharePoint Search Index	TCP	16500-16519
SharePoint Farm Communication	TCP	32843,32844,32845
SharePoint Profile Synchronizing (TCP)	TCP	5725,389,88,53
SharePoint Profile Synchronizing (UDP)	UDP	389,88,53,464
SharePoint User Code Service	TCP	32846
SharePoint SMTP Service	TCP	25

SharePoint Server Search  Administration Medium Could not find or connect to host controller on server

Issue: After we configure everything in related to search, when we try to find the search we see the error “A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond”

Event:

0x0BD8        SharePoint Server Search   Administration   Medium   Could not find or connect to host controller on server SERVER. Exception: Could not connect to net.tcp://SERVER/ceres/hostcontroller/nettcp. The connection attempt lasted for a time span of 00:00:21.0369864. TCP error code 10060: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 10.181.14.21:808.    Server stack trace:      at System.ServiceModel.Channels.SocketConnectionInitiator.Connect(Uri uri, TimeSpan timeout)     at System.ServiceModel.Channels.BufferedConnectionInitiator.Connect(Uri uri, TimeSpan timeout)     at System.ServiceModel.Channels.ConnectionPoolHelper.EstablishConnection(TimeSpan timeout)     at System.ServiceModel.Channels.ClientFramingDupl…        ff78fc9b-4b6c-b08b-52a2-ff7745f71f1f

Resolution: After I go through the Events and run the traces I come to know that the issue is related to network. I deep on diving the issue I could identify that something is blocking to search.

1.   Ensure to check the Fire wall settings.

2.   Check to stop the firewall and search. This will work but not recommended in production servers.

3.   Ensure that to create a new rule allowing Inbound traffic on TCP Port 808 on all SharePoint Servers

Go to->Windows firewall with advanced security->click on Inbound->create new rule to allow                                

                                   
                                     
              
                                           

Issue: After we configure everything in related to search, when we try to find the search we see the error “A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond”

Event:

0x0BD8        SharePoint Server Search   Administration   Medium   Could not find or connect to host controller on server SERVER. Exception: Could not connect to net.tcp://SERVER/ceres/hostcontroller/nettcp. The connection attempt lasted for a time span of 00:00:21.0369864. TCP error code 10060: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 10.181.14.21:808.    Server stack trace:      at System.ServiceModel.Channels.SocketConnectionInitiator.Connect(Uri uri, TimeSpan timeout)     at System.ServiceModel.Channels.BufferedConnectionInitiator.Connect(Uri uri, TimeSpan timeout)     at System.ServiceModel.Channels.ConnectionPoolHelper.EstablishConnection(TimeSpan timeout)     at System.ServiceModel.Channels.ClientFramingDupl…        ff78fc9b-4b6c-b08b-52a2-ff7745f71f1f

Resolution: After I go through the Events and run the traces I come to know that the issue is related to network. I deep on diving the issue I could identify that something is blocking to search.

1.   Ensure to check the Fire wall settings.

2.   Check to stop the firewall and search. This will work but not recommended in production servers.

3.   Ensure that to create a new rule allowing Inbound traffic on TCP Port 808 on all SharePoint Servers

Go to->Windows firewall with advanced security->click on Inbound->create new rule to allow                                

                                   
                                 
              
                                           

Database is in compatibility range and upgrade is recommended for Service Application or Content Databases Fixes

1) To correct the content database issue, just run the command as below from SharePoint PowerShell as Administrator:

$db = Get-SpDatabase

Upgrade-SPContentDatabase $db

After it has done, check the status again on Central Administration;

1) To correct the upgrade necessary on BDC database, below from SharePoint PowerShell as Administrator:

$db = Get-SpDatabase

$db.Type

$db.NeedsUpgrade

(Get-SPDatabase | ?{$_.type -eq “Microsoft.SharePoint.BusinessData.SharedService.BdcServiceDatabase”}).Provision()

After it has done, check the status again on Central Administration;

Queue Job Status shows “Waiting to be processed” and then never completes

To resolve this problem, set the SQL Retry Limit value to the default value of 5 and then restart the Microsoft Office Project Server Queue Service. 

To do this, follow these steps:

1. Set the SQL Retry Limit value to the default value of 5. 

To do this, follow these steps:

• a. On the Microsoft Office Project Web Access home page, click Server Settings.
• b. On the Server Settings page, under Queue click Queue Settings.
• c. On the Queue Settings page, change the SQL retry limit to 5.
• d. Click Save.

2. Restart the Microsoft Office Project Server Queue Service. 

To do this, follow these steps:

• a. From the server that is hosting Microsoft Office Project Server 2007, click Start, and then click Run.
• b. In the Run dialog box that appears, type “services.msc” without the quotation marks and then click OK.
• c. In the Services window, right-click Microsoft Office Project Server Queue Service and then click Stop. After the service has stopped, right-click it again and then click Start.

Create and Configure SQL Server Alias for SharePoint Installation

• What is SQL Server Instance and SQL Server Alias?
• Why we are using SQL Server Instance and SQL Server Alias?
• How to create and configure them?
• I will try to solve above questions. Let’s Start,

Client applications connect to an instance of Microsoft SQL Server to work with a SQL Server database.

When you are going to install SQL Server, you have to select options for SQL Server Instance:
An instance is either the default, unnamed instance, or it is a named instance.

SQL Server Default Instance

When SQL Server is installed in the default instance, it does not require a client to specify the name of the instance to make a connection. The client only has to know the server name.
MY-MACHINE-NAME /* unnamed version (default instance) */

SQL Server Named Instance

A named instance is identified by the network name of the computer plus the instance name that you specify during installation. The client must specify both the server name and the instance name when connecting.
MY-MACHINE-NAME\MSSQLSERVER /* named version */

How to make decision which to do:

• You can install only one default instance but can install multiple named instances. It is good to install only a default instance unless you have a special need to install multiple SQL Server instances on the same host, such as running different versions concurrently or other isolation requirements. These additional instances must be named.
• The underlying difference between a default and named instance is mostly a matter of network connectivity. Clients can connect to the default instance using only the host name over the well-known 1433 port. To connect to a named instance, clients specify the host and instance name (e.g. ” MY-MACHINE-NAME\MSSQLSERVER”) and the SQL Server Browser service returns the port the named instance is listening on.

Now Let go on SQL Server Alias:

A SQL Server alias is simply a friendly name or nick name, configured on the client computer that points at a SQL Server instance. This instance can either be installed locally or on a different machine on the network.

So if my SQL Server Alias name is “DevelopmentDB”,
Then you can say for SQL Server Named Instance, “MY-MACHINE-NAME\MSSQLSERVER” = “DevelopmentDB”

Note: For that we need to configure the SQL Server Alias on the SQL Server, after that we can use the SQL Alias name instead of the SQL Server Instance name.

SQL Server Alias Creation Steps:
Step 1: Run the SQL Server Configuration Manager, Programs -> Microsoft SQL Server 2012 -> Configuration Tools

SQL Server Configuration Manager

Step 2: Verify the SQL Server (MSSQLSERVER) Service is running under the SQL Server Service option.

Step 3: Verify the TCP/IP Protocol is enabled for the SQL Native Client 11.0 Configuration (32bit) and SQL Native Client 11.0 Configuration tree.

SQL Server Configuration Manager

Step 4: Create the new alias, Right click and select new alias under Aliases of SQL Native Client 11.0 Configuration (32bit) and tree.
In New Alias Creation Dialog,
• You have to set the protocol to TCP/IP,
• Port no, by default SQL Server uses 1433 port number. You can also specify other port number as well.
• Server name: SQL Server Instance Name as discussed above (default Instance or named instance)
• I have default Instance named “MyDBServer”.
• New alias name is “DevelopmentDB”
So Now, “DevelopmentDB” alias uses “MyDBServer”, port no “1433” and “TCP/IP” protocol to connect to the SQL Server Instance over the network

SQL Server Alias – 32bit

SQL Native Client 11.0 Configuration-32bit(a)

SQL Server Alias

SQL Native Client 11.0 Configuration(b)

Step 5: We have completed the create alias process. For the verification open SQL Server Management Studio and try to connect with SQL Server using newly created alias name “DevelopmentDB”. If everything configuration is fine, then it will connect to SQL Server:

SQL Server Management Studio

This is the scenario where you have one machine or server and you have successfully created SQL Server Alias.

One more beneficial use of SQL Server Alias is for the multiple servers’ environment like SharePoint Multiple Server Farm.
Assume, we have two servers

• Database Server: MyDBServer
• SharePoint Server: MySPServer

SQL Server is not installed on “MySPServer” server. But still we can use the SQL Server Instance of the “MyDBServer” using the alias configuration on “MySPServer” sever
Below Configuration, We have to do on the SharePoint Server “MySPServer” to connect to the SQL Server of “MyDBServer”.
We have already created alias named ”DevelopmentDB” on the server “MyDBServer”.

Now we have to configure SQL Server alias on the “MySPServer” server as below:
Step 1: If you haven’t installed the SQL Server 2012 client tools, you can still create an alias using the SQL Server Client Network Utility. To bring up the utility, click on Start, then run, and run cliconfg.exe.
Or you can open it form C:\Windows\System32\cliconfg.exe

Cliconfg

Step 2: Below view of SQL Server Client Network Utility, Enable the TCP/IP protocol from the General Tab,

SQL Server Client Network Utility

Step 3: Go to the Alias Tab to create a new alias, click on the Add… button.
“Add Network Library Configuration” view,
Server Alias is what the application will attempt to connect. I have already created that alias on SQL Server named “MyDevelopmentDB”
Server Name: Real Server Name, in our case server name is “MyDBServer”
Port Number: 1433 (Configured as before)
Click on apply and OK to create new SQL Server Alias on “MySPServer’ server

Network Library Configuration

One more thing we need to take care is about the Port Number.

Open the Port for Inbound Connection if Enable Firewall

When we configure the port number for SQL Server Alias Name then server does not allow the out side server connection on this port because of the Firewall Settings.
So our next step is open the custom port for inbound connection if firewall is enabled.
Create a new Inbound rule for allowing the connection for alias port (in my case it is 1433)

Step 1: Open Windows Firewall with Advance Security from the Control Pannel. Click on the“New Rule” of Inbound Rules Tab. Select the Rule Type as “Port”. Click Next.

Firewall Settings

Step 2: Domain Rule apply for To TCP and Provide the specific port number (1433). Click Next.

Firewall Setting for Port

Step 3: Apply the Rule on the Profile, Click Next and allow the connection.

Firewall Setting for Port

Firewall Setting for Port

Step 4: Provide the name of the inbound rule, Click Finish.

Firewall Setting for Port

Now while creating the new farm, provides the SQL Client Alias named “DevelopmentDB” instead of the SQL Server Instance name of “MyDBServer”. So All database for the SharePoint Configuration will be created on the SQL Server of the “MyDBServer”

Let me know if you have any suggestions or questions.

Thank you…

Get site collection owners for each site collection in SharePoint farm

This script gets list of site collection owners for each site collection for every web application within sharepoint farm and saves them to csv file for audit purposes. Also owners can be used to get maintenance and other approvals.

This script gets list of site collection owners for each site collection for every web application within sharepoint farm and saves them to csv file for audit purposes.  Also owners can be used to get maintenance and other approvals.

This csv file can be opened in Microsoft Excel for better viewing. Below is one sample output generated by script:

Assumptions:

1. This script needs to run either with farm admin privileges or a service account which has read access for every web application in the farm. Also site collections with lock state set as no access, will not be included in the script output.

2. This csv file is generated in the current working directory as per powershell prompt. If you need to save the output to some other directory, modify the value of $FileLocation variable in the script.

As a part of best practices, pls run this script in your dev/qa environment first to get familiar with script output. Here's the code:

Power Shell

############################################################################### 
# This script gets list of administrators for each site collection within  
# sharepoint farm and saves output in tab separated format (.csv) file.  
############################################################################### 
 
#Set file location for saving information. We'll create a tab separated file. 
$FileLocation = "SiteCollectionOwnersReport.csv" 
 
#Load SharePoint snap-in 
Add-PSSnapin Microsoft.SharePoint.PowerShell 
 
#Fetches webapplications in the farm 
$WebApplications = Get-SPWebApplication -IncludeCentralAdministration 
Write-Output "URL `t ID `t Site Collection Owner `t Site Collection Owner Email `t Site Collection Secondary Owner `t Site Collection Secondary Owner Email " | Out-file $FileLocation 
 
foreach($WebApplication in $WebApplications){ 
    #Fetches site collections list within sharepoint webapplication 
    Write-Output "" 
    Write-Output "Working on web application $($WebApplication.Url)" 
    $Sites = Get-SPSite -WebApplication $WebApplication -Limit All     
 
    foreach($Site in $Sites){      
            #Fetches information for each  site 
            Write-Output "$($Site.Url) `t $($Site.ID.Guid) `t $($Site.Owner.Name) `t $($Site.Owner.Email) `t $($Site.SecondaryContact.Name) `t $($Site.SecondaryContact.Email)" | Out-File $FileLocation -Append 
            $Site.Dispose() 
    } 
} 
 
#Unload SharePoint snap-in 
Remove-PSSnapin Microsoft.SharePoint.PowerShell 
 
Write-Output "" 
Write-Output "Script Execution finished" 
     
############################################################################## 
## End of Script 
##############################################################################

 

Automating site creation

For an automatic site creation we have the ability to use a lot of tools, like SharePoint Self-Service Site Creation, Workflows, Nintex, PowerShell and a lot of other tools. Also you can able to create your own power-shell script using below steps

Summary:

1. Create a list
2. Create a script
3. Add the script to the task scheduler
4. Requested site is created.

Steps for automating site creating with a SharePoint list and Power-shell:

1. Create a list where users can add there site request. The list has to have a least to following fields:
   1. Title (Text)
   2. Status (Choice: Requested, Approved, Created).
   3. URL (Hyperlink)
      
      The users have the ability to add a new item in this list. You can create a workflow to approve the requests and update the status field by the workflow.
2. Create a PowerShell script “siteCreation.ps1”. The script will create a new web for all items in the Teamsites list with status Approved.

   if((Get-PSSnapin | Where {$_.Name -eq "Microsoft.SharePoint.PowerShell"}) -eq $null) {
    Add-PSSnapin Microsoft.SharePoint.PowerShell;
   }
   
   $NewSiteURL = "http://portal/teams/"
   $sourceWebURL = "http://portal/site/"
   $sourceListName = "Teamsites"
   
   $spSourceWeb = Get-SPWeb $sourceWebURL
   $spSourceList = $spSourceWeb.Lists[$sourceListName]
   $spSourceItems = $spSourceList.Items | where {$_['Status'] -eq "Approved"}
   $list = $spSourceWeb.Lists[$sourceListName]
   $spSourceItems | ForEach-Object {
    $Title = $_['Title']
    $url = $NewSiteURL + "/" + $Title
    Write-Host "Create site: $Url"
    New-SPWeb –url $url -name $Title -template STS#0 
    $_['Status'] = "Created"
    $_['URL'] = $url + ", " + $Title 
    $_.Update()
   }

3. Create a task that will run every hour and start the site creation script.

   $A = New-ScheduledTaskAction -execute "powershell" -argument "-nologo -noprofile -noninteractive E:\[location of script]\siteCreation.ps1"
   $T = New-ScheduledTaskTrigger -Once -At 17:00PM 
   $T.RepetitionInterval = (New-TimeSpan -Minutes 60)
   $T.RepetitionDuration = ([Timespan]::MaxValue)
   Register-ScheduledTask -TaskName "SharePoint site creation" -Trigger $T -Action $A -description "Run site creation script every hour." -User "$env:USERDOMAIN\$env:USERNAME" -Password 'p@ssword' -RunLevel 1

4. The script will create a new web for all the requested sites in the Teamsites list with status Approved. After the site creation the status of the item will be modified to Created and the URL to the web is filled in.