How secure they are is dependent entirely on what you are wanting to defend against, and what you are wanting to defend. Defending against an internal employee who wants to look at their colleague's sales records is different from an external attacker who wants to steal customer information, which is different than defending against a government who wants to steal your proprietary designs.
generally speaking, these providers are not insecure as they follow the basic recommendations like salting passwords, allowing you to change the hash algorithm, etc (including non-crypto stuff as well). However then you get into the configuration problems like making sure the connection to SQL is secure, the page you POST your credentials to is over SSL, or that the session cookies are protected via SSL, access to the database itself is secure, etc.
Comparatively, Windows Auth has similar problems, and then you get into security boundary issues, as it becomes a pain to use Windows Auth over the internet.
In theory an Active Directory store is probably going to be more secure than an average SQL Server database, but in reality if all you are using it for is user authentication on a public website, its likely VERY insecure because it probably isn't treated as nicely as the corporate AD. Then again, corporate AD probably isn't treated nicely either (in general, not necessarily in your case).
SQL databases are prone to SQL injection attacks, Active Directory stores are prone to Windows-specific attacks. SQL databases tend to lead to identity sprawl... multiple user accounts for multiple applications spread across mulitple databases. Active Directory leads centralized accounts. Either could be good or bad, it depends on what you are trying to accomplish.
Writing your own providers is usually not recommended as they haven't gone through public scrutiny, whereas the providers built into .NET have. 3rd party providers run into the same basic problem. How have they been tested?
And then this brings up a question of why are you letting the application handle authentication itself? Maybe it's better to let someone else do it via Claims Based Authentication. This pushes the risk onto another system like Google, Facebook, or Windows Live.