MS SQL Server: Windows authentication works from a local account but not from an MS account

In SQL Server you have logins and users. Logins just get you connected into the SQL Server, users are specific to each database and have their own database permissions.

Adding a login for the cloud user to the SQL Server is the correct way, you can then map it to the same user as your other account.

Login as the local account and open Management -> SQL Server Logs -> Current

Find the line that shows your cloud user failing to login to get the specific spelling as the SQL Server see's it.

Logon failed for user 'someuser'. Reason...

Add 'someuser' as a login. In properties of that login you have created you can use User Mapping to link it to a specific user.