Windows Azure ACS does not support username/password authentication in the form you need. Shahin outlined it in details.
Your options really are:
While classic on-premises Active Directory deployed with a Domain Controller option is indeed quite enterprisy, WAAD is not exact copy of the same in the Cloud. I would actually recommend that you give a shot and try out. Using the WAAD Graph API you can fully automate user management in WAAD.
Your second option is based on IdSVR. This is full-featured Identity Server and Security Token Service. It is developed against the best practices in security and is quite easily extensible! While it does not provide out of the box support for user management (user registration, password reset, etc.) it is ASP.NET MVC 4 application. So you can very easily add this features yourself. There actually is an open enhancement planned for User Management API and UI.
Both WAAD and IdSVR integrate well with ACS, so you can provide seamless support for social identities along with own login.
Another service that is still in Beta/Preview is the SocialSTS project. It is kind of extension to Azure ACS and is designed to help you add more identity providers (such as LinkedIn, Twitter, GitHUB, Amazon Login, etc) to your Access Control Service.