Search This Blog

Thursday, August 03, 2017

OneDrive for Business - alternative email used in user look up for sharing

Hi All,
Just run across this issue, whilst trying to test OneDrive sharing setup.

Was trying to send myself a test share from within OneDrive for Business, however on typing in my home email address OneDrive helpfully looks this up in the Office365 directory, and returns my work email???!! Couldnot understand why this was happening. Tried some other colleagues and used their personal emails, same issue.
In the end I made a support call to Microsoft to understand what was going on.

It turned out to be an alternative email setup on the user account. Why Office365 is using this to look up a user, and then defaulting to the business account is beyond me.
Talking to the engineer MS are aware of the issue, so hopefully they will fix this and remove the alternative email from the lookup.

In the interim, the work around is to go to the user details in Office365, edit the role and then change the alternative email to another email. Yup this is a pain but at the moment there is no way 
to override this.
My community posting about this.
MS Support article for OneDrive Sharing


Tuesday, May 30, 2017

Check Backup\Restore status

When running the AG wizard it is not clear what the percentage completion figure is of each stage.

Use the query below to examine the status of backup and restore operations.

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')





Tuesday, May 23, 2017

Central Management Servers - HA


Recently setup a new Central management Server for SQL Server, whilst doing this I was hoping to use HA, however CMS stores its config in the msdb database, and as such is not able to be HA’d… Sad smile

So… I started looking at a way for me to utilise HA to achieve this.

In the end I came up with using Always On listeners and powershell script to sync between two servers.

Because you can’t add a CMS server to the list, I decided to create a very small Always on environment to just deal with the CMS. I am not going to go through how to setup Always on or how to setup named instances.

Two servers and one instance.

  • DEV-DB01\CMS
  • DEV-DB02\CMS.

The listener was set on


So by doing this DEV-DBCMS would point at whatever server was the primary server. Connecting SSMS to the DEV-DBCMS, would point it at either DEV-DB01 and DEV-DB02 changes made in CMS would only appear on the servers DEV-DBCMS was pointing at.

So I came up with a PowerShell script that will use the DEV-DBCMS as the source of truth (and this would be whatever the primary server was in the HA), and then sync to the other server (in fact it syncs to itself as while, but makes no changes as it will always match). The PowerShell script is run every 5 minutes and from what I can see is working very well. This is not ideal as there is the possibility that changes can be lost if a failover occurs before a change to CMS has sync’d, but that should not occur that often and the benefit of having the CMS always online in some manner is more important.

This script is a modification on some CMS scripts developed by Chrissy LeMaire

I used the following command in SQl Server job to run the powershell script on a schedule of 5 minutes. I had the same job running on both servers in the HA, with jobs staggered by 2 minutes.

SQL Server Job Command

powershell –ExecutionPolicy Bypass -Command C:\Scripts\CMS_SyncToServer.ps1 dev-dbcms dev-db01\cms; powershell –ExecutionPolicy Bypass -Command W:\Scripts\CMS_SyncToServer.ps1 dev-dbcms dev-db02\cms;

Powershell Script

param([string]$fromcms, [string]$tocms)

$cr = "`r`n"

Write-Host "######################################$cr" -ForegroundColor Red
Write-Host "Starting Sync for $fromcms to $tocms $cr" -ForegroundColor Red
Write-Host "######################################$cr" -ForegroundColor Red

#For SQL 2012 Microsoft.SqlServer.Smo.dll Client Tools SDK is needed
#For SQL 2012 Servers, Microsoft.SqlServer.Management.RegisteredServers.dll must be copied from an newer version of SQL Server (2016 etc)

#Add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll'
#Add-Type -Path 'C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Management.RegisteredServers.dll'

#For older versions of powershell.

$fromserver = New-Object Microsoft.SqlServer.Management.Smo.Server $fromcms
$toserver = New-Object Microsoft.SqlServer.Management.Smo.Server $tocms
try {
    $fromcmstore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($fromserver.ConnectionContext.SqlConnectionObject)
    $tocmstore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($toserver.ConnectionContext.SqlConnectionObject)
catch { throw "Cannot access Central Management Servers$cr" }
Function Parse-ServerGroup($fromserverGroup, $toservergroup)
    # Remove Servers
    foreach ($instance in $toservergroup.RegisteredServers)
        $instancename = $
        if($ -notcontains $instancename)
            Write-Host "Deleting Server $instancename$cr" -ForegroundColor Red
            $oldserver = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer($toservergroup, $instancename)
            $oldserver.ServerName = $instance.ServerName
            $oldserver.SecureConnectionString = $instance.SecureConnectionString
            $oldserver.ConnectionString = $instance.ConnectionString
            $oldserver.Description = $instance.Description
            Write-Host "Deleted Server $servername$cr" -ForegroundColor Red
    # Add Servers
    foreach ($instance in $fromserverGroup.RegisteredServers)
        $instancename = $
        if($ -notcontains $instancename)

            if ($instance.ServerName.ToLower() -eq $tocmstore.DomainInstanceName.ToLower())
                Write-Warning "Server name is Central Management Server name. Add prohibited. Skipping.$cr"

            Write-Host "Adding Server $instancename$cr"
            $newserver = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer($toservergroup, $instancename)
            $newserver.ServerName = $instance.ServerName
            $newserver.SecureConnectionString = $instance.SecureConnectionString
            $newserver.ConnectionString = $instance.ConnectionString
            $newserver.Description = $instance.Description
            Write-Host "Added Server $servername$cr" -ForegroundColor Green
            Write-Warning "Server $instancename already exists. Skipped$cr"

    # Add Groups
    foreach($fromsubgroup in $fromserverGroup.ServerGroups)
        $tosubgroup = $toservergroup.ServerGroups[$]
        if ($tosubgroup -eq $null)
            Write-Host "Creating group $($$cr" -ForegroundColor Green
            $tosubgroup = New-Object Microsoft.SqlServer.Management.RegisteredServers.ServerGroup($toservergroup,$
        Parse-ServerGroup -fromserverGroup $fromsubgroup -toservergroup $tosubgroup

foreach ($element in $array) {

foreach ($serverGroup in $fromcmstore.DatabaseEngineServerGroup)
    Parse-ServerGroup -fromserverGroup $serverGroup -toservergroup $tocmstore.ServerGroups[$]


Sunday, May 21, 2017

SQL Server - Use specific IP/Ports not all

Hi All,

Just something that threw me this morning and took a while to understand what was going on.

note: to disable dynamic ports, just delete the value in the field. To reenable just add 0 and restart the sql instance.

If you need\want your SQL server instance to only listen on specific IPs (and not all IPs in the machine), then you must set “Listen All” on the protocol tab to “No”, whilst this seems intuitive now there is an IP All section on the IP Addresses tab, that when you look at articles on the interwebs, this is the setting you change.


IPAll section on the IP Addresses tab


Then in the IP section you want to apply to, set the Enabled setting to Yes, restart the SQL server instance and the server should now be listening on only the server IP.

This does not affect the listeners which are on different IPs, and handled by the cluster and alwayson services.



SQL Server 2012: AlwaysOn High Availability error 9692: Endpoint port

Had an issue where I was unable to get the “New Availability Group” wizard to successfully complete.

It would continually error with a 9692 Error, indicating that the endpoint couldn't be setup on port 5022.

I had expected this and on the endpoint tab I had changed to a different port (the servers already had other instances running on port 5022), however even though I changed the wizard to an unused port (i.e 5025), the error kept coming back with could not create end point on port 5022.

I had not come across this before and I cannot find anything about it on the interwebs..

The way I got around this was to manually setup the endpoints, before running the wizard (Changing the port to the desired value).

-- Create a new Database Mirroring Endpoint on Replica 1
   ROLE = ALL,

-- Start the Endpoint on Replica 1


When the wizard got to the endpoint it then listed these endpoints and I was able to continue through (the ports are greyed out and can no longer be changed)


Would love to know why on this occasion the wizard failed, as I have happily changed the port in the past but this work around got me through.


Monday, May 15, 2017

Changing SQL Server Named Instance from dynamic port to static port - (“The target principal name is incorrect.  Cannot generate SSPI context. “) fun!!

Recently had an issue where an engineer had decided to change an existing SQL Server from using dynamic ports to a static port.

The server was a named instance.


Having made the change using SQL Server Management Studio (SSMS) on the actual server he did not notice that access to the server instance via SSMS remotely had stopped working.

Trying to connect remotely to the server resulted in a

“The target principal name is incorrect.  Cannot generate SSPI context. “

error dialog appearing.

after a bit of investigation it was clear this was a problem with the Service Principal Name (SPN) that had been created. There are a number of troubleshooting articles about this issue and I list them in the references below.

One of the solutions is to remove and recreate the SPN. This involves using the setSPN.exe, however you need to know structure the URLs. However whilst looking at this I cam across this

Kerberos Configuration Manager for SQL server

This tool actual checks your SPNs and will generate a script to fix it, or you can just click fix and it will run the script.

In the end this is all I used to fix my issue





Friday, April 28, 2017

AzureRM - Powershell Notes

Run POWERSHELL as admin

Get Verison of PowerShellGet

Get-Module PowerShellGet -list | Select-Object Name,Version,Path

What is PowerShellGet

I had to use –allowclobber to force the install.

Install-Module AzureRM (–allowclobber)

Load the module

Import-Module AzureRM

Login to azure



Set-AzureRmContext -SubscriptionName "Example Subscription Two"

New-AzureRmResourceGroup -Name TestRG1 -Location "South Central US"

Get-AzureRmResourceGroup -ResourceGroupName TestRG1

(returns all ResourceGroups)



Tuesday, April 25, 2017

Setting up DNS on openwrt on TL-WDR4300

Found a couple of articles that helped, referenced below. Had to connect to router via SSH, as luci does not have the interface for the added packages.

Note: I was unable to install the dhcp server  in the article, although I did manage to install another dhcp package. This said I do not use dhcp and I didnot test whether dhcp worked.

This did not install

opkg install isc-dhcp-server-ipv4

I was able to install the following package from the openwrt site.

opkg install

Note: I have been running this for months now, and I have just had a weird issue where dns stopped working. SSHing onto the router, and performing a dig worked fine just remote dns was not working. I tried rebooting the router and that didnot fix it. However I just restarted the bind service and all has started working again. Not sure what the actual problem was.

restart the dns service: /etc/init.d/named restart/etc/init.d/named restart

We remove the dnsmasq service which is a combined DHCP and DNS (forwarder) server and replace this with separate DNS and DHCP services.

I then followed the article

Changing values where needed (examples below)

I used the following acl in the named.conf file, note two ip ranges as my router is setup as a bridging client.

acl "trusted" {;;

options {
        directory "/tmp";
        recursion yes;
        allow-recursion { trusted; };
        allow-transfer { trusted; };
        allow-query-cache { trusted; };
        // If your ISP provided one or more IP addresses for stable
        // nameservers, you probably want to use them as forwarders.
        // Uncomment the following block, and insert the addresses replacing
        // the all-0's placeholder.

        forwarders {

        auth-nxdomain no;    # conform to RFC1035

Created folder to hold the zone files

mkdir /etc/zones
using existing zone files as templates.
cp /etc/bind/db.local /etc/bind/zones/db.homedomain.local
for reverse lookup (two zones as I have created two subnets)
cp /etc/bind/db.127 /etc/bind/zones/db.75.168.192
cp /etc/bind/db.127 /etc/bind/zones/db.75.75.172
Then edited the files for my needs

; BIND data file for reverse look up of 192.168.75/24
$TTL    604800
@       IN      SOA     homedomain.local. root.homedomain.local. (
                             10         ; Serial
                         604800         ; Refresh
                          86400         ; Retry
                        2419200         ; Expire
                         604800 )       ; Negative Cache TTL
@      IN      NS      router.homedomain.local.
11     IN      PTR     machine1.homedomain.local.
18     IN      PTR     router.homedomain.local.
110    IN      PTR     machine3.homedomain.local.

; BIND data file for reverse look up of 172.75.75/24
$TTL    604800
@       IN      SOA     homedomain.local. root.homedomain.local. (
                             10         ; Serial
                         604800         ; Refresh
                          86400         ; Retry
                        2419200         ; Expire
                         604800 )       ; Negative Cache TTL
@       IN      NS      router.homedomain.local.
1       IN      PTR     router.homedomain.local.
2       IN      PTR     machine4.homedomain.local.
10      IN      PTR     machine5.homedomain.local.

; BIND data file for homedomain.local zone
$TTL    604800
@       IN      SOA     homedomain.local. root.homedomain.local. (
                             10         ; Serial
                         604800         ; Refresh
                          86400         ; Retry
                        2419200         ; Expire
                         604800 )       ; Negative Cache TTL
@       IN      NS      router.homedomain.local.
@       IN      A
router  IN      A
machine4        IN      A
machine5        IN      A
machine3        IN      A
machine1        IN      A


Wednesday, February 22, 2017

PowerShell: List domain users with specific homedirectory folder

Recently had to get a list of users within active directory that were pointing at a specific file servers.

Came up with the following PowerShell, searching a specific domain and then looks for servers


  • The \">\">\\*<servername>\*, is because I found that the command had a issue with certain servernames if I just put \">\">\\<servername>\*. I spent a little time looking at this but couldnot find a reason and by slipping in \\* just made it work consistently.

-SearchBase "DC=<domain>,DC=local" 

with your domain… i.e. if your fqdn is company.local then this would read

-SearchBase "DC=company,DC=local"

  • Also replace <servernameX> with your server names with your domain… i.e. if your fqdn is company.local then this would read


Get-ADUser -Filter {(homedirectory -like '\\*<servername1>\*' -or homedirectory -like '\\*<servername2>\*') -and (Enabled -eq "True")}  -SearchBase "DC=<domain>,DC=local" -Properties homedirectory | Select-Object name, enabled, homedirectory