When using SQL logon to authenticate the user (which is not recommended), there are few things you have to be aware of.
First of all, you need a user which is able to create the and initialize Monitoring and Persistence database.
Note that if the database has been created before setup is started, setup will just use it and install all required into specified database.
If the database does not exist before starting of setup, the database will be created.
To activate SQL Logon in setup select SQL Server authentication like shown below:
Note that specified user (in this case appabricuser) need to be in SYSADMIN role. After databases (monitoring and persistence) are created,
it is recommended to toke the user out of SYSADMIN role and to give him AppFabric required roles.
Following picture shows which roles the user needs to have to be able to access the monitoring database.
Next picture shows which roles the user needs to have to access persistence database:
Note that the user needs to be mapped to both databases.In my samples above I used two users appfabricuser and appfabric,
with access to monitoring and persistence database respectively.
Last, but not least. All described in this document does not have to be done if you use Windows Authentication.
More about this: http://msdn.microsoft.com/en-us/library/ee677290.aspx
Read the complete post at http://developers.de/blogs/damir_dobric/archive/2010/06/29/configuring-appfabric-to-use-sql-logons.aspx