DeployCommerceDatabase: SQL Server Provider for Windows PowerShell is not installed.

I got the following error when running the DeployCommerceDatabase task during Experience Commerce 9 Update 2 installation:

SQL Server Provider for Windows PowerShell is not installed.

In order to fix this issue, I tried the following command in PowerShell to install the SqlServer PowerShell module:

Install-Module -Name SqlServer

However, this did not work, and I got the same error. The next step was to look at the actual deploy script (SIF.Sitecore.Commerce.1.2.14\Modules\DeployCommerceDatabase\DeployCommerceDatabase.psm1). I could see that it was adding the SQL Server Provider as a Snapin based on the version found in:

HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps

After inspection the registry, I found that I did not have that entry:

As a last resort, I uninstalled SQL Server Management 2017, installed 2016, and installed SqlServer PowerShell module. Unfortunately, this did not do the trick either, and I still couldn’t see the registry entry after installing the SqlServer PowerShell module.

It turns out if you don’t have SQL Server 2016 installed on your computer and you don’t want to install it (e.g. connecting to a remote SQL server), you need to install Powershell Tools from the Microsoft SQL Server 2016 Feature Pack:

After installing it, I can see that I now have the correct PowerShell module:

Note: If you get the same error, and have SQL Server 2017 installed instead of SQL Server 2016, have a look at this post from Marc Bastiaansen.