Always On Availability Group failover fails when using a local SQL service account

Version 1

    Introduction

    If you have configured either the Personalization or Management Server databases to use a local sql account for the service account, you may find that when a SQL Always On failover takes place, the connection to the database will no longer work.

    You may see entries similar to the following in the Application Event log on your server:

    Error 30/06/2016 20:35:48 AppSenseBackgroundService 0 None
    Process Action Records: database error:The connection is broken and recovery is not possible. The client driver attempted to recover the connection one or more times and all attempts failed. Increase the value of ConnectRetryCount to increase the number of recovery attempts.Cannot open database "AppSensePERS" requested by the login. The login failed.

    Additionally, you may see the following error in the fatal event logs on your personalization server (found in event viewer under the path "Applications and Services Logs > AppSense Server > EnvironmentManager > Personalization > Admin"):

    Error 30/06/2016 20:55:46 AppSenseServer-EnvironmentManager-Personalization 5 (65529)
    (7) [ProfileService.HandlerImplementation.GlobalApplication] Exception checking DALSchema compatibilityProfileService.Common.PSDatabaseErrorException: The database has generated an exception
    System.Data.SqlClient.SqlException: The target database, 'PersonalizationServer', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access.

    Detail

    When creating a local SQL account, the server you create it on will also create a unique SID which exists locally on that server.

    If this account is used as the service account for your Personalization or Management server, the account will be mapped to the relevant database using this same SID, and when using Always On this mapping will be synchronized to the secondary database.

    In a failover scenario, the personalization server will continue to communicate via the listener using the same SQL account username and password, meaning that while it will be able to authenticate to the second SQL server, the login details will be associated with different SID which only exists on the secondary database.

    The end result is that no mapping will exist for this users' SID on the secondary database.

    You can specify the SID to be used when creating a SQL account as follows:

    1. Once you have created the service account on your primary server, you can find the SID by running the following query (using an account called 'appsensesql' as an example):

    USE [master]
    SELECT name
    , sid
    FROM sys.server_principals
    WHERE name = 'appsensesql'

    2. Once you have the value of the 'SID' column you can create an identical account on the secondary SQL server with the same SID as follows:

    USE [master]
    CREATE LOGIN [appsensesql]
    WITH PASSWORD=N'Password01'
    , SID = 0x84A5FD548F4E3C489401FFA1FA1AFAA2
    , DEFAULT_DATABASE=[master]