Automate Azure SQL Size using Azure Automation (Manual)

Recently a customer asked me how to save cost on their Azure SQL database without moving away from DTU based subscription model. In this case this customer knows exactly at what time their database is heavily utilized, and when it’s idling. So with a script its easy to automate.

In this manual we are going to size a SQL database from S4 to S3.

Step 1: In this first step we are going to add some modules to your Automation Account. Go to modules, and click on Browse gallery

From the Gallery search for az.accounts, click on it

Next make sure to Import the module

Now browse the Gallery again, this time search for az.sql and make sure to import this module as well.

STEP 2: This next step is important. We will need to create and assign a Run As Account when you’ve chosen not to create a run as account on the setup of your automation Account. Go to Run as Account, and click on Create Azure Run As Account

Click on Create

STEP 3: Now we will need to add some variables to your automation account. These variables will need to be filled with information about your Azure SQL Database and Server. Create the following variables, and make sure that you fill them.

  • Resourcegroup
  • Servername (without database.windows.net)
  • Database

STEP 4: Now go to runbooks, and create a new runbook!

Give your runbook a name, as type select PowerShell!

In the new opened window copy and paste the code from below. Adjust the variables $Edition and $PricingTier to your needs.

 $ResourceGroupName = Get-AutomationVariable -Name "Resourcegroup"
 $ServerName = Get-AutomationVariable -Name "Servername"
 $DatabaseName = Get-AutomationVariable -Name "Database"
 $Edition = "Standard"
 $PricingTier = "S4"

 
# Keep track of time
$StartDate=(GET-DATE)
 
 
 

# Log in to Azure with AZ (standard code)

Write-Verbose -Message 'Connecting to Azure'
  
# Name of the Azure Run As connection
$ConnectionName = 'AzureRunAsConnection'
try
{
    # Get the connection properties
    $ServicePrincipalConnection = Get-AutomationConnection -Name $ConnectionName      
   
    'Log in to Azure...'
    $null = Connect-AzAccount `
        -ServicePrincipal `
        -TenantId $ServicePrincipalConnection.TenantId `
        -ApplicationId $ServicePrincipalConnection.ApplicationId `
        -CertificateThumbprint $ServicePrincipalConnection.CertificateThumbprint 
}
catch 
{
    if (!$ServicePrincipalConnection)
    {
        # You forgot to turn on 'Create Azure Run As account' 
        $ErrorMessage = "Connection $ConnectionName not found."
        throw $ErrorMessage
    }
    else
    {
        # Something else went wrong
        Write-Error -Message $_.Exception.Message
        throw $_.Exception
    }
}

  

# Getting the database for testing and logging purposes

$MyAzureSqlDatabase = Get-AzSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName
if (!$MyAzureSqlDatabase)
{
    Write-Error "$($ServerName)\$($DatabaseName) not found in $($ResourceGroupName)"
    return
}
else
{
    Write-Output "Current pricing tier of $($ServerName)\$($DatabaseName): $($MyAzureSqlDatabase.Edition) - $($MyAzureSqlDatabase.CurrentServiceObjectiveName)"
}


# Set Pricing Tier Database

# Check for incompatible actions
if ($MyAzureSqlDatabase.Edition -eq $Edition -And $MyAzureSqlDatabase.CurrentServiceObjectiveName -eq $PricingTier)
{
    Write-Error "Cannot change pricing tier of $($ServerName)\$($DatabaseName) because the new pricing tier is equal to current pricing tier"
    return
}
else
{
    Write-Output "Changing pricing tier to $($Edition) - $($PricingTier)"
    $null = Set-AzSqlDatabase -DatabaseName $DatabaseName -ServerName $ServerName -ResourceGroupName $ResourceGroupName -Edition $Edition -RequestedServiceObjectiveName $PricingTier
}
 




# Show when finished

$Duration = NEW-TIMESPAN –Start $StartDate –End (GET-DATE)
Write-Output "Done in $([int]$Duration.TotalMinutes) minute(s) and $([int]$Duration.Seconds) second(s)"
 

Use the menu to Save your runbook, use the Test pane to review the output of your PowerShell script. When ready Publish your runbook!

STEP 5: Last step is to create a schedule. From your workbook go to Schedules, and Add an schedule.

Create a new schedule based on your requirements/needs.

Click create to finalize the process. Now go back to your SQL database. When the change is happening, you should see a update line like below that shows that the pricing tier is being updated!

How to re-enable inactive mailbox from litigation hold in exchange online using Power Shell(Manual)

When users leave the company you might want to retain the email for a longer period than the default 30 days. By enabling litigation hold you can retain mailboxes longer than 30 days, before you disable a user you can set the litigation hold to any value you would like. But at some point you might need the mailbox to be re-enabled for some reason. In this manual I am going to explain how to do it.

STEP 1: Open a new Power Shell window and type the following command

Import-Module ExchangeOnlineManagement
Continue reading “How to re-enable inactive mailbox from litigation hold in exchange online using Power Shell(Manual)”

Azure Automation: Run SQL command on Azure SQL (Manual)

How cool would it be to automate your daily SQL tasks using Azure Automation? Well, really cool off course! So lets start using Azure Automation! So go ahead, if you don’t have an automation account yet, create one by going to Automation Accounts.

Give your automation account an name, choose a subscription, resource group and a location and hit the create button!

Continue reading “Azure Automation: Run SQL command on Azure SQL (Manual)”

Setup Azure File Share with AD authentication (Manual)

With the traditional file server coming to a end, it is time to move along with Azure File Share and AD authentication.

This image has an empty alt attribute; its file name is image-1.png

Pre-requisites:

STEP 1: First, let’s create a new storage account

Continue reading “Setup Azure File Share with AD authentication (Manual)”

Lock down Microsoft Team creation (Manual)

By default everyone may create a new team in Microsoft Teams. As an organisation admin you might want to control this, or release it a some point. With this manual you should be able to lock down team creation to users that are member of a Azure AD Security group.

STEP 1: First we will need to install the Preview version of the Azure Active Directory PowerShell module for Graph. Open a PowerShell window with Adminstrator privileges and run the following 2 commands:

Uninstall-Module AzureAD
Install-Module AzureADPreview

STEP 2: Now we will need to connect to Azure-AD to perform the necessary actions. Sign in with an admin account when prompted.

#Connect to AAD
$AzureAdCred = Get-Credential 
Connect-AzureAD -Credential $AzureAdCred

STEP 3: In Azure AD using the Azure portal (https://portal.azure.com), create a new security group.

STEP 4: Enter the name of your security group on the top line, and run the following script.

$GroupName = "Your Security Group Name"
$AllowGroupCreation = "False"

$settingsObjectID = (Get-AzureADDirectorySetting | Where-object -Property Displayname -Value "Group.Unified" -EQ).id
 if(!$settingsObjectID)
 {
       $template = Get-AzureADDirectorySettingTemplate | Where-object {$_.displayname -eq "group.unified"}
     $settingsCopy = $template.CreateDirectorySetting()
     New-AzureADDirectorySetting -DirectorySetting $settingsCopy
     $settingsObjectID = (Get-AzureADDirectorySetting | Where-object -Property Displayname -Value "Group.Unified" -EQ).id
 }
 $settingsCopy = Get-AzureADDirectorySetting -Id $settingsObjectID
 $settingsCopy["EnableGroupCreation"] = $AllowGroupCreation
 if($GroupName)
 {
     $settingsCopy["GroupCreationAllowedGroupId"] = (Get-AzureADGroup -SearchString $GroupName).objectid
 }
  else {
 $settingsCopy["GroupCreationAllowedGroupId"] = $GroupName
 }
 Set-AzureADDirectorySetting -Id $settingsObjectID -DirectorySetting $settingsCopy
 (Get-AzureADDirectorySetting -Id $settingsObjectID).Values

The result of the script should give you the updated settings. On the last line you should see EnableGroupCreation. If you want to reverse this setting. Just simply change the following line to True and run the entire script:

$AllowGroupCreation = “True”

If you want another security group, rerun the script with the new group name.

Change default email address Office 365 group (Manual)

Office 365 Groups are easy to create. However, changing the primary domain name when creating the group might not be that easy from the GUI. However, with Power Shell you can change this easily.

First we will need to open a Power Shell Window, and connect with Exchange Online.

$Credential = Get-Credential

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $Credential -Authentication Basic -AllowRedirection

Import-PSSession $Session

Next, we just need to change the 2 value’s below, and run it. After running, you don’t get a confirmation. It might take up to 30 minutes before changes are visible in all Office 365 and/or Azure portals.

Set-UnifiedGroup –Identity "Group name" –PrimarySmtpAddress primaryaddress@2azure.nl

Sources:
https://docs.microsoft.com/en-us/powershell/module/exchange/users-and-groups/set-unifiedgroup?view=exchange-ps

Credits: Martin van de Giessen

Convert AD domain users to Azure AD users (Manual)

With the move to the cloud there might be a time where you would like to remove the Active Directory link (AD Connect) and go for a cloud only strategy. With a few simple steps you can disconnect the AD connect sync from Azure AD.

When you look in your Office 365 environment you will notice that the sync status has different symbols. One for cloud only, and one for Active Directory. To disable the link, open a PowerShell window and run the following steps.

STEP 1: First make sure that you disable the AD Connect sync service by disabling the service, or set it to staging mode.

STEP 2: Connect to your Microsoft Office 365 environment using the following command, and login to the desired environment:

connect-msolservice

STEP 3: Now run the following command to disable the sync, confirm your actions, you cannot undo this change!

Continue reading “Convert AD domain users to Azure AD users (Manual)”

Office 365 Set language and time zone for all users with PowerShell (Manual)

When you create a new Office 365 tenant, all user mailboxes will have the default timezone and language. In my case, I work in the Netherlands, the preference for most companies is to set the Time zone to Central European Time (GMT +1) and the language of the users default folders to Dutch.

You can either ask the users to logon to webmail using https://outlook.office.com and fill in the first time question to set the time zone and default language. But how cool would it be to do this for all your users using PowerShell?

First time login screen Outlook Web Access
Continue reading “Office 365 Set language and time zone for all users with PowerShell (Manual)”

Azure Risk based conditional access explained and how to set it up!

With the Azure AD Premium P2 license you are entitled for Azure AD Identity Protection. You will get the option in Conditional Access to assign risk level based options to your policies. Azure AD Identity Protection can detect six different types of suspicious sign-in activities with 3 different levels of risks.

Six suspicious sign-in activities and 3 risk levels

With the riks levels combined with conditional access policies we can protect sensitive application and data access. With this article I am going to show you how to create risk-based conditional access policies

So let’s create a Policy and get Conditional Access applied with risk levels

Step 1: Log in to the Azure Portal: https://portal.azure.com

Continue reading “Azure Risk based conditional access explained and how to set it up!”

PowerShell script to export and import legacy Exchange x500 addresses (Manual)

When you’re migrating from one Exchange environment to another, or from on-premise to Exchange online without using the hybrid setup, the most forgotten part is the migration of the users x500 address. The reason why this is so important is because Exchange uses this to deliver local emails instead of the SMTP address that is normally associated with email. (This also goes along for calendar appointments)

So, by not migrating the x500 address it means that communications will fail when changing calendar appointments, or replying on old emails. To prevent this we will need to export the ExchangeLegacyDN from Active Directory, and import it again as a ProxyAddress in Active Directory.

Export the x500 address (ExchangeLegacyDN)

Step 1: From your source Active Directory, look up the distinguishedName, and copy the content of the value.

Continue reading “PowerShell script to export and import legacy Exchange x500 addresses (Manual)”

Enforce (Azure) MFA with Conditional Access policies

Multi Factor Authentication (MFA) is an added security feature from Azure which I believe that should be enabled by default for everybody in Office 365 and Azure. There for this manual how to enforce (Azure) MFA for all users using Azure Multi Factor Authentication

MFA can prevent unauthorized access in case of the following events:

  • Leaked credentials
  • Sign-ins from anonymous IP addresses
  • Impossible travel to atypical locations
  • Sign-ins from unfamiliar locations
  • Sign-ins from infected devices
  • Sign-ins from IP addresses with suspicious activities

Using Conditional access we can ensure that your users and company data is safe. Important to know is that Office 365 MFA is free of charge, and if you have Azure AD applications an Azure AD Premium license is required.

Named location

If you want to mark your locations as trusted location, you can do that if you have a static public IP. So the first steps are there to define your office locations.

Continue reading “Enforce (Azure) MFA with Conditional Access policies”

Exchange Online: Set default calendar sharing permissions for all users

In a new Exchange (Online) environment you might want to change the default calendar sharing permissions for all users. By default the sharing permissions for the entire organization are set to “Can view when I’m busy”.

Some companies have a different wish on the default calendar settings of their users. The preferred setting might be “Limited details”. This will show just the headlines and location of the calendar.

If you try to open an invite, it will notify that you do not have access.

So, what options do we have? From the Outlook app you can see that there are 5 options to choose from. (See screenshot below)

Continue reading “Exchange Online: Set default calendar sharing permissions for all users”

Change default send items behavior of Auto-mapped Shared Mailboxes

A commonly heart end-user frustration with Auto-mapped shared mailboxes is that Send emails from the shared mailbox end up in the send items of the user it self. In the past you would need to set a registry key on the client computer to get this resolved. But with Office 365, there is an easy way to change this behavior for every user.

PowerShell

With PowerShell this job is done in less than a minute in just 2 simple steps.

STEP 1: First connect to Exchange Online using the following commands:

$UserCredential = Get-Credential

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection

Import-PSSession $Session 

STEP 2: Now run the following command to set the default behavior for all Shared Mailboxes in your Exchange Online environment.

Get-Mailbox | Where {$_.RecipientTypeDetails -eq “SharedMailbox”} | Foreach-Object {Set-Mailbox -identity $_.Alias -MessageCopyForSentAsEnabled $True } 

Azure SQL update statistics (Manual)

I recently run into a case where I needed to update statistics of an Azure SQL Database because of poor performance and deadlocks. Preventing disruptions is key, so it is important to do something about it. With a simple script we can update the statistics easaly.

Why should I update statistics?

SQL Server statistics are essential for the query optimizer to prepare an optimized and cost-effective execution plan. These statistics provide distribution of column values to the query optimizer, and it helps SQL Server to estimate the number of rows. The query optimizer should be updated regularly. Improper statistics might mislead query optimizer to choose costly operators such as index scan over index seek and it might cause high CPU, memory and IO issues in SQL Server. We might also face blocking, deadlocks that eventually causes trouble to the underlying queries, resources.

The script

Just execute the following query on your database and you should be good to go! Keep in mind, depending on your database this might take a while. During this script your database will get slow, but will remain online.

SET NOCOUNT ON
 GO

 DECLARE updatestats CURSOR FOR
 SELECT table_schema, table_name  
 FROM information_schema.tables
        where TABLE_TYPE = 'BASE TABLE'
 OPEN updatestats

 DECLARE @tableSchema NVARCHAR(128)
 DECLARE @tableName NVARCHAR(128)
 DECLARE @Statement NVARCHAR(300)

 FETCH NEXT FROM updatestats INTO @tableSchema, @tableName

 WHILE (@@FETCH_STATUS = 0)
 BEGIN
    SET @Statement = 'UPDATE STATISTICS '  + '[' + @tableSchema + ']' + '.' + '[' + @tableName + ']' + '  WITH FULLSCAN'
    EXEC sp_executesql @Statement 
    FETCH NEXT FROM updatestats INTO @tableSchema, @tableName
 END

 CLOSE updatestats
 DEALLOCATE updatestats
 GO
 SET NOCOUNT OFF
 GO

How to solve Failed to sync the ArchiveGuid in Office 365 (Manual)

Last few weeks I’ve been struggling with an very difficult Office 365 / Exchange Online case, that got escalated to multiple Microsoft departments to be fixed. I already found one part of the solution, but Microsoft found the second part. Today I would like to take you through all the steps to fix possible causes and resolutions. So the initial problem started with the following error in the Office 365 admin portal with the affected users:

Failed to sync the ArchiveGuid 00000000-0000-0000-0000-000000000000 of mailbox MailboxGuid because one cloud archive CloudArchiveGuid exists.

Another symptom is the mailbox provisioning gets stuck, and hangs on “We are preparing a mailbox for this user”

You will only see this error with AD connect sync enabled environments. The problem occurs when the on-premise value mismatches with the Online Archive Guid. With just a few easy steps we can fix this issue.

Resolution

We will need to fill multiple Active Directory user attributes to resolve this issue.

Continue reading “How to solve Failed to sync the ArchiveGuid in Office 365 (Manual)”

Create new Outlook profile after Office 365 mail migration using GPO (Manual)

You want to move your mailboxes from Exchange on-premise to Office 365, and you want to give you users a smooth transition experience, then you will definitely need to implement the following to automatically create and configure a new Outlook profile on all Windows devices.

ZeroConfigExchange (ZCE)

Within Outlook Microsoft has created ZeroConfigExchange to setup new profiles with minimal user interaction. Depending on your exact configuration Outlook will be configured fully automatically, or the user is required to fill in his email address and/or password.

Continue reading “Create new Outlook profile after Office 365 mail migration using GPO (Manual)”

How to block non-modern authentication to Office 365 services. (Manual)

With Azure Conditional access you get more control over your data, get better security and visibility! To use this feature you will need to buy and assign Azure AD Premium or EM+S E3/E5 licenses to your users.

This manual can be used to enforce the use of the Outlook app on IOS and Android devices by blocking all apps that do not support Modern Authentication like iOS mail and Google mail client.

Step 1: In the Azure Portal go to Conditional Access. On the first page that you get create a New policy

Continue reading “How to block non-modern authentication to Office 365 services. (Manual)”

How to configure Outlook on IOS & Android using Intune (Manual)

If you deployed Intune to your mobile devices, you want to enforce the use of the Outlook app on the mobile device. We want to make the end user experience as smooth as possible and preconfigure Outlook for the. How can we prepare the Outlook app with your company email settings? With just a few steps, we can get this setup!

Step 1: From the Azure Portal go to Intune –> Clients Apps –> App configuration policies and click Add

Step 2: Give the configuration policy a name and description. Select Device Enrollment type, my preferred method is to use Managed apps, because this will deploy the policy to both enrolled and unenrolled devices. Select the Outlook apps on Associated app, and go to Configuration settings.

Continue reading “How to configure Outlook on IOS & Android using Intune (Manual)”

Create a drive mapping using Intune on Azure AD joined devices (Manual)

With the transition to Azure AD, you might want to connect your AAD joined devices to the traditional file server as explained in this article: Go Azure AD Joined with on-prem DC and fileserver The next step is to map some network drives with Intune!

Step 1: The first step is to create a PowerShell script that will do the actual drive mappings. This script will be placed on a Azure Blob storage (or your internal domain) where you will be able to manage and maintain the script. This script will be run using a second script that we will deploy with Intune. For your convenience I’ve already prepared the script:

Continue reading “Create a drive mapping using Intune on Azure AD joined devices (Manual)”