Recréer un login SQL avec le même SID sur les réplicas Always On
Générer un CREATE LOGIN avec mot de passe haché et SID identique pour éviter les utilisateurs orphelins après basculement.
Dans un groupe de disponibilité Always On, les bases sont répliquées mais pas les logins, qui vivent dans master. Si le login d'une application n'a pas le même SID sur tous les réplicas, l'utilisateur de la base devient orphelin après un basculement. Cet article montre comment recréer un login SQL sur un réplica secondaire avec le même SID et le même mot de passe (haché).
Générer l'instruction CREATE LOGIN depuis le primaire
Ce script lit le hash du mot de passe et le SID du login sur le réplica primaire, puis affiche l'instruction CREATE LOGIN à exécuter sur chaque secondaire.
DECLARE @user nvarchar(128) = 'app_user';
DECLARE @PasswordHash nvarchar(200);
DECLARE @sid nvarchar(200);
DECLARE @sql nvarchar(max);
SELECT @PasswordHash = master.dbo.fn_varbintohexstr(
CAST(LOGINPROPERTY(@user, 'PASSWORDHASH') AS varbinary(256)));
SELECT @sid = master.dbo.fn_varbintohexstr(sid)
FROM dbo.syslogins
WHERE name = @user;
SET @sql = 'CREATE LOGIN ' + @user
+ ' WITH PASSWORD=' + @PasswordHash + ' HASHED, SID = ' + @sid;
PRINT @sql;
Copiez le résultat affiché et exécutez-le sur les autres réplicas.
Créer un login avec un SID imposé
Pour connaître le SID d'un login existant :
SELECT sid FROM dbo.syslogins WHERE name = 'app_user';
SELECT sid FROM dbo.sysusers WHERE name = 'app_user';
SELECT sid FROM sys.database_principals WHERE name = 'app_user';
Création du login avec ce SID :
CREATE LOGIN app_user WITH PASSWORD = '<PASSWORD>',
SID = 0x0421442FAD357F4B9D269A52B93B78C8;
Créer l'utilisateur dans une base
Dans une base contenue (contained database), l'utilisateur peut aussi être créé avec un mot de passe et un SID :
USE AppDb;
GO
CREATE USER app_user WITH PASSWORD = '<PASSWORD>',
SID = 0x0421442FAD357F4B9D269A52B93B78C8;
Remarques
- Les valeurs de SID et les mots de passe ci-dessus sont des exemples génériques.
- Pour un login créé sans SID imposé, le SID est généré aléatoirement et diffère donc d'un réplica à l'autre.