Have you had instances when your DBA left you with a DB that no one has SA access rights to?
For any instance of Microsoft SQL install, including SQL Express, there is a mode which would enable a local admin on that SQL server machine to gain/assign access to DB.
There are few steps which you have to follow:
1. Stop all SQL services
2. Start a command prompt with Admin permissions
3. Navigate to:
C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Binn
sqlservr.exe -m “Microsoft SQL Server Management Studio” -s SQLEXPRESS or any SQL instance name
5. Start SQL Server Management Studio as local admin and assign relevant groups/users access to DB
With a recent client, we had to re-register the StorSimple physical appliance to a new instance of StorSimple device manager instance, the old instance was deleted and no longer available on Microsoft Azure (ASM).
If you attempt to run Invoke-HcsSetupWizard on the appliance, it would error with the appliance is already registered to another instance and the only way to register it to a new instance is to factory reset the device.
While resetting, the device hang up on phase 3 and wouldn’t go past that stage. After speaking to Microsoft Support, they have advised to shutdown both controllers and re-attempt to run the factory reset again which was successful in this case.
Just keep in mind that you will be losing all your device configuration and any data that you have accumulated on the device after the reset, ensure you update device after the reset.
While working with DevTest Labs on Microsoft Azure, it’s a good idea to make sure you have Auto-start and Auto-shutdown policies to keep cost down on resource utilisation while not being used.
The mistake or misconception I had form customers and I see this continuously that they forget to opt the VM in or out of the policy when scheduling these machines to shutdown or start at certain times.
This needs to be enforced on a per VM basis, see below.
I have been working with Microsoft lately on an issue that I was experiencing with an Azure application gateway (appGW) deployment that require both internal and external interfaces handling traffic over HTTPS.
If you try to attach the same AppGW front end port to internal and external front end configuration, this would cause the appGW to misbehave. In my scenario I had a rule attached to external interface for handling incoming traffic but no rule attached on the internal interface and as a consequence the internal interface started processing traffic while the external interface was rejecting all connections (not even 502 error! would you believe!). Just to note that all my appGW deployments are scripted using PowerShell/JSON.
Microsoft managed to replicate this internally and issued a bug report, they are working on it as we speak but without ETA currently.
I had to drop my second listener (internal) in order to bring the appGW back to it’s expected behaviour!
In part 1 of this article I have gone through creating Azure Applications Gateways (AGW) using Powershell which is a powerful way of deploying resources on Azure, using recursive functions and methods you could build a complex solution in few lines. Unlike Powershell, JSON is a static solution. It gives you a way of creating a baseline deployment, I still haven’t found a way of controlling sub-resources (which isn’t governed by the JSON Copy() function).
In this article I will go over the same deployment using a JSON template, this only serve as a reference point for your deployment. I will use the same Azure AGW configuration I used in part 1 to keep both deployments consistent.
When building Azure AGW, same prerequisites apply to the JSON method. There is a subtle difference which I will go over when I get to that point.
So this time I have prepared a Visio diagram to simplify our deployment and understand what we are trying to achieve – see below.
The diagram shows a Web server hosting four websites running a combination of HTTP HTTPS with authentication (using Basic authentication) or Anonymous access.
The only thing different in comparison to previous Powershell deployment is the steps we take to process SSL certificates and passing them over to the JSON template.
Azure Application Gateways is a layer 7 reverse proxy service offered as a PaaS to general public. It supports SSL offloading, which means you can terminate your SSL connection at the Application Gateway and connect to the backend server using HTTP traffic or initiate a new SSL connection to your backend service.
This is all well and good, simple and painless if you have a single backend server with a single website. The complexity of the solution increases as the backend start leveraging more of the IIS functionalities such as Windows/NTLM authentication, SNI and host headers or various SSL certificates used for each sub-site (if you have multiple sites running on the same IIS server).
Before even starting to look at designing your Azure Application Gateway, there are few guidelines you will need to follow:
- You should have an empty default site.
- If using both HTTP/HTTPS protocols on any of the sub-sites, the default website should be listening on both 80 and 443.
- In the case of HTTPS the default site will need to be loaded with a single SSL certificate that will primarily be used by the Application Gateway to authenticate against the server.
- Not running SNI on default website.
- If you are running NTLM or Windows authentication on any of the sites (except form based authentication) then you will need a site/page that allow anonymous authentication to be used for Application Gateway custom probe.
- Use IP address for the backend pool rather than FQDN.
The above will save you a lot of hassle while implementing and configuring your Application Gateway to work with your backend web server.
Microsoft have fixed few issues we were experiencing recently with Application Gateways around SSL and custom probes.
There are two ways available to deploy an Application Gateway, Powershell or JSON template. The latter is preferable to ensure consistency at each deployment. This article is in two parts, in this article I will be using Powershell to deploy an Application Gateway.
- SSL private key in PFX format for all sites using SSL
- SSL public key in CER format for default site
- IP address of the backend web server
- Front and backend listening port
- Site/page with anonymous access if requiring authentication
Powershell code below would deploy an Application Gateway listening on two ports (80,443). The backend consists of four sites with SNI and host headers enabled, two sites run under port 80, one of them require basic authentication. Another two sites run under port 443 bound with self-signed SSL cert for testing, one of the sites has basic authentication turned on. This would test the four common scenarios of a typical deployment.
To be continued …..
Automation has become a large part of any Ops team work stream. It reduces repetitive work and introduces a clear and effective method of ensuring consistency across your platforms, server estate and businesses.
In this article I will go through Azure Automation, especially automation of VM creation and joining the domain. This would lift some of the load of IT to provision and join those VM’s to the domain manually and allowing developers to take a much agile approach.
The automation process is based on Azure Runbook, utilising Powershell workflow and Runbook assets in fully automating the whole process.
You also should have a startup image (i.e. golden image) sysprep’ed and uploaded to a known and accessible storage account in that subscription. You need to ensure that the image was generalised and shutdown. This type of deployment will ensure all your applications and settings are packaged as part of that deployment – that’s if you don’t want to use Microsoft own images. You need to make sure you are covered by MSDN licenses if you are uploading client images and ensure your server image is appropriately licensed as well, plus any application you package as part of that image.
You are probably keen to get down to business …. few things you have to make sure of to ensure this whole process work for you. This automation task is based on creating an Azure ARM VM, you will need three credential assets created under your automation account which will be used with your script:
- Asset for local VM admin
- Asset with Azure co-admin rights – or at least VM creation rights
- Asset with domain admin rights
The vhd image of your packaged and generlised VM needs to be uploaded to a storage account. Make a note of that storage account, any VM created of that image will need to reside on same storage account. If you need to create a VM on a different storage account then you will need to copy that image across first.
The script is split to two parts, the main body which is the Runbook – Powershell workflow
On Azure, create a Runbook called AzureVMDomainJoin and paste the code below:
Note: Change $vmImageUri string in the above code to reflect your environment.
In order to run this Runbook, ensure it’s published on Azure. Then use the script below to activate the Runbook from your client machine – need to have AzureRm Powershell module installed to be able to run it.
Make sure you fill out all variables according to your environment. This script will only utilise what’s already created on Azure, like storage accounts, networks and subnets. If you want a new resource group created or a new network/subnet then pre-stage it on Azure first before you run this script.
Happy scripting ….. 🙂