SQL account permission sync issue on Always On

SQL account creation process is slightly different for SQL servers which are on always on availability group. The SQL account permissions won't sync properly if the SID of the accounts are not same in primary and secondary SQL server. 

Since the SQL account created in the primary SQL server cannot sync the permission properly in secondary server, after the fail over (when the secondary sever will become primary and vice versa) the account will have permission issue and cannot be used anymore to login. To avoid this issue, either 1) we should create the accounts in primary and secondary server with same SID, or 2) if the accounts are already created, delete/drop the account in the secondary SQL server and have the account recreated using same SID (this is not recommended if the account has many database mappings, it will complicate things).

Create a SQL account in primary and secondary SQL server

- Login to the primary SQL server with admin credential and run the below -

  Use [master]

  GO

  CREATE LOGIN [SQL_Login_Account_Name]  

  WITH PASSWORD = N 'Account_Password',

   DEFAULT_DATABASE=[master]

   GO

- Provide privilege to this account

- The account will not replicate automatically to the secondary SQL server. To have this account available in the secondary SQL server, we will have to re-create it in secondary. Before we do that, we need the SID of the account. Run the below to get the SID.

Select name, sid FROM sys.server_principals WHERE name = 'SQL_Login_Account_Name'

- Login to the secondary SQL server and run the below - 

   

  CREATE LOGIN [SQL_Login_Account_Name]

  WITH PASSWORD=N 'Account_Password',

  SID = SID_OF_THE_ACCOUNT_FROM_PRIMARY_SQL,

  DEFAULT_DATABASE=[master]

  GO

- Provide same privilege as given in primary 

- Go back to the primary SQL server and provide DB access to this account. The new user account will be automatically replicate in secondary SQL server with same permission.

If the accounts are already created in primary and secondary SQL server then the steps will remain same, except - 

1) Drop the account from the secondary sql server

2) Get the SID of the account from the primary sql server

3) Recreate the account in secondary sql server using the same SID

GUI doesn't support creation of accounts using same SID, hence this can be achieved only using TSQL.

SQL Server 2014 - Always on availability - Move MDF files

SQL Server 2014 - Always on availability - Move MDF files

The SQL Servers with always on availability allows you to have fail over options for databases. In an environment where you have always on availability, it requires that we remove the DB from the always on availability before we move the MDF files from one drive to another. 

In this post I am going to write about how to move the MDF files where always on availability is enabled and at the next half I will explain how to add the same DB in always on availability. So, before we start, it is expected that you have the knowledge of primary and secondary SQL server.

Move MDF Files

•          Login to the primary SQL server, expand Always On Availability
•          Select Availability Databases > Press F7
•          The object explorer window displays all DBs
•          Select the DB/s which you want to remove from Availability group and right click to "Remove Database from Availability Group"

            

•  After the DB is removed from availability, Detach the DB. Right Click on the DB and select Tasks > Detach
•  Login to Secondary SQL server
•  Remove the DB from the always on availability (refer the steps above)
•  Delete the DB/s from the secondary SQL server. Right click on the DB > click Delete > Select close all connections > click Delete
•  Copy the MDF files from source to target destination (ex. Disk2 to Disk1)
•   Login to Primary SQL Server
•  Right click on Databases > Attach > Add > Select the MDF file > click OK
• After the DB is attached we will work to add the DB in always on availability group of the primary and secondary server.

December '16 Cumulative Update for SharePoint 2013 Issue - Data View Web Parts Stopped working without workaround

The SharePoint Data View Web Part in On Prem environment is not working as expected after the Dec '16 CU is installed. This is fixed in April '17 CU. So, after you patch your SP 2013 environment with Dec '16 CU you would see your DVWP breaking. Below are the error details and workaround available.

If you add a DVWP in your page you would see an error like below.

Error On the Page :

Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

Correlation ID: -------

Error in ULS Log :

If you search the ULS log with the correlation id you will see an error like below.

Error while executing web part: System.NullReferenceException: Object reference not set to an instance of an object.   

 at Microsoft.SharePoint.WebControls.SPDataSourceView.ExecuteSelect(DataSourceSelectArguments selectArguments, String aggregateString, Boolean wantReturn, BaseXsltListWebPart webpart, SPListItem& listItem, SPListItemCollection& listItems, String[]& fieldList)   

 at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigatorInternal()   

 at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigator()   

 at Microsoft.SharePoint.WebPartPages.DataFormWebPart.GetXPathNavigator(String viewPath)   

 at Microsoft.SharePoint.WebPartPages.DataFormWebPart.PrepareAndPerformTransform(Boolean bDeferExecuteTransform)

Workaround :

Open the page in SharePoint Designer and remove the below tags from the data source.

<SelectParameters><WebPartPages:DataFormParameter ParameterKey="ListName" PropertyName="ParameterValues" DefaultValue="Project Study" Name="ListName"></WebPartPages:DataFormParameter><asp:Parameter DefaultValue="0" Name="StartRowIndex"></asp:Parameter><asp:Parameter DefaultValue="0" Name="nextpagedata"></asp:Parameter><asp:Parameter DefaultValue="1" Name="MaximumRows"></asp:Parameter>

OR

<asp:Parameter Name="StartRowIndex" DefaultValue="0"/><asp:Parameter Name="nextpagedata" DefaultValue="0"/>

Reference - https://answers.microsoft.com/en-us/msoffice/forum/msoffice_sharepoint-mso_win10/data-form-web-part-broken-in-spd-2013/c72ca036-8a86-451e-b90c-6c941e7212c8?rtAction=1493664482015

Observation :

The issue is not reproducible in environments where Dec '16 CU is unavailable. I have tested this.

Solution :

MS has confirmed that it is a known bug. The issue is fixed in April '17 CU, but I haven't installed and tested this to confirm. Patch installation requires planning, approval and downtime which is time consuming. Once I test this, I will update again to confirm.

SharePoint 2013 Site Collection Change Lock State using PowerShell

There are various situations where a site collection administrator has to make changes to the lock status of a/multiple site collections. It could be because you are migrating, or you want to execute some auditing and so on. Below is a script which can help you do the same. We can change the Lock state of a site collection to any of the available one. You can also add multiple site collections in the $sitelist parameter.

Hope it helps :) !


$sitelist = @("https://yoursitecollectionURL",)
foreach($site in $sitelist){
  $s = get-spsite $site
  $s.LockIssue = "Any text you want to add for logging purpose"
  //Set-SPSite -Identity $site -LockState [NoAccess|UnLock|ReadOnly]
  Set-SPSite -Identity $site -LockState NoAccess
                                         }

500 Internal Server Error - SharePoint STS Application Pool

Often we have noticed 500 Internal Server Error in SharePoint Site collections. The issue typically occurs when the service account that is used by the STS was used for an interactive log on and afterwards logged out. For example, an administrator logs on to a WFE server by using the farm account and then log off. By doing this, it forces the registry keys to be unloaded in the profile of that account. This condition makes the keys unavailable for future use. The most common errors which we notice in the ULS log would be like below.

SPSecurityContext: Request for security token failed with exception: System.ServiceModel.FaultException: The server was unable to process the request due to an internal error.  For more information about the error, either turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the <serviceDebug> configuration behavior) on the server in order to send the exception information back to the client, or turn on tracing as per the Microsoft .NET Framework SDK documentation and inspect the server trace logs

STS Call Claims Windows: Problem getting output claims identity. Exception: 'System.Runtime.InteropServices.COMException (0x800703FA): Illegal operation attempted on a registry key that has been marked for deletion. at System.DirectoryServices.DirectoryEntry.Bind(Boolean throwIfFail)  at System.DirectoryServices.DirectoryEntry.Exists(String path) 

STS Call: Failed to issue new security token. Exception: System.Runtime.InteropServices.COMException (0x800703FA): Illegal operation attempted on a registry key that has been marked for deletion. at System.DirectoryServices.DirectoryEntry.Bind(Boolean throwIfFail) at System.DirectoryServices.DirectoryEntry.Exists(String path) 

Workaround :

1.  Recycle the STS app pool

2.  If some users are still getting 500 Internal Server Error, clear the browser cache of the user machine and it should work

Solution :

There are two steps involved to fix the issue of 500 Internal Server Error.

Step 1 : Enable auto recycle of STS App pool during off business hours.

Step 2 : Enable "Load User Profile" feature in the IIS against STS app pool. You can refer this article to configure the same.

Get ULS Log Using Powershell

How to get the ULS log entries using powershell

It is not easy to search for an error using correlation id from the sharepoint log. We use ULS log viewer to make the ULS log look nicer. However, powershell provides us a way to extract the log between a specified time frame and provide a text file. Run the below command to get it.

Add-PSSnapin Microsoft.SharePoint.PowerShell
Get-SPLogEvent -StartTime "07/02/2016 11:35:00" -EndTime "07/02/2016 11:40:00" | ?{$_.Correlation -eq "3c768c9d-5f2e-70a4-962e-b5dddb8ca4f2"} | select Area, Category, Level, EventID, Message | Format-List | out-file "D:\Corelog.txt"