Well, yes and no. By default, it will create a sql server express file in your app_data folder and just use that. If you want to use SQL Server, that will require some minor changes to your web.config file. The only changes that you would ever need would involve specifying the connection string.
If you click on the url below, you will see demo files from a presentation that I did on Membership. One important thing to point out is that .NET has a really useful utility that will allow you to customize some of the membership features and it is all done through a gui, rather than by modifying the web.config file. You can get to it by clicking on the ASP.NET Configuration button. You will see it in the solution explorer and it is the button that looks like a globe with a red hammer to the left of it
http://www.xmlpitstop.com/ArticleManagement/DisplayArticle.aspx?ResourceID=131