SQL Server logins (both Windows and SQL types) must be identical on all replicas and their SID (Security Identifier) must match. Windows logins will automatically use the same SID but must be created on both replicas. For SQL logins, you must use the following steps:
NOTE: This does not apply to Windows logins, only SQL logins.
- Connect to the AlwaysOn listener. This ensures you are working with the primary node.
- Create the SQL Login and database mappings, security settings, etc.
- Next, run this query:
SELECT SUSER_SID ('USERNAME')
This will return the user's SID which is the unique identifier for the login. Even if the names match, unless the SID is the same the SQL login will not work properly on the secondary node after a failover.
- Connect to the secondary node. Be sure to check/refresh the Availability Group to ensure you are working with the correct node.
- Copy the SID into the following query, also update the username and default database as needed. Be sure to use the same password as you did when first creating the user.
CREATE LOGIN [USERNAME] WITH PASSWORD=N'PASSWORD', SID=[SID from prior query], DEFAULT_DATABASE=[Master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
This should create the SQL login on the secondary node with the same username, password, SID and default database.
- If you wish to test, open the Availability Group dashboard, ensure it is "all green" and then initiate a manual failover. This will make the secondary the primary and vice versa.
- Test your new login.