Microsoft SQL Server : problématique historique de la désynchronisation des logins
Par Fabien Celaia

Le , par Fabien Celaia, Rédacteur/Modérateur
Historique

Architecturalement parlant, une instance MS-SQL gère plusieurs bases de données.
C'est le cas de la plupart des moteurs SGBDR, à l'exception notable de Oracle.
Le modèle de Microsoft SQL Server est issu de l'architecture Sybase et date de ... 1986...

Reste donc toujours cette épine dans le pied des DBA lors de la restauration d'une base de données sur une autre instance, ou lors de la gestion de base AlwaysOn... la synchronisation des logins (dépendants de la base master) et des utilisateurs (propres à chaque base)

Un utilisateur doit être raccordé à un login pour pouvoir accéder à ses bases. Ce lien login/utilisateurs se fait via le SID, colonne unique dans la table sysxlogins et qui doit correspondre au SID de l'utilisateur (colonne sysuser.sid).

La méthode historique
Dans les temps immémoriaux, il "suffisait" de synchroniser 2 instances avec la même liste de logins. En cette glorieuse époque où le DBA pouvait encore tripatouiller les tables système, cela se faisait aisément.
  • bulk copy de la table master..syslogins source ;
  • suppression de toutes les lignes de la table de logins cibles à l'exception du sid=1 (sa) ;
  • Import via bulk copy avec l'option -b1 qui permettait d'ignorer l'erreur liée à l'ajout de duplicats.


La méthode moyenâgeuse
Après la version 2000, plus possible de modifier ces tables... le métamodèle change avec les tables historiques remplacées par des vues et de nouvelles tables (sysxlogins)

Apparaissent alors des procédures stockées permettant de résoudre ce souci de synchronisation : la fameuse sp_change_users_login et autre sp_help_revlogin / sp_hexadecimal

Nouvelle syntaxe
Depuis la version 2014, une syntaxe plus agréable existe, permettant de rattacher un utilisateur existant à un login existant :
Code SQL : Sélectionner tout
1
2
3
4
USE MaBase 
GO 
alter user MonUtilisateurDansMabase with LOGIN=UnLoginExistant 
GO
... mais subsiste toujours la problématique des logins inexistants...

Synchronisation
Reste cependant que la synchronisation des sid est toujours un problème lorsque l'on travaille avec plusieurs serveurs : c'est par exemple le cas avec les bases AlwaysOn.

En théorie, c'est juste prometteur et parfait : on prend une base, un la place dans un groupe de disponibilité (availability group) et le tour est joué : la base peut passer aisément d'une instance à l'autre en actif.
La réalité est un peu plus tristounette : si la base et les utilisateurs sont bien gérés dans les groupes, la problématique des logins à synchroniser reste entière-. Les n instance SQL se partageant des bases DOIVENT synchroniser leurs logins... et donc rester relativement homogènes

C'est u peu traitre, car tout se passera bien pour vous et vos utilisateurs jusqu'au moment ou vous vous connecterez sur l'autre instance... et lorsque l'on se connecte à un listener d'un availability group, on ne sait pas toujours quelle instance va répondre... D'où des pannes désagréables, car semblant aléatoires : connecte... connecte pas... connecte.... connecte pas...

Via Powershell, il est possible de migrer ses logins vers un autre serveur

Dans un premier temps, installer le module adéquat sur le serveur. Cette opération n'est pas nécessaire sen W10 ou WinServer2016. À noter que ce module est installé sur la partie utilisateur... donc chaque DBA devra faire l'installation.

Code Powershell : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
PS C:\Users\dvp> Invoke-Expression (Invoke-WebRequest -UseBasicParsing https://dbatools.io/in) 
dbatools was not installed by the PowerShell Gallery, continuing with web install. 
Installing module to C:\Users\dvp\Documents\WindowsPowerShell\Modules\dbatools 
Creating directory: C:\Users\dvp\Documents\WindowsPowerShell\Modules\dbatools 
Downloading archive from github 
Unzipping 
Applying Update 
1) Backing up previous installation 
2) Cleaning up installation directory 
3) Setting up current version 
Done! Please report any bugs to dbatools.io/issues or clemaire@gmail.com. 
  
dbatools v 0.9.394 
# Commands available: 419 
  
  
  
If you experience any function missing errors after update, please restart PowerShell or reload your profile.

Aussitôt fait, on peut passer à l'export des logins : ce script génère les ordres SQL a rejouer sur le serveur cible.
Code Powershell : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
  
PS C:\Users\dvp> export-dbalogin -sqlinstance mssql-dvp -database master -login $logins -FilePath c:\temp\mssql-dvp-logins.sql 
WARNING: [15:54:50][Export-DbaLogin] Skipping ##MS_PolicyEventProcessingLogin##. 
WARNING: [15:54:50][Export-DbaLogin] Skipping ##MS_PolicyTsqlExecutionLogin##. 
WARNING: [15:55:14][Export-DbaLogin] NT AUTHORITY\SYSTEM is skipped because it is a local machine name. 
WARNING: [15:55:14][Export-DbaLogin] NT SERVICE\MSSQLSERVER is skipped because it is a local machine name. 
WARNING: [15:55:14][Export-DbaLogin] NT SERVICE\SQLSERVERAGENT is skipped because it is a local machine name. 
WARNING: [15:55:14][Export-DbaLogin] NT SERVICE\SQLWriter is skipped because it is a local machine name. 
WARNING: [15:55:14][Export-DbaLogin] NT SERVICE\Winmgmt is skipped because it is a local machine name. 
WARNING: [15:55:16][Export-DbaLogin] Skipping sa. 
  
  
    Directory: C:\temp 
  
  
Mode                LastWriteTime     Length Name 
----                -------------     ------ ---- 
-a---        28.08.2018     15:55      26125 mssql-dvp-logins.sql

Les ordres sont de simples create user avec un SID forcé, et récupérable dans la table syslogins si vous souhaitez le faire à la main, sans powershell.

Code TSQL : Sélectionner tout
CREATE LOGIN [MonLogin] WITH PASSWORD=N'uMj10wRtyydbJZd334e8VyhavMJg2xO5786GjhxtRyg=',SID =0xCB97DBE2444BE84685B21F835A68244A, DEFAULT_DATABASE=[mabase], DEFAULT_LANGUAGE=[french], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Ne reste plus qu'à rejouer le script ainsi créé sur l'instance cible... et prier pour qu'il n'y ait pas un login étranger utilisant le même sid...

Au niveau de chaque base ensuite, ne reste plus qu'à contrôler que tous les sid ont bien été synchronisés grâce à la commande suivante :
Code TSQL : Sélectionner tout
1
2
3
4
select 'ALTER USER '+u.name+' WITH LOGIN='+u.name+';' Utilisateur from sysusers u 
where u.uid between 5 and 16383  
and u.hasdbaccess=1 
and sid not in (select sid from master..syslogins)
Si tout se passe bien, elle ne devrait rien retourner.
S'il manque des logins (utilisateurs orphelins sur le site source par exemple), à vous de checker et fixer au besoin.


Vous avez aimé cette actualité ? Alors partagez-la avec vos amis en cliquant sur les boutons ci-dessous :


 Poster un commentaire

Avatar de Asmodan Asmodan - Membre habitué https://www.developpez.com
le 29/08/2018 à 14:02
Salut

Très utile merci !
Avatar de frfancha frfancha - Membre confirmé https://www.developpez.com
le 30/08/2018 à 20:27
La solution la plus simple n'est-elle pas d'utiliser des contained databases et de se passer complètement des logins au niveau de l'instance (sauf ceux des DBA évidemment)?
Nous ne faisons que cela et cela solutionne tous ces problèmes très simplement: ils n'existent plus.
Avatar de SQLpro SQLpro - Rédacteur https://www.developpez.com
le 20/09/2018 à 17:54
Effectivement le concept de CONTAINED DATABASE règle bien des problèmes :
  • utilisateurs se connectant directement à la base
  • plus de problématique de collation entre la base et tempdb

Mais il ne permet pas d'avoir des utilisateurs qui scrutent plusieurs bases simultanément ce que certains développeurs ont fait imbécilement en utilisant plusieurs bases pour une même application !

A +
Contacter le responsable de la rubrique SQL-Server