Where do I get my actual Azure Website Deployment password?

You can get the current credentials via the Portal or PowerShell/CLI.

Azure Portal

On the portal, there is a button at the top of the webapp blade to download the publish profile (not the deployment credentials blade, but the main web app blade).

Azure PowerShell

First, ensure the Azure PowerShell cmdlets are installed: https://docs.microsoft.com/en-us/powershell/azure/install-azurerm-ps?view=azurermps-6.3.0

1. Open an elevated PowerShell window.
2. Enter $PSVersionTable.PSVersion. Ensure the output shows you have Major version 5 or later. If this command gives you an error then you're running PowerShell v1 which is ancient at this point.
3. Enter Install-Module -Name AzureRM (you may be prompted to update NuGet, in which case you should)
4. Wait for it to finish installing.
5. Enter Import-Module AzureRM
6. Enter Connect-AzureRmAccount and complete the authentication process.

7. Run this command to save the publishing profile to a file on disk (line-breaks added for readability, in reality put this on a single line). Set $WebAppName and $ResourceGroupName as appropriate:

   Get-AzureRmWebAppPublishingProfile
       -ResourceGroupName $ResourceGroupName
       -Name $WebAppName
       -OutputFile creds.xml
       -Format WebDeploy
   

.publishsettings file

The .publishsettings file is an XML file (without line-breaks). Inside you'll find a document with this structure. Look for the userPWD attribute in the <publishProfile> element with publishMethod="MSDeploy". Don't use the FTP credentials (in the second <publishProfile> element) because the username is different.

The userPWD attribute value is not encrypted, but is the base64 (or base62?) encoding of completely random bytes. You can copy and paste this value directly into the credential prompt within Visual Studio's publishing wizard.

<publishData>
    <publishProfile
        profileName="SITE - Web Deploy"
        publishMethod="MSDeploy"
        publishUrl="SITE.scm.azurewebsites.net:443"
        msdeploySite="SITE"
        userName="$SITE"
        userPWD="YOUR PASSWORD IS HERE"                    <-- This attribute here
        destinationAppUrl="http://SITE.azurewebsites.net"
        SQLServerDBConnectionString=""
        mySQLDBConnectionString=""
        hostingProviderForumLink=""
        controlPanelLink=""
        webSystem="WebSites"
    >
        <databases />
    </publishProfile>

    <publishProfile
        profileName="SITE - FTP"
        publishMethod="FTP"
        publishUrl="ftp://SITE.ftp.azurewebsites.windows.net/site/wwwroot"
        ftpPassiveMode="True"
        userName="SITE\$SITE"
        userPWD="FTP PASSWORD IS HERE"
        destinationAppUrl="http://SITE.azurewebsites.net"
        SQLServerDBConnectionString=""
        mySQLDBConnectionString=""
        hostingProviderForumLink=""
        controlPanelLink=""
        webSystem="WebSites"
    >
        <databases />
    </publishProfile>
</publishData>

If you are not familiar with PowerShell, you can try the below instruction to get the deployment username and password using the azure cli.

az webapp deployment list-publishing-profiles --name your_web_app_name --resource-group your_resource_group

You also add --query to retrieve the "userPWD"

az webapp deployment list-publishing-profiles --name your_web_app_name --resource-group your_resource_group --query '[].userPWD' -o tsv

Output will be like

oMarhPTTJc6F7muHfz11232342342342342420tv5Lwikf1Xc