Reverse de logins SQL Server 2000


1- Introduction

Sous SQL Server, syslogins n'est pas une table système comme sous Sybase, il s'agit en fait d'une vue sur la table sysxlogins.

Cet article présente quelques particularités sur la table sysxlogins et des méthodes pour générer un reverse des logins dans un serveur SQL Server avec les procédures stockées sp__dba_hexadecimal et sp__dba_revlogin.

2- Table sysxlogins

Brièvement, la table sysxlogins (comparativement à la table syslogins sous Sybase) comporte les particularités ci-dessous :

Colonne Type Description
srvid smallint Identifiant du serveur rattaché au login : NULL pour un login rattaché au serveur même et supérieur 1 si il s'agit d'un login rattaché à un serveur lié référencé dans sysservers (linked server)
sid varbinary(85) Security Identifier (identifiant de sécurité), c'est l'équivalent de suid sous Sybase, toutefois sous SQL Server, cet identifiant est de type varbinary.
xstatus smallint Colonnes rassemblant des informations de statut sur les logins (authentification NT ou SQL Server etc...).
password varbinary(256)

A la différence de Sybase, la correspondance entre les logins (syslogins) et les utilisateurs dans les bases de données (sysusers) est réalisée avec la colonne SID qui est de type varbinary(85).

3- Authentification

Deux méthodes d'authentification sont disponibles sous SQL Server : l'authentification NT et l'authentification SQL Server.

Il existe une méthode simple pour déterminer si il s'agit d'une authentification NT ou SQL Server :

3-1- Authentitication NT : sp_grantlogin, sp_denylogin, sp_revokelogin

Les procédures sp_grantlogin et sp_denylogin ajoutent un login pour un compte NT utilisateur ou un groupe.

Syntaxes :

sp_grantlogin [@loginame =] 'login'
sp_denylogin [@loginame =] 'login'

Le login doit être un compte ou un groupe NT au format Domain\User. La procédure sp_grantlogin créé le login et autorise l'accès alors que la procédure sp_denylogin créé le login mais n'autorise pas l'accès au serveur SQL.

Exemple :

sp_grantlogin @loginame='BUILTIN\Administrateurs'
sp_denylogin @loginame='BUILTIN\Administrateurs'

Il est possible de déterminer, en requêtant xstatus dans la table sysxlogins, les logins associés à des comptes ou groupes NT pour lesquels l'accès au serveur est autorisé ou pas :

La colonne xstatus de la table sysxlogins est également en mesure d'indiquer si il s'agit d'un compte utilisateur ou d'un groupe NT :

Le mot de passe est nécessairement celui du compte NT et ne peut en aucun être modifié avec la procédure sp_password, il doit être modifié par le système de sécurité de NT.

La procédure sp_revokelogin est utilisée pour supprimer un login associé à un compte ou groupe NT :

sp_revokelogin [@loginame =] 'login'

La base de données par défaut des logins associés à des comptes NT est la base master et le langage par défaut est celui du serveur, pour modifier ces paramètres, les procédures sp_defaultdb et sp_defaultlanguage doivent être appliquées.

sp_defaultdb [@loginame =] 'login', [@defdb =]'database'
sp_defaultlanguage [@loginame =] 'login', [@language =]'language'

Exemple :

exec sp_defaultdb @loginame='BUILTIN\Administrateurs', @defdb='master'
exec sp_defaultlanguage @loginame='BUILTIN\Administrateurs', @language='us_english'

3-2- Authentification SQL Server : sp_addlogin, sp_droplogin

Les logins propres à SQL Server (authentification SQL Server) sont ajoutés avec les procédures stockées sp_addlogin et sp_droplogin.

Il y a seulement 2 particularités à noter sur la procédure stockée sp_addlogin sous SQL Server (comparativement à Sybase) : en effet la procédure sp_addlogin accepte en paramètres le SID (System Identifier) et le mot de passe crypté, ceci facilite grandement les tâches d'administration pour resynchroniser les logins entre deux SQL Server.

sp_addlogin [ @loginame = ] 'login'
  [ , [ @passwd = ] 'password' ]
  [ , [ @defdb = ] 'database' ]
  [ , [ @deflanguage = ] 'language' ]
  [ , [ @sid = ] sid ]
  [ , [ @encryptopt = ] 'encryption_option' ]

3-2-1- Forcer le SIDà la création d'un login

Avec la syntaxe MS SQL Server, le SID (Security Identifier number) peut être forcé lors de la création du login.

Exemple :

EXEC master..sp_addlogin 'Northwind_reader', @pwd='Northwind_pwd', @defdb ='Northwind' , @deflanguage = 'us_english' , @sid = 0x68DE5743C9540144989407CE9AA5C30F

3-2-2- Forcer un mot de passe crypté à la création d'un login

Le dernier paramètre @encryptopt de la procédure stockée sp_addlogin permet d'indiquer à SQL Server lors de la création du login si le mot de passe est déjà crypté et doit être conservé tel quel ou non. Trois valeurs sont possibles pour ce paramètre @encryptopt

NULL Le mot de passe doit être encrypté, c'est la valeur par défaut de ce paramètre
skip_encryption Le mot de passe est déjà encrypté. SQL Server doit stocker la valeur dans sysxlogins sans effectuer d'encryptage
skip_encryption_old Le mot de passe fourni a été encrypté avec une version antérieure à SQL Server 7. SQL Server doit stocker la valeur sans effectuer d'encryptage. Cette option doit être fournie pour les logins ayant subi des upgrades SQL Server 6.5 vers SQL Server 7 ou 2000

Lorsqu'il s'agit d'un login qui a subi un upgrade de SQL Server 6.5 vers 7.0 ou 8.0 (2000), l'option 'skip_encryption_old' doit impérativement être spécifiée (le mode de cryptage de la fonction pwdencrypt et l'utilisation de la fonction pwdcompare sont en effet modifiés entre la version 6.5 et la version 7.0) .

La requête ci-dessus permet de retrouver rapidement les logins issus d'un serveur ayant subi un upgrade depuis la version 6.5 :

select name from sysxlogins where xstatus & 2048 = 2048
go

Exemple :

DECLARE @pwd sysname
SET @pwd = CONVERT (varbinary(256),
0x0100F1068A1A97557737A25B33C9D64204DDD8C4D306C463E8190E513D6E39F6388493BD18BF6D4716D7E35F6A38
)
EXEC master..sp_addlogin 'pubs_dbo', @pwd, @defdb ='pubs' ,
@deflanguage = 'us_english' ,
@sid = 0x2E2225C98DF33A49BEE81D255A34E496, @encryptopt = 'skip_encryption'

4- Reverse de logins avec sp__dba_revlogin

La procédure stockée sp__dba_revlogin permet de réaliser du reverse de logins (sauf pour le login sa).

Usage :

exec sp_dba__revlogin

Cette procédure stockée a besoin de procédure sp__dba_hexadecimal qui permet de convertir du varbinary (256) en du varchar (256).

Exemple :

> exec sp_dba__revlogin
    DECLARE @pwd sysname
    -- Generating creation for login: CGC\OLAP Administrators
    PRINT 'Creating login [CGC\OLAP Administrators]'
    EXEC master..sp_grantlogin 'CGC\OLAP Administrators'
    EXEC master..sp_defaultdb @loginame='CGC\OLAP Administrators', @defdb='master'
    EXEC master..sp_defaultlanguage @loginame='CGC\OLAP Administrators', @language='us_english'
    -- Generating creation for login: CGC\SQLServerAdmin
    PRINT 'Creating login [CGC\SQLServerAdmin]'
    EXEC master..sp_grantlogin 'CGC\SQLServerAdmin'
    EXEC master..sp_defaultdb @loginame='CGC\SQLServerAdmin', @defdb='master'
    EXEC master..sp_defaultlanguage @loginame='CGC\SQLServerAdmin', @language='us_english'
    -- Generating creation for login: Northwind_dbo
    PRINT 'Creating login [Northwind_dbo]'
    SET @pwd = CONVERT (varbinary(256), 0x0100A52BDE15615A1916E8751F4982FEDD01A7D9F81607FD228ED18070E2412292BFE41231984B9B156BB129DCD1)
    EXEC master..sp_addlogin 'Northwind_dbo', @pwd, @defdb ='Northwind' , @deflanguage = 'us_english' , @sid = 0xE6940D5E39A94E4F8533BC55968C492E, @encryptopt = 'skip_encryption'
    -- Generating creation for login: northwind_reader
    PRINT 'Creating login [northwind_reader]'
    SET @pwd = CONVERT (varbinary(256), 0x01000B4238158A71E34C9A5C72B9AA0699B555B70AC211DED5337E32614F878B0E178C7ABCE3818428B0FBC9ACBC)
    EXEC master..sp_addlogin 'northwind_reader', @pwd, @defdb ='Northwind' , @deflanguage = 'us_english' , @sid = 0x33CFB95F2391BF46B1A9842D0AE6A458, @encryptopt = 'skip_encryption'

Le script généré peut ainsi être appliqué sur un nouveau serveur SQL Server permettant ainsi de recréer des logins avec les bases de données, les langages, les SIDs et les mots de passe.

    Creating login [CGC\OLAP Administrators]
    Granted login access to 'CGC\OLAP Administrators'.
    Default database changed.
    CGC\OLAP Administrators's default language is changed to us_english.
    Creating login [CGC\SQLServerAdmin]
    Granted login access to 'CGC\SQLServerAdmin'.
    Default database changed.
    CGC\SQLServerAdmin's default language is changed to us_english.
    Creating login [Northwind_dbo]
    New login created.
    Creating login [northwind_reader]
    New login created.

4-1- Code source sp__dba_hexadecimal

    CREATE PROCEDURE sp__dba_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar(256) OUTPUT
    AS
    /**
    * Génère un script de conversion du varbinary(256) en varchar(256), valeur hexadécimale
    *
    *
    * Usage : sp__dba_hexadecimal
    * Date : 11.2004
    * Author :
    *
    * Version : 1.0 SQL Server 7 & 8 (2000)
    */
    DECLARE @charvalue varchar(256)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
    WHILE (@i <= @length)
    BEGIN
    DECLARE @tempint int
    DECLARE @firstint int
    DECLARE @secondint int
    SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
    SELECT @firstint = FLOOR(@tempint/16)
    SELECT @secondint = @tempint - (@firstint*16)
    SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
    SELECT @i = @i + 1
    END
    SELECT @hexvalue = @charvalue
    GO

4-2- Code source sp__dba_revlogin

    CREATE PROCEDURE sp__dba_revlogin @login_name sysname = NULL AS
    /**
    * Génère un script automatique de reverse des logins
    *
    * Usage : sp__dba_revlogin @login_name
    * Date : 11.2004
    * Author : Stephane PAQUOT
    *
    * Version : 1.0 SQL Server 7 & 8 (2000)
    */
    DECLARE @name sysname
    DECLARE @xstatus int
    DECLARE @binpwd varbinary (256)
    DECLARE @txtpwd sysname
    DECLARE @defdbname sysname
    DECLARE @deflanguage sysname
    DECLARE @tmpstr varchar (256)
    DECLARE @SID_varbinary varbinary(85)
    DECLARE @SID_string varchar(256)
    /** Declaration du curseur : tous les logins ou aucun */
    IF (@login_name IS NULL)
    DECLARE c_login_curs CURSOR FOR
    /** Recuperation de tous les logins (logins non remote ) */
    SELECT a.sid, a.name, a.xstatus, a.password, b.name, a.language
    FROM master..sysxlogins a, master..sysdatabases b
    WHERE srvid IS NULL
    AND a.name <> 'sa'
    AND a.dbid = b.dbid
    ELSE
    DECLARE c_login_curs CURSOR FOR
    SELECT a.sid, a.name, a.xstatus, a.password, b.name, a.language
    FROM master..sysxlogins a, master..sysdatabases b
    WHERE srvid IS NULL
    AND a.name = @login_name
    AND a.dbid = b.dbid
    OPEN c_login_curs
    FETCH NEXT FROM c_login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @defdbname, @deflanguage
    IF (@@fetch_status = -1)
    BEGIN
    PRINT 'No login(s) found.'
    CLOSE c_login_curs
    DEALLOCATE c_login_curs
    RETURN -1
    END
    PRINT 'DECLARE @pwd sysname'
    WHILE (@@fetch_status <> -1)
    BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
    PRINT ''
    SET @tmpstr = '-- Generating creation for login: ' + @name
    PRINT @tmpstr
    SET @tmpstr = 'PRINT ''Creating login [' + @name + ']'
    PRINT @tmpstr
    /** NT auth. */
    IF (@xstatus & 4) = 4
    BEGIN
    IF (@xstatus & 1) = 1
    BEGIN -- NT login is denied access
    SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
    PRINT @tmpstr
    END
    ELSE BEGIN -- NT login has access
    SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
    PRINT @tmpstr
    END
    SET @tmpstr = 'EXEC master..sp_defaultdb @loginame=''' + @name + ''', @defdb=''' + @defdbname + ''''
    PRINT @tmpstr
    SET @tmpstr = 'EXEC master..sp_defaultlanguage @loginame=''' + @name + ''', @language=''' + @deflanguage + ''''
    PRINT @tmpstr
    END
    /** SQL Server auth. */
    ELSE BEGIN
    IF (@binpwd IS NOT NULL)
    BEGIN -- Non-null password
    EXEC sp__dba_hexadecimal @binpwd, @txtpwd OUT
    IF (@xstatus & 2048) = 2048
    SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
    ELSE
    SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
    PRINT @tmpstr
    EXEC sp__dba_hexadecimal @SID_varbinary,@SID_string OUT
    SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
    + ''', @pwd, @defdb =''' + @defdbname + ''' , @deflanguage = ''' + @deflanguage + ''' , @sid = ' + @SID_string + ', @encryptopt = '
    END
    ELSE BEGIN
    -- Null password
    EXEC sp__dba_hexadecimal @SID_varbinary,@SID_string OUT
    SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
    + ''', NULL, @defdb =''' + @defdbname + ''' , @deflanguage = ''' + @deflanguage + ''' , @sid = ' + @SID_string + ', @encryptopt = '
    END
    IF (@xstatus & 2048) = 2048
    -- login upgraded from 6.5
    SET @tmpstr = @tmpstr + '''skip_encryption_old'''
    ELSE
    SET @tmpstr = @tmpstr + '''skip_encryption'''
    PRINT @tmpstr
    END
    END
    FETCH NEXT FROM c_login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @defdbname, @deflanguage
    END
    CLOSE c_login_curs
    DEALLOCATE c_login_curs
    RETURN 0
    GO

Annexe

Historique

Version Date Commentaires
1.0 12/2004 Version initiale

Liens

MSDN Books Online, SQL Server 2000, Transact-SQL reference, sp_addlogin
MSDN Books Online, SQL Server 2000, Transact-SQL reference, sp_grantlogin