Interroger Active Directory et plusieurs bases de données liées depuis SQL SERVER.

Problème

Dans le cadre d’un audit, être en mesure de présenter la liste des salariés qui disposent d’un compte applicatif sur les différents logiciels métier de l’entreprise (dans notre cas dans notre CRM et dans notre ERP) .

Solution

Nous allons extraire des dnnées de 3 base différentes : Active Directory, base de données du CRM et base de données de l’ERP (progiciel métier) à l’aide de serveurs liés et de la commande OPENQUERY. Le lien entre les différentes base de données se fera via l’adresse email du salarié que nous pouvons considérer comme l’identifiant unique sur l’ensemble des outils (pas de comptes génériques dans les applications)

Créer un serveur lié

Je vous renvoie à cet article de Microsoft pour tous les détails relatifs aux serveurs liés et notamment la commande sp_addlinkedserver.

La première chose que nous allons faire est de créer notre serveur lié à Active Directory en utilisant le code ci-dessous (NB : Pour accéder à certains champs – attributs AD – « sensibles » il faut que le compte d’accès à AD utilisé pour la définition du serveur lié soit un compte d’administration) :

Commande Serveur Lié

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N’ADSI’, @srvproduct=N’Active Directory Service Interfaces’, @provider=N’ADSDSOObject’, @datasrc=N’adsdatasource’
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’ADSI’,@useself=N’False’,@locallogin=NULL,@rmtuser=N’domaine\compte’,@rmtpassword=’** Mettre le mot de passe **’
GO
EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’collation compatible’,  @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’data access’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’dist’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’pub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’rpc’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’rpc out’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’sub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’connect timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’collation name’, @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’lazy schema validation’,  @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’query timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’use remote collation’,  @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’remote proc transaction promotion’, @optvalue=N’true’ GO

Assurez-vous de remplacer les variables @rmtuser et @rmtpassword par un identifiant et un mot de passe ayant accès à votre Active Directory (et les droits d’administration en fonction des champs que vous souhaitez extraire).

Chaine LDAP

Une fois le serveur lié créé, nous pouvons créer notre requête qui nous permettra d’extraire des informations depuis Active Directory ainsi que des différentes BDD applicatives concernées.

Avant toute chose il faut connaitre les informations relatives au serveur AD ainsi qu’au domaine (avec son extension) ceci permet de définir la chaine LDAP de connexion qui aura la forme suivante :

(Pour notre exemple) LDAP://controleur_de_domaine/DC=Domaine,DC=extension

La chaine pourrait également comporter une OU (unité d’organisation) ce qui permet de mieux cibler les objets AD qui nous intéressent dans ce cas la chaine serait : LDAP://controleur_de_domaine/OU=Mon_OU_ActiveDIrectory,DC=Domaine,DC=extension

Requête SQL d’interrogation d’Active Directory et de plusieurs bases de données applicatives

SELECT distinct UTILISATEURS_CRM.MON_CHAMP, UTILISATEURS_ERP.MON_CHAMP, tblADSI.* FROM OpenQuery
(
ADSI,
‘SELECT title, displayName, telephoneNumber, sAMAccountName, mail, mobile, department, givenname, whencreated
FROM  »LDAP://NOM_DU_SERVEUR_AD/DC=DOMAINE,DC=EXTENSION »
WHERE objectClass =  »User »
‘) AS tblADSI
left outer join BASE1.dbo.TABLE_UTILISATEURS AS UTILISATEURS_CRM on tblADSI.mail = UTILISATEURS_CRM.CHAMP_CONTENANT_LE_MAIL
left outer join BASE2.dbo.TABLE_UTILISATEURS AS UTILISATERS_ERP on tblADSI.mail COLLATE DATABASE_DEFAULT = UTILISATEURS_ERP.CHAMP_CONTENANT_LE_MAIL
WHERE telephonenumber IS NOT NULL
group by UTILISATEURS_CRM.MON_CHAMP, UTILISATEURS_ERP.MON_CHAMP, title, displayName, telephoneNumber, sAMAccountName, mail, mobile, department, givenname, whencreated
ORDER BY le_champ_de_votre_choix