Author Archives: Robert Haken

About Robert Haken

Software Architect, Founder at HAVIT, Microsoft MVP - ASP.NET/IIS

SQL: Index statistics update date

Simple query can help you get basic insights on when the index statistics where updated:

SELECT
		o.name AS TableName,
		i.name AS IndexName,
		STATS_DATE(i.object_id, i.index_id) AS StatisticsUpdate
	FROM sys.objects o
		INNER JOIN sys.indexes i ON (o.object_id = i.object_id)
	WHERE
		(i.type > 0)
		AND (o.type_desc NOT IN ('INTERNAL_TABLE', 'SYSTEM_TABLE'))
	ORDER BY TableName, IndexName
	-- ORDER BY StatisticsUpdate

See also:

SQL LocalDB: Upgrade to 2017 (14.0.1000)

For me it was quite confusing to find the 2017 version of LocalDB and it is not a streamline process to upgrade your local default instance. The link for “SQL Server 2017 Express LocalDB” on official website (https://www.microsoft.com/en-us/sql-server/sql-server-editions-express) leads to “SQLServer2016-SSEI-Expr.exe” which runs a SQL Server 2016 with SP2 installer. Now what?

The easiest way to upgrade your LocalDB instance to 2017 is:

  1. Download the LocalDB 2017 installer directly:
    https://download.microsoft.com/download/E/F/2/EF23C21D-7860-4F05-88CE-39AA114B014B/SqlLocalDB.msi
  2. Before running the installer, delete your current MSSQLLocalDB instance:
    sqllocaldb stop MSSQLLocalDB
    sqllocaldb delete MSSQLLocalDB
    
  3. Run the LocalDB 2017 installer. It will create a new MSSQLLocalDB instance.
  4. [OPTIONAL] If you did not delete the older instance before running the installer, you can delete it now and recreate the instance. It will be created as new version:
    sqllocaldb stop MSSQLLocalDB
    sqllocaldb delete MSSQLLocalDB
    sqllocaldb create MSSQLLocalDB
    
  5. Now you can re-attach your original databases using SQL Server Management Studio (RClick + Attach…)
  6. Done.

Credits:

Word: Replace hyphens with non-breaking ones

Word has a non-breaking hyphen (Ctrl+Shift+-). If you use it in a word, it does not break the line (in opposite to the regular hyphen).

If you want to mass-replace your regular hyphens in whole document (in my case, I wanted to print a morse-code quiz :-D) you can use the Replace (Ctrl+H) dialog.

Unfortunately the Replace dialog does not accept the Ctrl+Shift+- keyboard shortcut. You have to type ^~ to represent the non-breaking hyphen:2018-09-03_10-31-18

 

Azure App Service scheduled restart

If you want to restart your App Service on a scheduled basis, you can do that using simple PowerShell:

Stop-AzureRmWebApp -Name '_App Service Name_' -ResourceGroupName '_Resource Group Name_'
Start-AzureRmWebApp -Name '_App Service Name_' -ResourceGroupName '_Resource Group Name_'

Basically you have to solve two issues:

  1. How to schedule such Powershell script to run automatically at given times? We can use a simple WebJob for that.
  2. How to authenticate the execution? We should use a Service Principal Id for that.

Let’s start from the second one.

Credits: This is an updated and fixed version of a procedure originally published by Karan Singh – a Microsoft Employee on his MSDN blog.

Getting a Service Principal Id for authentication

It is not a good idea to use a real user-account for authentication of such a job. If you have an Organizational Account with 2-Factor Authentication, forget it.

The right way of authenticating your jobs is to use a Service Principal Id which allows you to proceed with silent authentication.

To create one, save and run following Powershell script from your PC (one-off task):


param
(
    [Parameter(Mandatory=$true, HelpMessage="Enter Azure Subscription name. You need to be Subscription Admin to execute the script")]
    [string] $subscriptionName,

    [Parameter(Mandatory=$true, HelpMessage="Provide a password for SPN application that you would create")]
    [string] $password,

    [Parameter(Mandatory=$false, HelpMessage="Provide a SPN role assignment")]
    [string] $spnRole = "owner"
)

#Initialize
$ErrorActionPreference = "Stop"
$VerbosePreference = "SilentlyContinue"
$userName = $env:USERNAME
$newguid = [guid]::NewGuid()
$displayName = [String]::Format("VSO.{0}.{1}", $userName, $newguid)
$homePage = "http://" + $displayName
$identifierUri = $homePage

#Initialize subscription
$isAzureModulePresent = Get-Module -Name AzureRM* -ListAvailable
if ([String]::IsNullOrEmpty($isAzureModulePresent) -eq $true)
{
    Write-Output "Script requires AzureRM modules to be present. Obtain AzureRM from https://github.com/Azure/azure-powershell/releases. Please refer https://github.com/Microsoft/vsts-tasks/blob/master/Tasks/DeployAzureResourceGroup/README.md for recommended AzureRM versions." -Verbose
    return
}

Import-Module -Name AzureRM.Profile
Write-Output "Provide your credentials to access Azure subscription $subscriptionName" -Verbose
Login-AzureRmAccount -SubscriptionName $subscriptionName
$azureSubscription = Get-AzureRmSubscription -SubscriptionName $subscriptionName
$connectionName = $azureSubscription.SubscriptionName
$tenantId = $azureSubscription.TenantId
$id = $azureSubscription.SubscriptionId

#Create a new AD Application
Write-Output "Creating a new Application in AAD (App URI - $identifierUri)" -Verbose
$secpasswd = ConvertTo-SecureString $password -AsPlainText -Force
$azureAdApplication = New-AzureRmADApplication -DisplayName $displayName -HomePage $homePage -IdentifierUris $identifierUri -Password $secpasswd -Verbose
$appId = $azureAdApplication.ApplicationId
Write-Output "Azure AAD Application creation completed successfully (Application Id: $appId)" -Verbose

#Create new SPN
Write-Output "Creating a new SPN" -Verbose
$spn = New-AzureRmADServicePrincipal -ApplicationId $appId
$spnName = $spn.ServicePrincipalName
Write-Output "SPN creation completed successfully (SPN Name: $spnName)" -Verbose

#Assign role to SPN
Write-Output "Waiting for SPN creation to reflect in Directory before Role assignment"
Start-Sleep 20
Write-Output "Assigning role ($spnRole) to SPN App ($appId)" -Verbose
New-AzureRmRoleAssignment -RoleDefinitionName $spnRole -ServicePrincipalName $appId
Write-Output "SPN role assignment completed successfully" -Verbose

#Print the values
Write-Output "`nCopy and Paste below values for Service Connection" -Verbose
Write-Output "***************************************************************************"
Write-Output "Connection Name: $connectionName(SPN)"
Write-Output "Subscription Id: $id"
Write-Output "Subscription Name: $connectionName"
Write-Output "Service Principal Id: $appId"
Write-Output "Service Principal key: <Password that you typed in>"
Write-Output "Tenant Id: $tenantId"
Write-Output "***************************************************************************"

You will be asked for a Subscription Name and Password for the Service Principal Id. You will also need to be an admin on your Azure Active Directory to be able to proceed.

Save the results securely, you can use the created Service Principal Id which gets the Owner role (or any other you specify) for many other administrative tasks (although it is a good idea to create a separate Service Principal for every single task).

2018-07-31_17-32-06.png

Scheduling the restart using PowerShell WebJob

Use any WebJob deployment procedure of your taste to create a scheduled Powershell WebJob executing following script:

$ProgressPreference= "SilentlyContinue"
$password = '_Service Principal Key/Password_'
$secpasswd = ConvertTo-SecureString $password -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ("_Service Principal Id_", $secpasswd)
Add-AzureRmAccount -ServicePrincipal -Tenant '_Tenant Id_' -Credential $mycreds
Select-AzureRmSubscription -SubscriptionId '_Subscription Id_'
Stop-AzureRmWebApp -Name '_App Service Name_' -ResourceGroupName '_Resource Group Name_'
Start-AzureRmWebApp -Name '_App Service Name_' -ResourceGroupName '_Resource Group Name_'

For manual deployment, you can use the Azure Portal directly:

 

  1. Save the script as run.ps1 file and create a ZIP archive with it (with any name).
  2. Go to App Service / Web Jobs and Add a new WebJob there:

2018-07-31_17-47-28.png

And it’s done. Just be sure to enable Always On for your App Service to execute the WebJobs on schedule.

You can Start the job manually from here (the Start button) if  you want to test it and you can verify the execution results using KUDU Dashboard (the Logs button).

 

Office365/Exchange: Remote Server returned ‘532 5.3.2 STOREDRV.Deliver; Missing or bad mailbox Database property’

We have a mail-enabled public folder called Accounting which has an auto-generated e-mail address Accounting@havit.onmicrosoft.com. We use this folder to archive all the accounting-related communication (e.g. by Bcc/Cc-ing all e-mails to Accounting@havit.onmicrosoft.com).

A few days ago we started using Microsoft Teams where we created a new team called Accounting.

Now whenever we try to send anything to Accounting@havit.onmicrosoft.com we get an error:

Remote Server returned ‘532 5.3.2 STOREDRV.Deliver; Missing or bad mailbox Database property’

We renamed the Microsoft Teams team to Accounting, Administration (anything else than Accounting) and the issued disappeared immediately.

…there has to be some weird bug in Office365/Exchange which breaks the message routing when there is a team and public folder name collision.

UPDATE: After few days, the issue reappeared for some scenarios. We had to adjust e-mail addresses of the public-folder and the team manually to fit our needs.

Managing your Microsoft Account-owned Azure Subscription with your Organizational Account (AAD)

There might be a situation where

  • you have an existing Azure Subscription associated with your Microsoft Account,
  • you want to manage the subscription using your Organizational Account(s) (Azure AD),
  • you don’t want to or cannot transfer the ownership of the subscription to the Organizational Account – e.g. it is a sponsored subscription where the sponsorship is related to a specific Microsoft Account (Microsoft Partner Network, MSDN Subscription, MVP Sponsorship, etc.).

The trick here is to change the directory of the subscription to your Azure AD directory. Changing the subscription directory is a service-level operation. It doesn’t affect your subscription billing ownership, and the Account Admin still remains the original Microsoft Account.

There are only a few simple steps to follow:

1. Invite the Microsoft Account to your Azure AD as a guest user

To be able to change the directory, your Microsoft Account owning the subscription must exist in the target Azure AD. To associate the MSA with the AAD:

  • Login to Azure Portal as the Azure AD administrator of the target AAD.
  • Open the Azure Active Directory blade.
  • Go to the Users section.
  • Click the + New guest user button at the top of the blade.
  • Invite your Microsoft Account to the Azure Active Directory.

2018-03-26_9-50-53

2. Accept the invitation of your Microsoft Account to AAD

Now you have to accept the invitation…

  • You will receive an invitation e-mail to the mailbox associated with your Microsoft Account.
  • Do not click the Accept Invitation button from your e-mail client as it usually opens the web page in your browser where you are logged in using your Organizational Account and the invitation acceptance will fail.
  • Instead, copy the target URL of the button and open it in a New incognito window  (or In-private window or whatever it is called in your browser). (Alternatively you can sign out from your Organizational Account.)
  • Login using your Microsoft Account when asked for the credentials.
  • After accepting the invitation you will probably end up at the Applications page of the AAD (often empty), which might be a little confusing, but the association is done. You can close this browser window.

3. Change the directory of the subscription

Now you can change the directory of the subscription:

  • Sign in to Azure Portal using your Microsoft Account.
  • Navigate to your subscription and open the Subscriptions blade (you can type “subscription” in the search box).
  • Click the Change directory button in the top-row of the blade.
  • In the Change the directory panel you should have an option to select your Azure AD as a target directory for your subscription.
  • Confirm the change (Change button below).

Now you have to wait up to 10 minutes for the change to take effect.

2018-03-26_9-49-25

4. Add permissions to your Organizational Account

To be able to manage the subscription by your Organizational Account, you have to add permissions to it (still signed in with the original Microsoft Account).

  • In the Subscription blade switch to the Access control (AIM) section.
  • Add your Organizational Account with an Owner role to the subscription level.
  • Right-click the Organizational Account added and click Add as co-administrator in the context menu. (An optional step for legacy scenarios where the co-administrator privilege is still needed).

2018-03-26_9-22-25

5. Done

  • Sign-out from your Microsoft Account and sign up using your Organizational Account to verify you are able to manage your subscription from there.

iTunes – high CPU usage on Windows

My iTunes (currently 12.7.3.46) consumes ~25-30% CPU whenever I listen to any music.

The only trick I found to resolve this is to switch it to the “Miniplayer mode”, e.g. using Ctrl+Shift+M keyboard shortcut (use the same shortcut to switch back).

It has to be a weird glitch in iTunes, but the CPU usage almost immediately drops to 1-2% CPU.