Deep dive SharePoint 2013 access apps issue - SQL 2012 AlwaysOn availability groups not supported

Access 2013 features a new application model that is designed for one purpose―to simplify web development much like earlier versions of Access with Windows development. Access 2013 enables subject matter experts to quickly create an application that can be used to run their business. By using Microsoft SharePoint 2013 to host the front end of the app and Microsoft SQL Server 2012 as its data storage technology, Access 2013 significantly improves the manageability and scalability of Access applications.

The number one issue for Access App is the SQL 2012 database AlwaysOn availability groups not supported. The symptom on the issue is users will access denied error on the Access Apps if database failed over to different instance on the AlwaysOn availability groups. Let’s deep dive to the database created by Access Apps to explain the root cause and workaround.

When you create Access Apps, it will create databases in SharePoint 2013. I’ve created one “HarryDevApp1” with two tables “Employee” and “Rating” with one look up column as in the screen shot. You could find the database name like “db_2105d1af_e924_460f_a749_59376ae2a9fd” for the Access App by click “File” from Access client.

You will find the two tables named “Access. Employee” and “Access.Rating” are created inside that database along with other supporting databases. At meantime, there are two individual logins named “db_2105d1af_e924_460f_a749_59376ae2a9fd_custom” and “db_2105d1af_e924_460f_a749_59376ae2a9fd_dbo” are also created as owners for these two databases. 

 

 

Based on Microsoft support, Access databases won’t be supported in the AlwaysOn scenario due to the nature of it (behaving as an external application, not a SPContentDatabase SPDatabase) and changes on this are only being considered for a future version of SharePoint.  By looking at the tables craeted from the database side, we found the real issue of the user access failing for an access database after a fail-over appears to also be caused because by the access services not properly handling the individual logins it creates for each database. When one of these databases for access services is created it also creates two unique logins. Those logins are only created on the SQL instance where the database was created. These logins are not created on the secondary instance. When we failover to the secondary instance the logins do not exist so the users cannot access the database.

As a result, the current workaround we have is to sync the logins between all AG database instances manually.  

It appears that the Microsoft developers wanted to work around this issue by using a SQL Server feature named Partially Contained Database but it appears that the logins were not created correctly to support this feature. It looks like they are creating these individual users as SQL logins instead of Contained Users. If they created these individual users as Contained users (which is what should be used with Partially Contained Databases) then we would not have this issue.

We have added this information to the Microsoft support  ticket regarding the access app databases since it would be best if Microsoft could implement the supporting Contained Users.