When using SQL AlwaysOn Availability groups with local SQL users you need to ensure that the SQL users have the same SID on both servers. To do this create your Database and you local SQL account on the Source SQL server. Obtain the SID of the user account and then use the below script to create the account on the second server. You can then configure the AAG for failover.
In this example the Database named USERDB1 and the local user is Username.
In this example the Database named USERDB1 and the local user is Username.
The following commands were ran to obtain the SID of the
local SQL user (on the source server):
USE [master]
SELECT SUSER_SID ('username')
We then used the following command to create the SQL login
on the secondary AAG cluster node:
USE [master]
GO
CREATE LOGIN [USERDB1] WITH PASSWORD=N'xxxxxxxx’, SID=xxxxxxxx, DEFAULT_DATABASE=MASTER, DEFAULT_LANGUAGE=us_english, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
Comments
Post a Comment