I was able to get a pipeline build in Azure DevOps to fail when the Quality Gate fails simply by adding these 2 properties to the SonarQubePrepare task (under its extraProperties attribute):
sonar.qualitygate.wait=true
sonar.qualitygate.timeout=300
See Overview | SonarQube Docs for more info. The preferred technique still seems to be not to cause individual builds to fail however. Rather SonarSource want you to use a branch policy to block a merge into your master branch if your PR fails to meet the Quality Gate, as @Daniel_Meppiel has already said.