Azure Virtual Machine (VM), Azure Bastion, iPad Pro, Surface Arc Mouse … ultimate portability!

Azure VM on iPad

I really wanted to JUST carry my iPad around at conferences. The problem was that I often needed a Windows O/S “machine” to complete task like code Python in VS Code or remote into my office computer.

So, what to do. The solution was simple I thought … use an Azure Virtual Machine and the Remote Desktop (RDP) app from Microsoft on my iPad. However, that was not an ideal solution as you really need a mouse to work well on Windows and the iPad does not support a mouse … or at least it USED to not support a mouse. Enter iPadOS 13! With iPadOS 13 you can now use a Bluetooth mouse (like the Surface Arc Mouse, the one I use!) with your iPad. Now I have a solution I can use.

Read this article from 9to5 Mac, How to use mouse with iPad, to learn how to setup your mouse with you iPad. Next, read the rest of this blog post to setup an Azure Virtual Machine (VM) and configure it for use with your iPad … or any other device that support RDP or just has a browser (we can use Azure Bastion to connect via a browser … but at a small cost[$] that we will discuss later).

This blog will show you how to setup and configure the following in Azure and on your iPad (or a PC, Mac or Android table) to compute anywhere using Azure:

Setup Azure Virtual Machine

  1. Navigate to the Azure Portal (https://portal.azure.com) and click the “Create a resource” button at the top of the page.
  2. In the Azure Marketplace click “Compute” then click “Virtual Machine” to begin the wizard to create a Virtual Machine.
  3. In the Virtual Machine Wizard select the desired subscription you desire to create the VM and select the Resource Group to create the VM inside. If you don’t have a resource group already, click “Create new” under Resource Group to create a new Resource Group. Enter desired name in pop-up for Resource Group and click “OK”.
  4. Under Instance details enter your desired VM name, pick your region, your O/S (in my case just want the latest Windows 10 Pro build – 1809) and your VM size (click “Change size” to select size) for your VM. For demo purposes I will pick a small VM size. You should pick the appropriate VM size for your needs. Note you can shutdown and stop the VM when you are not using it to save cost. So, the monthly cost can be 1/3 less for example if you are only using it 8 hours a day and stopping the VM when not in use! The good news is you can change the size after you create the VM if you need a bigger size later on – Resize Virtual Machines.
  5. Under Administrator account enter a username from the admin user and create a password. Under Inbound port rules select “Allow selected ports” and choose RDP (3389) to expose the RDP port to allow the iPad RDP client to connect.
  6. Click “Next : Disks >” and leave the defaults on the Disk page and click “Next : Networking >”
  7. Configure the Virtual Network by leaving the default name and clicking “Create new”.On the Create virtual network screen leave the default name and fill out the information like below (you can choose you own ranges but make sure to have one with mask 24 for RDP/HTTP and one with mask 27 for Azure Bastion) for the Address spaces and Subnets. Note we create two address spaces and subnets. One is the default with mask 24 for use with RDP and the other is with mask 27 used for Azure Bastion. The name of the Azure Bastion subnet must be AzureBastionSubnet. Click “OK” when done filling out the addresses.

  8. You can leave the Subnet setting to the default. Click “Create new” under Public IP and select “Static” under Assignment (This will allow the IP address to stay the same so you can easily connect to it each time for RDP). Leave the RDP (3389) setting and click “Next : Management >”.
  9. At the bottom of the Management section you may want to configure Auto Shutdown to assist in saving money by having the VM shutdown each night at a specific time. You can configure it to email you before the VM shutdowns as well. Make the desired setting here and click “Next : Advanced >”.
  10. You can take the defaults on the Advanced and Tags pages and go Review + Create page. Review all your settings and make sure they are correct. Make sure you have the green
    bar at top with “Validation passed” and click “Create” to create your VM.

  11. The next screen will show the status of you VM deployment. Watch this screen until you get “Your deployment is complete”. Then click “Go to resource”.
  12. On the VM resource page click “Connect”.
  13. On the RDP page you will find the Public IP of your VM and you can also click the “Download RDP File” to download an RDP file to connect from your PC. You will want to jot down the Public IP address (I have masked mine slightly for security in the blog post) for use on your iPad or other devices which support RDP.
  14. To use Azure Bastion which enables connectivity from a browser, click the Bastion at the top and then click “Use Bastion”. On the next screen you can leave the default settings as we already configured the AzureBastionSubnet in the VM setup and click “Create”. You will get a “Creating a new bastion <name>.” message. Wait until you get the “Provisioning State: Succeeded” message with the login and then login and test Bastion.NOTE: Azure Bastion at the time of this blog post is about $140/month plus network charges (first 5GB is free). I just use RDP personally, but I wanted to demonstrate how to setup Azure Bastion as it is a great service for organizations that want a more secure connection that utilizes just a browser and https. Azure Bastion pricing.

  15. When Azure Bastion connects you will see a pop-up to allow to send text and images to the clipboard when you connect. Click “Allow” to enable the clipboard for your VM (Cut/Copy/Paste).
  16. You are now connected to your new Azure VM! On the privacy screens you can take the defaults (or change if you like) and click “Accept”.
  17. After that it will prompt to see if you want your machine discoverable on the network. I picked “No” as I just have the one VM.
  18. And you are in you VM. Install the software you want to use – VS Code, Miniconda, Docker, … Whatever you like! You have a Windows 10 Pro PC in the cloud!

Configure RDP on iPad

  1. Install the Microsoft Remote Desktop (RDP) client on your iPad to connect to Azure VM from iPad. Launch the RDP Client, “RD Client”, to start the Microsoft Remote Desktop client.
  2. When launched click the “+” at the top right to start a new RDP connection to your Azure VM.
  3. Select “Desktop” on the “Add New” screen that pops up.
  4. In the “Desktop” screen select the “PC Name” section to enter the IP for your Azure VM. In the “Add Remote Desktop” screen the pops up enter the Public IP address for your Azure VM and click “Done”. (See Step 13 above in the Setup Azure Virtual Machine section).
  5. Click the “Additional Options” entry box and click “Friendly Name” in the “Additional Options” screen to enter the name of your VM. Enter a friendly name from your VM and click “Done”.

  6. Click “Desktop” on the next screen and then click “Save” to save the RDP connection in the RD Client.
  7. In the main window of the Remote Desktop client click the new connection you made to connect to your Azure VM.
  8. Enter the username and password for you Azure VM and optionally select the “Store username and password” option and then click “Done”.
  9. You should successfully be connected to your Azure VM if you entered everything correctly!!! If you want to use your mouse make sure you configured your iPad for the mouse using instructions from 9to5 Mac, How to use mouse with iPad.When you are ready to end your RDP session you click the button in the top middle with the three bars and then click “End Session”. To save money be sure to “Stop” your VM if not using it. If you configured the auto shutdown when you configured your VM it should automatically shutdown at the time you specified each day if it is up and running at that time.

Stop Azure VM when Not in Use to SAVE MONEY!


In the Azure Portal click the “Stop” button at the top to “Stop” the Azure VM when not in use.

When you are ready to use the Azure VM again, login to the Azure Portal and select the VM and click “Start”. Then you can reconnect.

I hope you enjoyed this blog and I hope you enjoy your Azure VM and iPad with a mouse as much as I enjoy using mine!

Loop Through Dates in T-SQL … One Day at a Time!

 

T-SQL Code to Loop One Day at a Time

Jamey Johnston (@STATCowboy)

 

Hidden in my SQL Server 2016 Security Demo blog post is a neat T-SQL trick to loop through a date range day by day (check out the “2 – Oil&Gas RLS Demo – LoadTables.sql” script when you download the code)! But to give you a simpler example the gist of the code in the script is this:

 

DECLARE @StartDate DATE = ‘2016-10-01’;
DECLARE @EndDate DATE = ‘2016-10-31’;

WHILE (@StartDate <= @EndDate)

BEGIN

print @StartDate;

— Do Something like call a proc with the variable @StartDate

set @StartDate = DATEADD(day, 1, @StartDate);

END;

Download code!

As you can see this is simple code and works like a charm! Hope you enjoy and hit me up on Twitter (@STATCowboy) if you have questions or improvements to the T-SQL Code!

Setup Azure SQL Database using the New Azure Portal

April 7, 2015

Jamey Johnston

Azure SQL Database is a relational database-as-a-service that allows for scaling up to thousands databases. It is a self-managed service that allows for near-zero maintenance, in other words, Microsoft handles all the backups, patching and redundancy. Azure SQL Database offers service tiers that allows for dialing up or down the horsepower as needed which also means the pricing can scale up or down. Pricing of the Service Tiers range from ~ $5/month to the slowest and smallest in size to ~$3,270/month for the fastest and largest.

More Info on Azure SQL Database

In this post we will walk through the steps to create an Azure SQL Database using the new Azure Portal. Please note the new Portal is still in beta so the steps and screenshots may change as Microsoft still rolls out the new portal.

This post is based on the documentation on Azure, Get started with SQL Database by jeffgoll, which shows how to setup an Azure SQL Database using the older Azure Portal.

 

Step 1: Create an Account on Azure

 

This step can be skipped if you already have an account on Azure. If not, go to http://azure.microsoft.com and click on the “Free Trial” in the upper right corner to get started.

 

Step 2: Logon to the New Azure Portal and Provision a Virtual Server and SQL Database

 

  1. Access the new Azure portal at http://portal.azure.com and login with your Azure Account.
  2. Click “New” at the bottom left of the page, then “Data + Storage”, then “SQL Database” to start the SQL Database wizard.

  3. In the “SQL Database” panel enter your desired Azure SQL Database name in the “Name” field (e.g. myfirstazuresqldb) then click “Server / Configure required settings“. In the Server Panel pop-out to the side, click “Create a new server” (I am assuming if you are reading this tutorial you don’t have an existing Azure SQL Server setup!).

     

     

     

  4. In the “New Server” panel pop-out enter an Azure SQL Server name in the “SERVER NAME” field (e.g. myfirstazuresqlserver), a Server Admin Login account name in the “SERVER ADMIN LOGIN” field (e.g. SQLAdmin), a password in the “PASSWORD” field and enter the password again in the “CONFIRM PASSWORD” field. Choose the location where you want the server to reside by clicking in the “LOCATION” area and choosing the Location. Leave the “Yes” chosen in the “CREATE V12 SERVER (LATEST UPDATE) so we can learn some new features in a later blog post. Leave “ALLOW AZURE SERVICES TO ACCESS SERVER” checked. Click “OK” at the bottom of the “New Server” panel to continue.

     

     

  5. Back on the “SQL Database” panel leave “Blank Database” selected in “SELECT SOURCE” option and click on the “PRICING TIER” option and choose “B Basic” in the Pricing Tier panel pop-up and click “Select” at the bottom.

     

     

     

  6. On the “SQL Database” panel you can set the desired Collation but we will leave it the default for now. Click on “Resource Group”, then in the “Resource Group” panel choose “Create a new resource group” and then in the “Create resource group” panel type a resource group name in the “Name” field (e.g. myfirstresourcegroup”).

     

     

  7. Choose the “Subscription” (e.g. Visual Studio Ultimate with MSDN) to pay for the Azure service. Check “Add to Startboard” to have the SQL Database show up on the Portal front page. Then click “Create” to begin provisioning your new Azure SQL Database! 🙂

     

     

  8. The page will go back to the Portal front page and a tile will appear with the title “Creating SQL Database”. Also, in the side bar to the left you will see a notification.

     

     

     

  9. Once the database is provisioned the pages will refresh and show the Azure SQL Database Dashboard page.

     

     

  10. Your database is provisioned!

     

     

Step 3: Add Firewall Rule to Access Azure SQL Database

 

  1. Click on the Azure SQL Server under the “Summary” section.

     

     

  2. The “SQL Server” panel will pop-out to the right, click “Settings” to open up the SQL Server configuration panel to the right.

     

     

  3. In the “Settings” panel click on “Firewall”. In the “Firewall Settings” panel that pops-out enter a “RULE NAME” (e.g. Home), the START IP and END IP which will probably be the same IP address if testing from home. You can use a website like whatismyip.com to get your external IP address. Click “Save” at the top once finished entering the values. (NOTE: The values below are not valid and you should enter the correct ones for your location).

    Your IP may change so check periodically to make sure you have the correct IP addresses in the firewall rules. Also, if trying to connect from work your work firewall may block access to the default port of 1433 which is used for access to the Azure SQL Database so if you are having issues trying to connect from work they may be your issue! J

     

     

  4. A message should appear indicating the firewall rules were successfully updated. Click “Ok”.

     

     

  5. Firewall is configured.

 

Step 4: Setup SQL Server Management Studio to Manage and Access Azure SQL Database

 

  1. Finally to test your new Azure SQL Database download the SQL Server 2014 Management Studio Express and install using the defaults. Choose the 32-bit or 64-bit version depending on your O/S version (probably 64-bit for most).

     

     

     

     

  2. Launch SQL Server 2014 Management Studio from your PC and login to the server you created in Step 2 using the server name, admin account and password and click “Connect”.

     

     

     

     

  3. Congratulations you have created your first Azure SQL Database!