Configuring the Azure VM for SQL Server connectivity

In the last SQL Bangalore UG meeting, I had talked about how to use the Custom Scripting component in Azure to run the post configuration operations on an Azure VM which was hosting a SQL Server instance. The post configuration options that I am going to talk about in this post are necessary for you to be able to connect to your SQL Server instance on an Azure VM from a Management Studio running on your on-premise machine.

Before you can connect to the instance of SQL Server from the Internet, the following tasks must be completed:

  • Configure SQL Server to listen on the TCP protocol and restart the Database Engine.
  • Open TCP ports in the Windows firewall.
  • Configure SQL Server for mixed mode authentication.
  • Create a SQL Server authentication login.
  • Create a TCP endpoint for the virtual machine. This would normally be done while providing the endpoint configuration if you are using the Azure Management Portal wizard.

If you had used an Image from the Image gallery, then you will get a default database engine installed with the TCP/IP port configured as 1433. I had written a post earlier which walks through an Azure VM creation using a SQL Server image from the image gallery.

Here I am going to talk about how to automate the bulleted points mentioned above using PowerShell and the Custom Script extension that the Azure provides. This is going to be a long read… So I suggest you get a coffee before you start reading further!

Continue reading

Remapping the temporary drive on an Azure VM

There might be explicit requirements from an application standpoint which requires the D: drive to be available. While re-mapping your application to use another drive may be a very simplistic suggestion, it might not be viable in a certain scenarios. If you have used an Azure Virtual Machine, it is made clear in various articles that the temporary drive i.e. the D: drive should not be used. In this blog post, I shall show you how to re-claim that the letter D from your Azure Virtual Machine and assign that to another drive that might be craving for this particular letter of the English alphabet.

Azure VM Disk Management The first thing that you will need to do is assign a new data disk or an existing data disk to your Azure VM if you do not have a spare disk. This can be accomplished easily from following the steps mentioned here.

Once you have initialized the disks, your disk management view should be something similar to what you see in Screenshot 1. The temporary disk shows that it hosts the Page File. Remapping this first requires you to move the page file to a new disk or some other data disk that was already present on the server. You will have to reboot the machine for the  changes to take effect.

Once the machine is back up, change the drive letter mapping from Disk Management.

Change the page file settings to use the temporary storage but this time the drive letter would be a different one. Once you reconfigure the page file settings, you will need to reboot the virtual machine again.

When the virtual machine is finally online again, you will have your desired drive letter mapping. As you can see in Screenshot 2, the page file and my temporary storage is now the Z: drive where as the D: drive is assigned to a data drive.

Azure VM Page File Changed

Say CHEESE Azure Win 8.1 VM

In my last few posts on Azure, I had talked about creating a virtual machine on the Microsoft Azure platform. I configured a Virtual Machine with Windows Enterprise 8.1. Then I configured the VM to use my Microsoft Account. Once my configurations were done, I decided to use this as a base image for my future trysts with Azure. This post is about how I created the image for the Windows 8.1 Enterprise machine that I had setup earlier.

An image is a .vhd file that you can use as a template to create a new virtual machine. An image is a template because it doesn’t have specific settings like a configured virtual machine, such as the computer name and user account settings.

You have two options before you create the image of the Windows 8.1 Enterprise VM.

1. Sysprep the machine using the steps mentioned here

2. Follow the steps mentioned below without performing the sysprep

The “Capture the virtual machine” dialog is available under Virtual Machines –> Instances tab in the Azure Management portal. What you will get is a straightforward looking window with three options to be filled in.


Check the box “I have run Sysprep on the virtual machine” only if you have done Step #1 mentioned above. If not, leave it unchecked. Since I had already run a sysprep on the machine, I will use this option.

Clicking on OK registers the image. If the image is created successfully, the virtual machine will be deleted. The image is now ready to be used as a template to create a virtual machine running Windows 8.1. To do this, you’ll create a custom virtual machine by using the From Gallery method and select the image you just created.

In the “Choose an Image” page, you will chose “My Images”. Select the Image which was registered (see Screenshot 2). The image name will be same as the image name provided when you created the image (see Screenshot 1).


The rest of the configuration steps are the same as explained in my previous blog post.

Keep in mind that when you create a VM using the wizard with a pre-configured custom image, the VHD gets created with a random alphanumeric name. In my case, I had a VHD named “btquhzrp.xkg201406071118490885”!


Manage Disks and Images

Capturing an Image

How to log into an Azure VM using a Microsoft Account

I recently deployed a Windows 8.1 VM in Microsoft Azure. I now needed to add my Microsoft account as an Administrator to my VM. This seemed like a simple enough task, right! I added the user to the list of users on the VM and then made the user an admin. When I attempt to log into my VM using my (Microsoft) account, I get a logon failed.


You actually need to make a few changes in the VM to allow remote connections. Go to Settings –> PC Info –> Remote Settings on the VM and uncheckAllow connections only from computers running Remote Desktop with Network Level Authentication (recommended)“. See Screenshot 1.


imageAfter that connect to the VM from Microsoft Azure Portal again, download and save the RDP file. Edit the RDP file and replace “prompt for credentials:i:1” with “enablecredsspsupport:i:0“. After the change, the RDP file should look like what you see in screenshot 3. Save the RDP file and use that to login into the VM. You will be able to log into the VM and now you can choose the account that you had added above (see Screenshot 4). image

Once you have logged in, you can checkAllow connections only from computers running Remote Desktop with Network Level Authentication (recommended)” option which you had disabled earlier. Once you do that, you will need to do the exact opposite action. Edit the RDP file and replace “enablecredsspsupport:i:0” with “prompt for credentials:i:1“.

Once you do the above, you will be able to log into your VM with MicrosoftAccount\email address as user name in the remote desktop dialog.


Creating a Client VM on Azure for testing

I had recently blogged about setting up a SQL Server environment on Microsoft Azure for testing purposes. With the availability of Windows 7 and Windows 8.1 images, developers and testers could take advantage of deploying the applications to these Azure VMs for testing without having to create or setup new machines in their environments. Deploying Windows 7 and Windows 8.1 Enterprise clients to Microsoft Azure is now available for MSDN subscribers.

When you attempt to create a new Virtual Machine, the Wizard will offer you a choice of picking a virtual machine from a Gallery . imageThe gallery has a pre-created set of images which you can use to create your virtual machine and can save you time from additional post-setup configurations. I am looking to create a Windows 8.1 machine which comes in two flavors:

a. Windows 8.1 Enterprise (x64)

b. Windows 8.1 Enterprise N (x64)

I chose Windows 8.1 Enterprise x64.

In the virtual machine configuration page, I provided the details for the VM like the machine name, user account  as seen in the Screenshot 1. I picked an A1 size configuration (Basic) for the client. This can obviously be extended at will at a later time.

On the next configuration page, I used an existing cloud service that I had and picked the pre-created storage account for hosting the VHDs. Creating a storage account is not mandatory as the wizard for creating a VM will create a randomly named storage account for you. However, I do not like have entities named with weird alphanumeric sequences which is why I chose to pre-create the storage account.

In the next configuration screen, I picked the VM Agent (enabled by default) and the Microsoft Antimalware which is in Preview (see Screenshot 2). image

Now you have a virtual machine created in a few minutes which is ready for testing.

If you need to install additional software for testing, then install it on the virtual machine by establishing a remote desktop connection to the Azure VM. Once your post-deployment steps are complete, capture an image of the virtual machine. This can be done using the Capture option available in the Azure Management portal by highlighting the VM instance that you want to capture the image of! Once the image is captured, it will be available under the Images tab under Virtual Machines in the Azure Management portal. This however has a caveat! The image is created in the same container where your VM VHD is stored. Subsequent captures of the VM are stored in the same Storage Account. If you do not want this behavior, then you will need to use the Create Image wizard available in the Images tab. This is something that I will show in another blog post.



Deploying Windows 7 and Windows 8.1 Enterprise Clients to Microsoft Azure Available for MSDN Subscribers

Azure VM Image