Get SQL Management Studio to work with an Authenticated Proxy

SQL Server Management Studio (SSMS) has the ability to use a corporate proxy for data connections to Azure.  Unfortunately it isn’t compatible with an authenticated proxy by default.  There is no way that I know to change this in the application.  However, you can do it by adding some configuration.

You do this by editing the Ssms.exe.config file.  This sits next to the Ssms.exe file, on my  machine this was C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio.

You’ll need to option the config file as admin.  Then look for the <> section.  You’ll want to add this configuration element:   <defaultProxy enabled=”true” useDefaultCredentials=”true”></defaultProxy>

This line doesn’t exist in the configuration file so it uses the default settings.  If it was included it would look like this:  <defaultProxy enabled=”true” useDefaultCredentials=”false”></defaultProxy>

So we’re just adding the command to enable the use of the user credentials for the proxy.  This does not hard code in a particular proxy, so it will still use the IE proxy settings for the workstation.


Recover Azure VM with AD

Azure VMs do not use the built in administrator account.  This means that the account you use can expire and be locked out.  For a standard VM there is a tool in Azure to reset an expired account, but it will not work for an AD joined machine.

If you set up an Azure VM as a domain controller, it also doesn’t use the built in account, and your domain admin can be locked out and expire.  If the password expires, you’re pretty much stuck.  You don’t have console access, so you can’t change the password, and the Azure tools can’t change the password either.

If you find yourself in this situation and need to recover, there is a way, but it’s a bit crazy.  It requires the deletion of VMs, so read through the entire thing and validate on your own.  This process did work for me.

First, create a new VM in the same resource group as the VM you need to recover.  The VM needs to support nested Hyper-V, so check what supports that.  I used a D2SV3 server.  Once the server is created, log in and add Hyper-V to it.

Now for the scary part.  Delete the VM you need to recover.  There are no prompts or questions, but this just deletes the VM and leaves the OS disk behind.  The important part is that it unbinds the OS disk so after the delete finishes, you can add it as a data disk to your new Hyper-V Host VM.

After you add the disk, you need to go into disk manager and mark the disk as offline.  Now you can create a nested VM and attach what it sees as a physical disk.

Then you can boot that VM, and since you have a console now, you can go through the password change process.  After that, shut down the nested VM, and remove the data disk.

After that completes, you can use the OS disk to create a new VM.  For some reason you have to start this process from the disk and not from the create VM screen.

Server 2016 ADFS – Limit Claims Providers

If your ADFS setup has more than one Claims Provider configured, the default behavior is for all the providers to be available for use by your Relying Parties.  If you’d like to limit some RPs to limit which CPs are available, you can use the following PowerShell:

Set-AdfsRelyingPartyTrust -TargetName RPName -ClaimsProviderName @("CPName")

To check the settings you can use the get version of the command, but the identifier is Name instead of TargetName:

Get-AdfsRelyingPartyTrust -Name 'RPName'


Server 2016 ADFS – Enable Test Page

ADFS has a local page that will allow you to use it as a launcher for Federated logins (https://FQDN/adfs/ls/idpinitiatedsignon.aspx).  It is also a great test page to use to confirm a login is working.

In Server 2016 this page is disabled by default.  To turn it on run the following PowerShell:

Set-AdfsProperties -EnableIdpInitiatedSignonPage $true

When you’re done testing, you can turn it back off:

Set-AdfsProperties -EnableIdpInitiatedSignonPage $false

S2D Cluster Stuck in Degraded State

We ran into a problem this month with our S2D Hyper-V cluster.  Since the nodes replicate their disks, after a restart it needs to get the restarted node back in sync with the cluster.  This happens automatically, and we keep an eye on it to make sure the node pool is synced before we bring the restarted node back into production.

FYI – you can do this with:



However, this month the jobs were created, but never started.  I let it sit overnight, but when they still hadn’t run by the next morning.  I started to look into how to fix this.  Luckily, I found a blog with the right things to check and fix:

He has a number of things to check, and what to do to fix them.  In our case the problem was that some of the drives stayed in maintenance mode.  From the Failover Cluster Manager they were not listed in maintenance, so without checking in PowerShell we probably never would have seen it.  To check the drives to see if any have an operational status of ‘In Maintenance Mode’ run


If you have some that aren’t ‘OK’, you can fix them by running

Get-PhysicalDisk | Where-Object { $_.OperationalStatus -eq "In Maintenance Mode" } | Disable-StorageMaintenanceMode

Once we did that the Storage jobs started and the drives got in sync.

Update Offline Virtual Machine

If you’ve created a golden image VM, you may have run across the hassle of keeping it updated.  You can fire it up monthly and run updates, or just wait and update as the first step in deploying a new machine.  OR…  You can deploy updates to the offline VM with a little prep work and powershell.

These notes are for deploying the monthly cumulative update on Windows Server 2016, but it should work for older versions and other updates.

The first step is to download the update file.  Then use the Expand program (built in) to extract the contents:


In the extract folder, find the .cab file for the update. Copy that to the VM host.  In this example, I’m using C:\Temp.

Now either open a PS Session or from the VM host open PowerShell.

Go to a folder on the local disk (this process failed when mounting into a CSV) and create a temporary folder to use as a mount point. It’s easiest if this is also where you’ve put the .cab file.

mkdir -Path C:\Temp\MountDir

Now mount the offline VHD(X) you want to update.

Mount-WindowsImage -ImagePath $VHDtoUpdate -Path C:\Temp\MountDir -Index 1

Now apply the image.

Add-WindowsPackage -Path C:\Temp\MountDir -PackagePath $updatepath

Wait while the process runs, then dismount the VHD(X).

Dismount-WindowsImage -Path C:\Temp\MountDir -Save

Now repeat for additional virtual machines, or clean up your temporary mount folder.

rd -Path C:\Temp\MountDir

Now you can start up your updated VM.

Full script:

$vmhost = <yourhost>
Enter-PSSession -ComputerName $vmhost

$cummupdatepath = "C:\Temp\"
$VHDtoUpdate = "C:\ClusterStorage\Volume1\Example\Virtual Hard Disks\Example.vhdx"

mkdir -Path C:\Temp\MountDir
Mount-WindowsImage -ImagePath $VHDtoUpdate -Path C:\Temp\MountDir -Index 1
Add-WindowsPackage -Path C:\Temp\MountDir -PackagePath $cummupdatepath
Dismount-WindowsImage -Path .\MountDir -Save
rd -Path C:\Temp\MountDir