LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon