Fabien Celaia

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 - Visualiser dans une fenêtre à part

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 - Visualiser dans une fenêtre à part

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 - Visualiser dans une fenêtre à part

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 - Visualiser dans une fenêtre à part

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 - Visualiser dans une fenêtre à part

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.