I guess you have something like this in web.config:

<membership defaultProvider="MultiplexingMembershipProvider" userIsOnlineTimeWindow="10" hashAlgorithmType="HMACSHA512">
 ...
</membership>

<roleManager enabled="true" defaultProvider="MultiplexingRoleProvider" cacheRolesInCookie="true">
 ...
</roleManager>

Just replace

<membership defaultProvider="MultiplexingMembershipProvider" userIsOnlineTimeWindow="10" hashAlgorithmType="HMACSHA512">

with

<membership defaultProvider="SqlServerMembershipProvider" userIsOnlineTimeWindow="10" hashAlgorithmType="HMACSHA512">

and

<roleManager enabled="true" defaultProvider="MultiplexingRoleProvider" cacheRolesInCookie="true">

with

<roleManager enabled="true" defaultProvider="SqlServerRoleProvider" cacheRolesInCookie="true">

Multiplexing providers are enabled by default in Alloy, and they allow you to use both Windows and custom (SQL Server) login.

It's still not possible to add sql user to an AD group. I want to create a new admin user (using SqlServerMembershipProvider) and add that user to the Administrators group.

If you already have Administrators AD group, then you should create a new group in SQL Server, for example WebAdmins, and give it admin permissions.

You can check this article: http://dcaric.com/blog/episerver-how-to-log-in-to-admin-edit-mode

I used ASP.NET Configuration tool to create users / groups, but since you have access to admin mode, you can do it from EPiServer.

As administrators are coming from Windows role provider - you will not be able to add users directly to that group - as that provider becomes read-only inside EPiServer.

How I'm doing it with fresh EPiServer installation:

a) in Alloy windows is configured as 1st providers (membership and role).

b) if you know any local user with Administrators permissions - use that, otherwise create new user in Administrators group.

c) login in EPiServer with that user

d) create new roles in "Group management" under Admin in EPiServer UI, called: WebAdmins, CmsAdmins (these are built-in roles).

e) create new user (this will be stored in SqlMembershipProvider) and add it to both roles

Now you should be able to login in EPiServer using new user. If you succeed - you can remove user created in step b)

Thanks Dejan, 3 years later still your post is very helpful.

Just a top up to Dejan Caric's answer, as now ASP.NET configuration option has been removed from Visual Studio newer versions. In order to create users locally please follow video here to access the page or summarised as below: https://www.youtube.com/watch?v=-t_bJW80NQQ

1. Go to program files (x 86 for 64 bit OS) folder -> IIS Express -> Shift + Right click -> Open Powershell 
2. Write command iisexpress.exe /path:C:\Windows\Microsoft.NET\Framework\v4.0.30319\ASP.NETWebAdminFiles /vpath:"/ASP.NETWebAdminFiles" /port:[port] /clr:4.0 /ntlm
3. If above command is not recognized preceed it with .\ and replace [port] with your the port number appearing in the url when you run the application locally like below.\iisexpress.exe /path:C:\Windows\Microsoft.NET\Framework\v4.0.30319\ASP.NETWebAdminFiles /vpath:"/ASP.NETWebAdminFiles" /port:31337 /clr:4.0 /ntlm
4. Now powershell window will show something like "Press Q to close" PLEASE DO NOT CLOSE THE POWERSHELL WINDOW, just minimize it and keep it up and running in background
5. Now browse to url using your localhost port number i.e. replace your local port number in place of 31337 in the url - <a "http://localhost:31337/ASP.NETWebAdminFiles/default.aspx">http://localhost:31337/ASP.NETWebAdminFiles/default.aspx

It works for me. Hope it helps you as well reach the ASP.NET Configuration page and then follow D.Caric's post to create user.