Zubius (4/1/2014)
How should application/users security be set up? If server #1 fails, availability group #1 fails over to server #2 but the security does not, so applications fail. I have duplicated all users to server #2 (from server #1) but the apps/users' mappings are not there and cannot be created...Thanks
Just like Log Shipping and Database Mirroring, for SQL Server authentication accounts you'll need to synchronise these between the replicas.
This includes preserving the account SIDs to avoid orphaned users. Plenty of scripts available to help with this, here's a basic to get you going
SELECT'CREATE LOGIN [' + name + '] WITH PASSWORD = ' +
sys.fn_varbintohexstr(password_hash) +
' HASHED, SID = ' + sys.fn_varbintohexstr(sid) +
', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name) +
', DEFAULT_LANGUAGE = ' + default_language_name +
', CHECK_EXPIRATION = ' +
CASE
WHEN is_expiration_checked = 0 THEN 'OFF'
ELSE 'ON'
END +
', CHECK_POLICY = ' +
CASE
WHEN is_policy_checked = 0 THEN 'OFF'
ELSE 'ON'
END +
CASE is_disabled
WHEN 0 THEN ''
ELSE '; ALTER LOGIN [' + name + '][ DISABLE;'
END
FROM master.sys.sql_logins
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉