The Problem

My team I am working with were recently busy setting up a new VM hosting SQL Server on Azure for our client to do their QlikView reporting on.

As an Entelect employee, we have the privilege (and as you will soon see, the curse) of having an Azure Account through our organisational account (Office 365), linked to our e-mail address.

Our current client uses Azure to host the solution we are building for them. The client is the owner of this Azure subscription, with me and my team having administrative user accounts. This means that we can add or remove Azure subscriptions, manage the Azure services and gain access to all the VM’s associated to the account. There are however a few things we cannot do, such as viewing the Azure costs, client banking details, adding additional users, etc.

Because I already had an organisational account linked to Azure through my e-mail, my access to the client’s Azure services are through my own e-mail but linked to a personal (or Microsoft Account).

My plan of action for setting up the new environment seemed straight forward: Use Powershell to authenticate with the Azure service, pull the latest production database backup (from the Azure Blob Storage) to the new VM, restore the database on the VM (and scrub the data) and delete the backup on the VM. If you are interested in seeing the entire process of how we achieved this, keep a lookout for my follow-up article.

Our good friends at Microsoft have given as a command to login to Azure services using the “Azure” Powershell module: -Add-AzureAccout

However, as Microsoft so aptly points out in their documentation “This non-interactive log in method only works with a work or school account. A work or school account is a user that is managed by your work or school, and defined in the Azure Active Directory instance for your work or school.” You can view the documentation on this link.

Their workaround is to create a new organisational account (such as Office 365) and add a new user to the Azure portal with these credentials. Although our final version did use this approach, you may not want to hold off setting up your environment and writing scripts until you manage to procure a new Microsoft Organisational license, or you may be unable (for whatever reason) to gain access to an additional organisational license. This is where my workaround comes into play

The Workaround

My hope is that this workaround saves someone from the frustration of spending hours finding a solution and that it could save some time setting up a new VM on Azure with automated Azure Authentication.

The first step is to install the AzureRM and Azure Powershell commands module onto YOUR local machine. Due to firewall and security concerns you may not be able to do the following on an Azure SQL Server VM.

Install AzureRM and Azure Module

You can install both modules using this MSI Installer on Git or through PowerShell itself by executing the following commands.

1. Install-Module AzureRM
2. Install-Module Azure

After the installation you can verify that the installation is successful by running the following command and seeing if a list of Modules are returned without any errors:

• Get-Module -ListAvailable Azure*

Download Azure Publish Settings File

The following should also be done on your personal machine rather than on the VM.

1. Open up your browser, browse to the Azure portal and login to your azure account by selecting “Personal Account” instead of “School or Work Account”.
2. Now, open up Powershell with Administrative privileges and run the following command
   1. Get-AzurePublishSettingsFile
3. If you have already logged-in to Azure a “*.publishsettings” file will start to download. Else, just log in as you would normally do and make sure to login with a Personal (non-organisational) account.
4. Save the file to a location from where you can easily access it

Setup automated VM Azure authentication

Remote connect to the VM to which you wish to grant Azure authentication and then follow the following steps:

1. Copy the “.publishsettings” file to a location on the VM
2. Get the full path of the file and copy it to the clipboard
3. Open up Azure on the VM with Administrative rights
4. Run the following command with the file path included

   • Import-AzurePublishSettingsFile -PublishSettingsFile “<path to the file>”

You should now see a response with the Azure Service Id, Name, Environment, Account, State and other basic properties.

You can now proceed to access the Azure services through Powershell.

Please note that it is not recommended to use this authentication method as a full-time solution as is. You may need to consider some encryption or file locking to keep the login data secure. This post is only intended to provide information on the workaround and not to be a full on production secure solution. Keep an eye out for my follow-up article on how we implemented our final Azure authentication service and proceeded to do the whole database restore and data-scrubbing.