Here is just a small problem I experienced while adding databases with SQL Server authentication enabled (used for some php websites) to an availability group.

Steps I took to add the databases:

1. Add all logins to the secondary server (SQL Management Studio > Security > Logins > Create login)
2. Add database to availability group (Always On High Availability > Availability Groups > desired group > Add Database)

So the operation was successful and all databases were synchronized.
Now I did a manual failover to the second node. But the php application was not able to login anymore.

What happened? SQL users in the database are mapped to SQL logins. When I did the failover to the second node the SQL users sid did not match the sid of the SQL login with the exact same name anymore.

Now you could simply use the sp_change_users_login stored procedure to “remap” the login to the user, but you would need to do that after every failover – not a very nice solution.

You could also use the partial contained databases feature so you don’t need SQL logins anymore. Since php is a business need we cannot easily use this because php’s mssql_connect function does not support connection strings which you would need forcefully so SQL Server does know the ininital catalog you want to connect to.

So there is one last option. Make sure that all SQL users that shall be used on multiple nodes have a SQL login whose sid is the same on every node.
How to achieve that?

If you don’t already have a sql login create a new one (e.g. sql_login) on the first node. Use the sp_change_users_login stored procedure to map the login to the db user (sql_db_user):

sp_change_users_login 'update_one', 'sql_db_user', 'sql_login'

Now get the sid of the newly created login:

Now that you have the login’s sid you can run the following query on the next node to create a login with the same sid:

So that’s it. Now you have a login with the same sid on every node. You can easily failover to any node and your logins will not break anymore.

As a last step, make sure that the default database (properties page of the SQL login) for each login is the same on every node…