Author: Wesley

SharePoint and User Profiles not updated

Hello,

Another day, another issue with the SharePoint UPSA and FIM.

Context

This time, it all began with an InfoPath which was fetching the manager of users to send an email. The form had worked correctly for a few months now and suddenly, it began to fail with some users only.

The issue of the form was related to empty Manager entries for those users. While it was correctly populated in the Active Directory, the field was empty in the SharePoint User Profile.

Troubleshooting

I opened the FIM client GUI to check why the Manager wouldn’t get updated correctly. During the last step where the profiles get exported in the SharePoint Profile DB, a lot of errors were saying “ma-extension-error“. Clicking on it for more details gave us this error message :

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.AggregateException: One or more errors occurred. ---> System.InvalidOperationException: More than one DN specified for the same profile.
 at Microsoft.Office.Server.UserProfiles.ProfileImportExportService.AddDNLookupTable(UserProfileApplicationProxy upaProxy, Guid partitionID, Int64 recordId, String objectType, String distinguishedName)

The total errors listed for the export process reached 1153 :

mRemoteNG_2018-03-08_14-53-53

The error message told me I had to check directly in the Profile database to review what was going on with those “duplicate DN”.

After a few queries in the DNLookup and UserProfile_Full table, I was able to make an initial assumption. Not so long ago, I configured the User Profile sync with the “SharePoint Active Directory Import” built-in engine by mistake. I then re-configured it using the “SharePoint Profile Synchronization” (FIM engine) but a few synchronizations had already been going on. I didn’t thought that the switch would leave any artifacts but it seems that the Profile DB kept some information of the light-weight import.

Some profiles were still in the “DNLookup” table but with a DN value not similar to the one used by the FIM engine : there is no “MVID=” characters in front of the ID. Interestingly, running the below TSQL, it retrieved 1155 results. A pretty close number to the sum of errors (1153) that the FIM client exposed ūüôā

SELECT UPF.NTName, UPF.[PreferredName], DN.DN
FROM [UPA-Profile-DB].[dbo].[UserProfile_Full] as UPF with (nolock)
 INNER JOIN [UPA-Profile-DB].[dbo].DNLookup as DN with (nolock)
 ON UPF.RecordID = DN.RecordId
where DN.DN not like 'MVID=%'

Here are other SQL queries that helped me :

SELECT UPF.[RecordID], UPF.[UserID], UPF.[NTName], UPF.[PreferredName], DN.DN
FROM [UPA-Profile-DB].[dbo].[UserProfile_Full] as UPF with (nolock)
 INNER JOIN [UPA-Profile-DB].[dbo].DNLookup as DN with (nolock)
 ON UPF.RecordID = DN.RecordId
where Manager is NUll

and

SELECT UPF.[NTName], Count(UPF.NTName)
FROM [UPA-Profile-DB].[dbo].[UserProfile_Full] as UPF with (nolock)
 INNER JOIN [UPA-Profile-DB].[dbo].DNLookup as DN with (nolock)
 ON UPF.RecordID = DN.RecordId
Group BY UPF.NTName
Having Count(UPF.NTName) > 1
Order by UPF.NTName

Solution

There is no magic solution here. Each profile that has an entry in the DNLookup table without the “MVID=” prefix must be deleted from the SharePoint User Profile. Then an incremental sync must be run to add back the users.

I scripted the solution as usual ūüôā

Import-module dbatools
$SQL_Instance = ‘[your SQL server instance]’
$UPSA_DBName = ‘[your UPA Profile DB]’

$UPQuery = “SELECT UPF.NTName, UPF.[PreferredName], DN.DN
FROM [$UPSA_DBName].[dbo].[UserProfile_Full] as UPF with (nolock)
INNER JOIN [$UPSA_DBName].[dbo].DNLookup as DN with (nolock)
ON UPF.RecordID = DN.RecordId
where DN.DN not like ‘MVID=%'”

$SPUser_ToRemove = Invoke-Sqlcmd2 -ServerInstance $SQL_Instance -Database $UPSA_DBName -Query $UPQuery -As PSObject

$gc = Start-SPAssignment
$mySiteHost = ($gc | Get-SPSite ‘https://%5Byour mysite host url]’)
$spContext = ($gc | Get-SPServiceContext -Site $mySiteHost)
$UPM = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($spContext)

Foreach ( $User in $SPUser_ToRemove ) {
Try {
Write-host “Removing user from Profile DB : ‘$($User.PreferredName)'”
$UPM.RemoveUserProfile($User.NTName)
} Catch {
Write-warning “An error occured while removing user ‘$($User.PreferredName)'”
}
}
Stop-SPAssignment $gc

Once the deletion process has finished, run an incremental sync to re-populate the users.

Resources

FIM Client GUI is located in¬†‘C:\Program Files\Microsoft Office Servers\15.0\Synchronization Service\UIShell\miisclient.exe‘.
It must be run as the same account declared in the Central admin.

$cred = Get-Credential ‘domain\[service account]’
Start-Process -FilePath ‘C:\Program Files\Microsoft Office Servers\15.0\Synchronization Service\UIShell\miisclient.exe’ -Credential $cred

 

 

Advertisements

SSRS SharePoint Mode Database migration

During the testing phase of the migration of our SSRS SharePoint mode from SharePoint 2013 to SharePoint 2016, I went out through some small issues. Here’s a quick recap.

  • Dismount, mount SPRS database
    After dismounting the fresh new RS database from the service application and mounting the migrated RS database, perform an IISReset in order to refresh the service application proxy.
$DB_OLD = <FreshSSRSDB>
$DB_Migrated = <SSRSDBFromSP2013>
$SSRSServiceApp =
Dismount-SPRSDatabase -Identity $DB_Old -Confirm:$false
Remove-SPRSDatabase -Identity $DB_Old -Force -Confirm:$false
Mount-SPRSDatabase -Name $DB_Migrated -ServiceApplication $SSRSServiceApp -DatabaseServer <SQLServer>
  • Reporting Services Databases status
    Review the status of the migrated databases in SharePoint and make sure they are online.
$DBs_Offline = Get-SPDatabase | Where-Object type -like ‘*Reporting*’ | Where-Object status -eq ‘Online’
if($DBs_Offline) {
foreach($DB_Offin$DBs_Offline) {
Write-Warning”Changing RS database ‘$($DB_Off.Name)’ status to ‘Online'”
$DB_Off.Status=’Online’
$DB_Off.Update()
}
}
  • Grant sufficient permissions on the RS databases
    Once the RS databases are mounted in SharePoint, you should run the SQL script granting permissions.
$sb_DBPermissions_ServiceAct = {
Add-PSSnapIn*sharepoint*
$RS_AppPoolAccount=(Get-SPRSServiceApplication).ApplicationPool.ProcessAccountName
(Get-SPRSDatabaseRightsScript-UserName $RS_AppPoolAccount-DatabaseName (Get-SPRSDatabase)-IsWindowsUser).Replace(‘ReportingServiceDatabase Name=’,”)
}
$sb_DBPermissions_CentralAdminAct = {
Add-PSSnapIn*sharepoint*
$RS_AppPoolAccount=(Get-SPServiceApplicationPool|Where-Object name -like’*security*’).ProcessAccountName
(Get-SPRSDatabaseRightsScript-UserName $RS_AppPoolAccount-DatabaseName (Get-SPRSDatabase)-IsWindowsUser).Replace(‘ReportingServiceDatabase Name=’,”)
}
$RS_SQL_GrantRights_ServiceAct = Invoke-Command -ScriptBlock $sb_DBPermissions_ServiceAct -ComputerName $SPServer_New -Credential $cred -Authentication Credssp
$RS_SQL_GrantRights_CentralAdminAct = Invoke-Command -ScriptBlock $sb_DBPermissions_CentralAdminAct -ComputerName $SPServer_New -Credential $cred -Authentication Credssp
Import-Module ‘SqlServer’
Invoke-Sqlcmd -ServerInstance <SQLServer> -Query $RS_SQL_GrantRights_ServiceAct
Invoke-Sqlcmd -ServerInstance <SQLServer> -Query $RS_SQL_GrantRights_CentralAdminAct
  • SQL Server Agent is running
    Make sure the Central Administration is configured with Kerberos if you want to see the status of the SQL Server Agent in the “Provision Subscriptions and Alerts” page.
  • Review the Reporting Services webservice logs
    Logs are located in the SharePoint Hive :
    c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\WebServices\LogFiles
$Cred = Get-Credential
$SPServer = <SPServer>
$RS_Log = “\\{0}\c$\Program Files\Common Files\microsoft shared\Web Server Extensions\{1}\WebServices\LogFiles\”
$sb_Auth = {
Add-PSSnapIn*sharePoint*
[PSCustomObject] @{
RS_Instances = Get-SPServiceInstance | Where-Object typename -like ‘*Reporting Services*’ | ForEach-Object {$_.Server.address}
RS_SPVersion = (Get-SPFarm).BuildVersion.Major
}
}
$RS_ServiceInstances = Invoke-Command -ScriptBlock $sb_Auth -ComputerName $SPServer -Credential $cred -Authentication Credssp
$RS_ServiceInstances.RS_Instances | ForEach-Object {
$RS_Log_Path=$RS_Log-f$_,$RS_ServiceInstances.RS_SPVersion
$Latest_LogFile=Get-ChildItem-Path $RS_Log_Path-Recurse -File | Sort-Object -Property Name -Descending |Select-Object-First 1
Write-Warning” — Showing logs from server $_ — “
Get-Content-Path $Latest_LogFile.FullName-Tail 15
}

 

 

SSRS Encryption key backup location

Backup the Report Services encryption key:

If you don’t specify the filepath optional argument when using the cmdlet “Backup-SPRSEncryptionKey”, the backup file will be saved in the following folder :

c:\windows\System32\[filename]

SharePoint, Powershell and FIM

Many, many posts can be found on the Internet regarding issues provisioning the User Profile Synchronization service with one of them being the Holy Grail :
Spencer Harbar’s Bible

Context :

2 of our 3 farms began to fail during the full farm backup procedure we perform every week. After looking in the ULS to check what was going on, it appeared that the UPSA failed to provision and was blocking the whole full farm backup.

The error found in the ULS was :

Event 9i1w : ILM Configuration : Error “ERR_CONFIG_DB”

Solution :

After digging a while, I found out it was linked to the Powershell Module “PSReadline“.

I add installed WMF 5.1 on the servers and installed the module at some point. What I didn’t knew was that the module PSReadline is loaded in all and every powershell console once it has been declared. It doesn’t appears in the profile files but it is still loaded.

This module seems to be in conflict with the UPSA provisioning method and makes the FIM installation crash at provisioning.

Lesson learnt :

DO NOT EVER CUSTOMIZE THE POWERSHELL CONSOLE ON SHAREPOINT SERVERS !!!!

ūüôā

SQL Server 2016 Configuration Manager

Another issue we came across, cannot use the SQL Server Configuration Manager from a machine where SQL Server isn’t installed.
This is kind of sad knowing that MS is pushing administrators to connect remotely on servers and make use of remote tools.

In order to use the SQL Server Configuration Manager snap-in integrated in the Computer Management MMC, you need at least the “SQL Server 20xx Common files” component which is installed when you install the Database Engine feature. You can check this by verifying directly in the uninstall section of the registry for MSI installations.

Get-ItemProperty -Path “HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\*” | Sort-Object Displayname | Select-Object DisplayName, DisplayVersion

So,¬†if you require the Configuration Manager, you need to install a local instance of the¬†Database Engine because the standalone version of SQL Server Management Studio doesn’t give you this ability.

Diagnostic

I used Process Monitor from SysInternals to find out that when loading the compmgmt.msc window on a machine having SQL¬†Server Database Enngine instance, it will load a specific DLL registered in the system. The ‘Computer Management’ snap-in will check if any extension has been registered for specific node types as defined here :
Computer Management Extensible Node Types
Here’s the flow :
First, it will read the extensions.
mRemoteNG_2017-03-15_15-19-24The MMC executable found an extension with the ID¬†{EE7F2DDB-1319-4227-8FD4-4EB51615D34A} referenced as ‘SqlcmSnapin’.
The ID {476E6449-AAFF-11D0-B944-00C04FD8D5B0} is the unique ID for the Computer Management snap-in (CompMgmt.msc).
It will then check the unique ID of the snap-in in the dedicated registry path ‘HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MMC\SnapIns’.
mRemoteNG_2017-03-15_15-20-46
This entry validates the snap-in and gives it a friendly name (SQL Server Configuration Manager). We also see a first reference to the file used by the snap-in (C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SqlManager.dll).
The MMC now will check if the snap-in is correctly registered in the local machine’s classes using the UID (HKEY_CLASSES_ROOT\CLSID{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}).
mRemoteNG_2017-03-15_15-21-41
Again, the entry is found and we can see a second reference to the DLL.

Once the registry keys have been validated, it will try to load the files located in the SQL Server common installation path ‘C:\Program Files\Microsoft SQL Server\130\Tools\Binn\’.
First, the ‘SQLManager.dll’ file and then the different resources files.
mRemoteNG_2017-03-15_15-22-48
mRemoteNG_2017-03-15_15-23-45

Knowing this, it was just a question of registering the correct files on my machine to force the loading of the SQL Server Configuration Manager snap-in.
For the files themselves, you need to copy them from an existing SQL Server installation though.

Below is the Powershell script to automate this little configuration :

$myserver = ‘tst-s04’

$SQLInstallPath = ‘Program Files\Microsoft SQL Server\130\Tools\Binn’

# Copy the SQLmanager dll file
If ( -not (Test-Path -Path “C:\$SQLInstallPath\SqlManager.dll”) ) {
Copy-Item -Path “\\$myserver\c$\$SQLInstallPath\SqlManager.dll” -Destination “C:\$SQLInstallPath”
}
# Copy the Resources folder
If ( -not (Test-Path -Path “C:\$SQLInstallPath\Resources”) ) {
Copy-Item -Path “\\$myserver\c$\$SQLInstallPath\Resources” -Destination “C:\$SQLInstallPath” -Recurse
}

# Register the snap-in keys
#Extension
If ( -not ((Get-ItemProperty “HKLM:\SOFTWARE\Microsoft\MMC\NodeTypes\{476E6449-AAFF-11D0-B944-00C04FD8D5B0}\Extensions\NameSpace”).'{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}’) -eq ‘SqlcmSnapin’ ) {
New-ItemProperty -Path “HKLM:\SOFTWARE\Microsoft\MMC\NodeTypes\{476E6449-AAFF-11D0-B944-00C04FD8D5B0}\Extensions\NameSpace” -Name ‘{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}’ -Value ‘SqlcmSnapin’ -PropertyType String
}
# MMC
$RegPath = ‘HKLM:\SOFTWARE\Microsoft\MMC\SnapIns\{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}’
If ( -not (Test-Path -Path $RegPath) ) {
New-Item -Path $RegPath -Force | Out-Null
New-ItemProperty -PropertyType String -Path $RegPath -Name ‘(default)’ -Value ‘SqlcmSnapin’
New-ItemProperty -PropertyType String -Path $RegPath -Name ‘NameString’ -Value ‘SQL Server Configuration Manager’
New-ItemProperty -PropertyType String -Path $RegPath -Name ‘NameStringIndirect’ -Value ‘@C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SqlManager.dll,-3’
New-ItemProperty -PropertyType String -Path $RegPath -Name ‘About’ -Value ‘{E84BEF4D-385C-4113-AE37-2795FE726A18}’
$RegPath = ‘HKLM:\SOFTWARE\Microsoft\MMC\SnapIns\{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}\NodeTypes’
If ( -not (Test-Path -Path $RegPath) ) { New-Item -Path $RegPath -Force | Out-Null }
$RegPath = ‘HKLM:\SOFTWARE\Microsoft\MMC\SnapIns\{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}\NodeTypes\{1D59FD70-D8B8-4425-B12B-72E32516A9E9}’
If ( -not (Test-Path -Path $RegPath) ) { New-Item -Path $RegPath -Force | Out-Null }
$RegPath = ‘HKLM:\SOFTWARE\Microsoft\MMC\SnapIns\{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}\NodeTypes\{B919722D-5ED6-44A2-A034-40C796E3E38E}’
If ( -not (Test-Path -Path $RegPath) ) { New-Item -Path $RegPath -Force | Out-Null }
$RegPath = ‘HKLM:\SOFTWARE\Microsoft\MMC\SnapIns\{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}\StandAlone’
If ( -not (Test-Path -Path $RegPath) ) { New-Item -Path $RegPath -Force | Out-Null }
}

# SQL Manager Class
If (-not (Get-PSDrive -Name ‘HKCR’ -ErrorAction SilentlyContinue) ) { New-PSDrive -Name ‘HKCR’ -PSProvider Registry -Root ‘HKEY_CLASSES_ROOT’ | Out-Null }

$RegPath = ‘HKCR:\CLSID\{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}’
If ( -not (Test-Path -Path $RegPath) ) {
New-Item -Path $RegPath -Force | Out-Null
New-ItemProperty -PropertyType String -Path $RegPath -Name ‘(default)’ -Value ‘SqlcmSnapin Class’
$RegPath = ‘HKCR:\CLSID\{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}\InprocServer32’
If ( -not (Test-Path -Path $RegPath) ) { New-Item -Path $RegPath -Force | Out-Null }
New-ItemProperty -PropertyType String -Path $RegPath -Name ‘(default)’ -Value ‘C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SqlManager.dll’
New-ItemProperty -PropertyType String -Path $RegPath -Name ‘ThreadingModel’ -Value ‘Apartment’
$RegPath = ‘HKCR:\CLSID\{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}\ProgID’
If ( -not (Test-Path -Path $RegPath) ) { New-Item -Path $RegPath -Force | Out-Null }
New-ItemProperty -PropertyType String -Path $RegPath -Name ‘(default)’ -Value ‘SQLManager.SqlcmSnapin.5’
$RegPath = ‘HKCR:\CLSID\{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}\VersionIndependentProgID’
If ( -not (Test-Path -Path $RegPath) ) { New-Item -Path $RegPath -Force | Out-Null }
New-ItemProperty -PropertyType String -Path $RegPath -Name ‘(default)’ -Value ‘SQLManager.SqlcmSnapin’
}

PowerPlan, WMI, GUI and Windows Server 2012

Howdy !

If you try to configure the power plan settings of your servers using DSC, you might come into the below issue if you are using one of the following resource : xPowerPlan or cPowerPlan. They both call WMI classname.

The WMI class ‘Win32_PowerPlan‘ of the namespace ‘root/cimv2/power’ is only available¬†when using a FULL GUI Windows Server 2012 (R2). If you switch to CORE flavor, the classname becomes inaccessible.

At the moment you are using a¬†Core only OS, this classname¬†doesn’t works anymore under Windows 2012 and 2012 R2 except for Windows 2016 Core.

You can quickly check the falvor of your OS with the following cmdlets :

$Computer = ‘yourMachine’
$sb = {
Get-ItemProperty ‘HKLM:\Software\Microsoft\Windows NT\CurrentVersion’ -Name Productname | Select -expa ProductName
Get-Item ‘HKLM:\Software\Microsoft\Windows NT\CurrentVersion\Server\ServerLevels’ | ft -AutoSize
}

icm -ScriptBlock $sb -ComputerName $Computer

Cheers,
MW

PowerShell DSC Encryption issue

Context

While working on a new setup, we had to deploy some binaries on a server using DSC.

To make the process scale for many machines, we created a network share to host the binaries in order to centralize the access. In the DSC world, this meant we had 2 options :

1- Add the computer account of each machine accessing the share in the permissions of the share.

2- Use the encryption feature in DSC (define an account in the MOF in order to access the share)

During the testing phase, everything went well. The configuration was as is :
Authoring machine : Windows 10, Powershell 5.1.14393.693
Target machine : Server 2016, Powershell 5.1.14393.0

Issue

We then¬†deployed the configuration on a Windows Server 2012 R2 and the LCM kept getting in error, throwing the message “Decryption failed. LCM failed to start desired state configuration manually.”
powershell_2017-01-19_10-00-07

Digging a little deeper in the ‘Microsoft-Windows-Desired State Configuration/Operational’ event log,¬†just before the “Decryption failed” error, another error was caught :
“Message Invalid provider type specified.”mremoteng_2017-01-19_10-23-16

With the help of the Internet and some querying, I was able to “decrypt” our issue. I began by reading this nice article :
https://hyper-v.nu/archives/bgelens/2015/02/integrating-vm-role-with-desired-state-configuration-part-7-creating-a-configuration-document-with-encrypted-content/
Which led me to review the certificate we used for the DSC encryption.

The initial setup was using a CA issued custom certificate we created following Microsoft’s recommendation stated here :
https://msdn.microsoft.com/en-us/powershell/dsc/securemof#certificate-requirements
firefox_2017-01-19_10-31-36
And since we wanted to follow the MS’ Best practices, we configured our certificate template using the provider ‘Microsoft RSA SChannel Cryptographic Provider’.

Solution

The caveat with this configuration is that Windows Server 2012 R2 doesn’t know how to decrypt¬†anything using the ‘Microsoft RSA SChannel Cryptographic Provider‘. Even if you deploy WMF 5.1 Preview, it won’t help.
If you use the Self-Signed certificate generator script, it will work flawlessly because it actually uses the legacy provider named ‘Legacy Cryptographic Service Provider‘.

Either you create a certificate template¬†using the Provider category ‘Legacy Cryptographic Service Provider’ thus not following Microsoft’s certificate requirements or you use only self-signed certificate using the custom script or you upgrade your OS to Windows 2016.

Regards,
MW

SharePoint 2013, AlwaysOn, Availability group and SQL alias

Bonjour,

I had a story at my company where I had to migrate the SharePoint databases to a fresh AlwaysOn Availability Group (AOAG) SQL Instance. This move wasn’t an issue until we found out that our backup script (which is performing a “Backup-SPFarm” cmdlet) failed to re-provision the User Profile¬†Synchronization (UPS) Service.

Context

The issue all came down from the¬†fact that the UPS database was now configured with the AlwaysOn feature, therefore, any operations to the DB couldn’t occur anymore.

The error message in the ULS was :

SqlError: ‘The operation cannot be performed on database “UserProfile-Sync-DB” because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.’¬†¬†¬† Source: ‘.Net SqlClient Data Provider’ Number: 1468 State: 1 Class: 16 Procedure: ” LineNumber: 5 Server: ‘AG Listener Name’

When the move to the new AlwaysOn SQL server happened, the operation went pretty smoothly since we are using SQL alias. We only updated our current alias name to redirect to the new SQL instance.

Updated configuration :
SQLAlias-AG-01

After some research, we found out that this is a “internet” known issue and can also occur on the Usage and Health database when performing patching of SharePoint farms.

Hence, we decided to modify the configuration and implement a better integration between SharePoint and AlwaysOn by using the SharePoint Database Availability Group cmdets. It will give us the flexibility to manage the databases in the AOAG directly from SharePoint.

In this initial setup, SharePoint isn’t aware of the AlwaysOn service running beneath¬†it. It only sees the SQL Alias and has no way to know which SQL server is running behind the SQL Alias ; it is a regular connection string.

By explicitly declaring to SharePoint that the databases are hosted by a HADR system, SharePoint Admins keep some visibility and control over the AOAG.

Availability Group Listener (AGL) and port attribution

When your AGL is configured to use the¬†default port (1433) and you don’t use SQL alias, you will surely have no issue when configuring your environment.
The troubles arises when you use SQL alias or have a custom port defined for the AGL, let’s say 25066 for the example.

  • When using a SQL alias (redirecting to the AGL), SharePoint will¬†fail retrieving the “Availability Group listeners”.
    SQLAlias-AG-02
  • When¬†using a custom port for the AGL, SharePoint will check the database server by using¬†a trimmed version of the data source.
    SQLAlias-AG-03
SQL Alias

You can use SQL Alias with Availability Group Listener as long as the SQL alias uses the same DNS entry as the AGL.

Availability Group Custom Port

You must use a SQL Alias to bypass the SharePoint port validation.

Solution (for both scenarios)

In both cases, you must use a SQL alias that mirrors the AGL dns name. For example, if your AGL is “AGListener” on port 25066.
SQLAlias-AG-05

Some in-depth details :

I was able to pinpoint this issue by decompiling the SharePoint assembly and verify the logic behind the powershell cmdlets. I found the root issue in the “UpdateDataSource” method of the Database object.
SQLAlias-AG-04
When trimming the datasource from its port, it then sends the port-less server string to the method “ChangeDatabaseInstance” which¬†itself calls “ValidateDatabaseServer”.
At this point, it won’t be able to validate the connection to the SQL server because¬†of the modified server string.

Cheers !
MW

PS:¬†I wasn’t able to find anything on the web regarding this particular issue except 1 slide that confirmed my troubleshooting.
Ref: http://blogs.technet.com/b/fromthefield/archive/2015/04/23/sharepoint-2013-amp-sql-alwayson-sharepoint-evolution-conference-2015.aspx РSlide 30

Quickly reset Firefox’s certificate store and exceptions

Hey there,

A quick PowerShell function to reset the certificate store and certificate exceptions for all firefox profiles. Can be handy in organisations when performing tests with certificate configuration and firefox.

shack/Reset-MAKFirefoxCertificateSettings.ps1


Function Reset-MAKFirefoxCertificateSettings{
# Certificate DB and exceptions reset
If(Get-Process -Name "firefox" -ErrorAction SilentlyContinue){Stop-Process -Name "firefox"}
Get-ChildItem -Path "$Env:APPDATA\Mozilla\Firefox\Profiles" -Recurse -Include "cert8.db","cert_override.txt" | ForEach-Object {
If(Get-ChildItem -Path "$Env:APPDATA\Mozilla\Firefox\Profiles" -Recurse -Include "$($_.Name).old" -ErrorAction SilentlyContinue){
Remove-Item -Path "$Env:APPDATA\Mozilla\Firefox\Profiles" -Recurse -Include "$($_.Name).old"
}
Rename-Item -Path $_ -NewName "$($_.Name).old"
}
}

Read-Host -Prompt "Press a key to continue and reset the Firefox settings."
Reset-MAKFirefoxCertificateSettings

Quickly generate PFX files for the Central Certificate Store from a SAN certificate

  • Define location of the certificate.
     $CertPath = 'C:\MyDisks\Certificates\MyCDN.mydomain.org.pfx' 
  • Load the certificate
     $Cert = Get-PfxCertificate -FilePath $CertPath

    [Enter the password in the credential prompt]
    OR

     $Pass = ConvertTo-SecureString -String 'TheSecurePasswordOfCertificate' -AsPlainText -Force
    [System.Security.Cryptography.X509Certificates.X509Certificate]::new($CertPath,$Pass) 
  • Generate the files using the DNS Names of the certificate
     $Cert.DnsNameList | %{Copy-Item -Path $CertPath -Destination C:\MyDisks\TMP\$($_.unicode).pfx}