Unable to update NuGet or Packages in Powershell due to “WARNING: Unable to download the list of available providers. Check your internet connection.”

When attempting to install or update PowerShell Modules, NuGet or NuGet packages in PowerShell 5. You receive one or more of the following errors

WARNING: Unable to resolve package source 'https://www.powershellgallery.com/api/v2/'.

The underlying connection was closed: An unexpected error occurred on a receive.

WARNING: Unable to download the list of available providers. Check your internet connection.

Equally, you may receive the same error when attempting to run a WGET or an Invoke-WebRequest command e.g.

wget https://www.google.com/

You are unable to install/update the software component or make an outbound internet connection.

This issue may be especially prevalent on IIS installations serving HTTPS websites.

The Fix

Conventional troubleshooting is fairly well documented on-line

  1. Ensure that you are actually able to open a https webpage in a web browser
  2. Ensure that your DNS is working correctly.
  3. Check to see whether wget can connect to a non-https site e.g.
    wget http://www.google.com/
  4. Check to see whether or not you need to use a Proxy server. If so, you must configure PowerShell to use your Proxy Server before you proceed. This may require you to to configure PowerShell with your Proxy Server credentials.
    $webclient=New-Object System.Net.WebClient
    $webclient.Proxy.Credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials

A less obvious issue to explore related to the default operating system security configuration for using SSL.

More Info

By default, Windows Server and Windows client will allow SSL3, TLS 1.0, TLS 1.1 and TLS 1.2. The .net Framework is also configured to allow these protocols, and, by default, any outbound request for a SSL site will attempt to use SSL3/TLS 1.0 as its default protocol.

In secure environments, where system administrators have enabled recommended best practice on Windows systems to disable the use of SSL1, 2,3 and TLS 1.0. PowerShell is not currently clever enough to internally compare its configuration to that of the operating system. consequently, when attempting to make an outbound https request in such an environment. PowerShell will attempt to use one of the older protocols which has been disabled by the operating system’s networking stack. Instead of re-attempting the request using a higher protocol. PowerShell will fail the request with one of the error messages listed at the beginning of the article.

As NuGet and Update-Module both attempt to make connections to Microsoft servers using HTTPS, they too will fail.

Encountering this issue on a SSL enabled IIS install will be more common, as it is more likely that system administrators will have applied best practice and disabled legacy encryption protocols on these servers. their public facing, high visibility should demand such a response.

To fix the issue there are two options:

  1. Reconfigure and reboot the system to re-enable client use of TLS 1.0 (and possibly SSL3) via
    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\<protocol>\Client

    DisabledByDefault = 0
    Enabled = ffffffff (hex)

  2. Alternatively, you must set-up each PowerShell environment so that the script itself knows not to use the legacy protocol versions. This is achieved via the following code which restricted PowerShell to only using TLS 1.1 and TLS 1.2.
    [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.SecurityProtocolType]'Tls11,Tls12'

Create a Slipstreamed Hyper-V Server 2019 installation image with working Remote Desktop for Administration

If you have been following the saga of the non-working Hyper-V Server 2019 release from November. You may be aware that the most prominent issue – that of Remote Desktop Services for Administration not working – has now been resolved in the February 2019 patch release cycle.

This article outlines how to create updated media for Hyper-V Server 2019 using the original installation medium and patch it into a working state.

Note from the author

Please note that if you intend to use Hyper-V Server in a production environment, you should wait for Microsoft to re-issue the office ISO. Once it is released, it will be made available in the Microsoft Server Evaluation Centre.

View: Microsoft Evaluation Centre

Pre-requisites

You will need access to a Windows 10, Windows Server 2016 or Windows Server 2019 system in order to update the installer.

Obtain and install the Windows ADK 1809 (or later) selecting the Deployment Tools option (providing you with an updated version of DISM)
Download: Windows Assessment & Deployment Kit (ADK)

Retrieve the original Hyper-V Server 2019 ISO
Download: Hyper-V Server 2019 (1809)

Download following updates from the Microsoft Update Catalogue
Note: This is correct as of early March 2019. It is suggested that you apply newer cumulative and servicing updates as they are released in the future.

  1. KB4470788
  2. KB4482887
  3. KB4483452

View: Microsoft Update Catalogue

[Optional] If you wish to apply any language regionalisation (e.g. EN-GB), source the CAB file(s) for the language features that you require. For example:
Microsoft-Windows-Server-LanguagePack-Package~31bf3856ad364e35~amd64~en-GB~10.0.17763.1.cab

Updating the Installation Image

To update the installation image:

  1. Create a folder on C:\ called ‘Mount’
  2. Add a second folder on C:\ called ‘hvs’
  3. In the hvs folder, create a subfolder called ‘Updates’
  4. Extract the entire contents of the ISO from the Hyper-V Server 2019 ISO into C:\hvs
  5. Place the three MSU files from the Microsoft Update Catalogue into the C:\hvs\Updates folder
  6. [Optionally] Place the CAB file for the language pack into the C:\hvs folder and for convenience rename it ‘lp.cab’
  7. Open an elevated Command Prompt
  8. Issue:
    cd /d "C:\Program Files (x86)\Windows Kits\10\Assessment and Deployment Kit\Deployment Tools\amd64"
    To navigate into the working folder for the updated version of DISM.exe
  9. Issue:
    dism.exe /mount-image /ImageFile:"C:\hvs\Sources\install.wim" /Index:1 /MountDir:"C:\Mount"
    To unpack the installation image into the C:\Mount folder
    Note: Do not navigate into this folder with CMD, PowerShell or Windows Explorer. If you leave a handle open against this folder when you try to re-pack the install.wim, it will fail.
  10. Once the mounting is complete, patch the installation by issuing:
    dism.exe /Image:"C:\Mount" /Add-Package /PackagePath:"C:\hvs\Updates"
  11. [Optional] Apply the language pack by issuing (change en-GB to your language as applicable):
    dism.exe /Image:"C:\Mount" /ScratchDir:"C:\Windows\Temp" /Add-Package /PackagePath:"C:\hvs\lp.cab"
    dism.exe /Image:"C:\Mount" /Set-SKUIntlDefaults:en-GB
    If you intend to use ImageX, DISM or WDS to deploy this image, you can skip the following command. If you intend to create a new bootable ISO or UFD, issue:
    dism.exe /image:"C:\Mount" /gen-langini /distribution:"C:\hvs"
    This will create a new Lang.ini file which must be included in the ISO/UFD media (but is not required for other deployment methods)
  12. Dismount and re-package the install.wim file by issuing:
    dism.exe /unmount-image /MountDir:"C:\Mount" /Commit
  13. Once DISM has processed the installation image, the new Install.wim file can be found at:
    C:\hvs\Sources\install.wim
  14. At this point you will have a working installation image which you can use to create a new ISO, UFD or install via WDS. You should delete the Updates folder and [optional] lp.cab from C:\hvs before creating a new ISO or bootable UFD.

If it goes wrong at any point, issue the following command to abort the process and go back and try again:
dism.exe /unmount-image /MountDir:"C:\Mount" /Discard

Delete the C:\Mount and C:\hvs folders once you have finished creating you new deployment media.

Final Word

If you follow the above, you will have not only a fixed RDP experience, but also a current patched version of Hyper-V Server. Eliminating a little time spent waiting for Windows Update to run.

If you are going to enable RDP for Administration. As ever, do not forget to enable the firewall rule in PowerShell. SConfig.cmd does not do this for you!

Enable-NetFirewallRule -DisplayGroup "Remote Desktop"

Where is Hyper-V Server 2019? Remote Desktop for Administration Bug & ISO Download Update

*Update* 6th March 2019. For a workaround to create your own fixed Hyper-V Server 2019 installation media, see Create a Slipstreamed Hyper-V Server 2019 installation image with working Remote Desktop for Administration

The release of the Windows Server 2019 family has not gone well for Microsoft. Hitting RTM in September 2018 (1809), the release became available through the different distribution channels (VL, MPN, Visual Studio) from late October 2018. It was promptly pulled from the public domain and re-packaged.

Background

With Windows Server 2019, Microsoft chose to skip the RTM phase and sent the code directly to General Availability (GA). The mainstream Windows Server 1809 branch was blighted – a not necessarily fairly – by a widely publicised data-loss bug. Under some circumstances, Known Folder Redirects under upgrade installs of Windows 10 1809 would erase the  contents of the target. As a precautionary measure, Microsoft withdrew Windows Server 2019 due to it sharing the same code base as Windows 10. Reissuing it, and Windows 10 with fixes on 13th November 2018.

Despite this, there have been a spate of other reported issues in the Windows Server 2019 release. Examples include

Microsoft were vocally chided by the user community for taking until 17th January 2019 to restore the evaluation ISO for the regular release of Windows Server 2019. As well as for failing to comment on the situation with Hyper-V Server 2019. This irked many IT professionals as they lost the Christmas testing period. Interfering with opportunities for initial product evaluation ahead of the New Year. The delay will cause a deferral of adoption in many organisations; being especially true for Exchange Server 2019, which requires Windows Server 2019 as a pre-requisite.

Unlike the Windows Server 2019 evaluation ISO, the Hyper-V Server 2019 did not return and to date (2nd March 2019) it remains unavailable.

 

Hyper-V Server 2019 1809

As with the full version of Windows Server 2019. Hyper-V Server 2019 was only released for a matter of days before its withdrawal.

ISO: 17763.1.180914-1434.rs5_release_SERVERHYPERCORE_OEM_x64FRE_en-us.iso
SHA1: 04B40E7E2FD14C964086C214D6409795908F6887

The availability of the ISO was fleeting for several key reasons.

  1. Hyper-V Server 2019’s shared codebase means that it is impacted by the redirected folders bug. However as a rule, you should not be upgrade installing Hyper-V Server. See my article on upgrade installing Hyper-V Server for reasons why this is not advised.
  2. Remote Desktop for Administration does not work at all in the release [1] [2]. This has been resolved via KB4482887 from 2019-02 Patch Tuesday.
    NB: Don’t forget to enable the firewall rule:
    Enable-NetFirewallRule -DisplayGroup "Remote Desktop"
  3. sconfig.cmd no-longer loads automatically after joining a domain (this is true on 2019 Server Core also).
  4. When installed in Hyper-V, the Virtual Machine Connection display fails to draw correctly, leading to frequent display corruption.

 

Windows Server 2019 has had a rough start. Many commentators are expressing concerns that Microsoft does not yet possess the experience to successfully navigate the SaaS landscape with Windows. Observationally, QA has been in decline at Microsoft for some years, but up until now we have not seen such deficits impact the mainstream Server ecosystem.

With Microsoft posting a resolution to the Remote Desktop for Administration issue via KB4482887. It is logical that Microsoft will seek to re-release Hyper-V Server 2019 in coming days and weeks. Microsoft for their part have made no comment on the issues with Hyper-V Server 2019, or any planning for its re-release. So this remains speculation, but the community may see it under a 1903 re-packaging hopefully as early as next week.

In the interim, you should continue to avoid using the original release ISO in your test labs.

View: Microsoft Evaluation Centre

SQL Server combining LIKE with IN (SELECT LIKE IN query)

System Requirements

  • SQL Server

 

The Problem

This was one of those “it should be obvious” moments.

Dating back to 2004 in its current iteration, the hpcfactor.com membership database has been blighted by SPAM. We are no different to any web community in this regard. A can be a particular problem as many accounts that we may miss are registered and (if they survive) become a valuable commodity to sell as trojan accounts for use in mischief at a later date.

The moderation team at HPC:Factor does a great job of ensuring that these ‘pay per post’ (usually from Indian IP’s) urchins are wasting their time, but it does not stop them from trying. At one point we were having to content with several hundred a day, and had to shutdown registration completely. A custom layer that I wrote to sit on-top of the BBS registration system has done a good job to convert that SPAM torrent into a trickle of a “couple of times a month”.

Over the years, I have added more and more banned email domains to a ban list that is maintained in the user registration database. It occurred to me that as that list has increased, the historic pool of user account has never been re-parsed against the updated banned domains list.

Sounds simple right? Fire up SQL Server Management Studio and create a SQL SELECT LIKE IN query… is it that simple?

 

The Solution

Yes actually it is.

The obvious logic that you want to follow is to take this syntax

SELECT id, username, emailaddress
FROM members
WHERE emailaddress IN (
   SELECT emailaddress FROM bannedemails
)

… and merge it with a LIKE statement to create a SELECT LIKE IN query, something like

SELECT id, username, emailaddress
FROM members
WHERE emailaddress LIKE IN (
   SELECT '%' + emailaddress FROM bannedemails
)

Unfortunately however this syntax is completely illegal.

The answer is to perform the LIKE/IN as part of an INNER JOIN

SELECT members.id, members.username, members.emailaddress
FROM members
INNER JOIN bannedemails ON members.emailaddress LIKE '%' + bannedemails.emailaddress

 

Performance

This is a not a “real time” friendly SQL expression. The inefficiency of using LIKE evaluation becomes very apparent as the record set scales. The HPC:Factor database had 2,271 banned email domains and 30,336 user. Giving a total record iteration and evaluation count of 68,893,056 rows.

The total query execution time for this on was 2 minutes 13 seconds (ouch). Having said that, on reviewing why it took that long, I realised that at some point in time I was being extremely stingy with the SQL Server VM. It only had access to 2 CPU cores and 12 GB of RAM. The hypervisor server itself has access to a 10 core hyper-threaded Xeon.

 

The Result

I have a lot of respect and sympathy for people who use VPNs and temporary email addresses and I genuinely understand why they feel the need (or in some cases must, depending on what part of the world you are in).

I saying that, I also have to deal with the issue from the other side of the equation. If the website is having a bad day, it’s usually because of something coming from one or both of these camps. As ever in life, a minority will insist on spoiling a good thing for everyone else.

After running the query, we had only missed 8 user accounts – credit to the moderation team. The oldest account was 26/09/2006 and my favourite that we probably should not have missed was “mrputin” from January 2013.