Connecter Power BI à SQL Server via la passerelle de données

Brancher Power BI sur un fichier Excel est trivial. Le brancher sur une base SQL Server qui vit dans la salle serveur de l’entreprise, et faire en sorte que les rapports publiés se rafraîchissent tout seuls chaque nuit, l’est beaucoup moins. Le maillon qui rend cela possible s’appelle la passerelle de données locale (on-premises data gateway). C’est un petit service installé sur une machine de votre réseau, qui sert de pont sécurisé entre le service Power BI dans le cloud et vos bases internes. Ce tutoriel détaille toute la chaîne, de la première connexion jusqu’à l’actualisation planifiée.

Ce tutoriel appartient à une série sur Power BI et Microsoft Fabric ; le guide de référence Power BI et Microsoft Fabric situe la passerelle dans l’architecture globale. On suppose que vous savez déjà importer et nettoyer des données dans Power BI Desktop.

Prérequis

• Power BI Desktop installé, et un compte Power BI (le service en ligne).
• Une instance SQL Server accessible depuis le réseau de l’entreprise (locale ou sur une machine virtuelle).
• Une machine Windows toujours allumée (serveur ou poste dédié) pour héberger la passerelle, avec accès réseau à SQL Server et sortie Internet.
• Niveau : intermédiaire. Quelques notions de réseau et de droits SQL aident.
• Temps estimé : 60 à 90 minutes.

Étape 1 — Se connecter à SQL Server depuis Power BI Desktop

Avant toute histoire de passerelle, il faut établir la connexion dans l’outil de conception. Cette première connexion sert à construire le rapport ; la passerelle n’interviendra que plus tard, pour l’actualisation automatique une fois le rapport publié.

Dans Power BI Desktop, cliquez sur Obtenir les données > SQL Server. Renseignez le nom du serveur (par exemple SRV-SQL\PROD pour une instance nommée) et, optionnellement, la base de données. Le point décisif arrive ensuite : le choix entre Importer et DirectQuery.

En mode Importer, Power BI copie les données dans le modèle : les rapports sont rapides, mais les données datent de la dernière actualisation. En mode DirectQuery, chaque interaction interroge SQL Server en direct : les données sont toujours fraîches, mais les performances dépendent de la base. Pour une PME avec des volumes modérés, le mode Importer combiné à une actualisation nocturne est presque toujours le meilleur compromis. Après validation, choisissez l’authentification (Windows ou SQL Server) ; vous voyez alors la liste des tables et pouvez commencer à modéliser.

Étape 2 — Comprendre pourquoi une passerelle est nécessaire

Tant que vous travaillez dans Power BI Desktop sur votre poste, tout fonctionne car votre machine atteint directement SQL Server. Le problème surgit après publication : le service Power BI, hébergé dans le cloud Microsoft, n’a aucun moyen d’atteindre une base qui vit derrière le pare-feu de l’entreprise. Sans pont, l’actualisation planifiée échoue avec un message indiquant qu’aucune passerelle n’est disponible.

La passerelle résout cela de façon élégante et sûre. Elle s’installe sur une machine interne qui, elle, voit à la fois SQL Server (réseau local) et Internet (sortie). Quand le service a besoin de rafraîchir un rapport, il envoie une demande chiffrée que la passerelle récupère, exécute contre la base interne, puis renvoie. À aucun moment la base n’est exposée directement sur Internet : c’est la passerelle qui initie toutes les connexions vers l’extérieur.

Étape 3 — Choisir entre mode standard et mode personnel

La passerelle existe en deux variantes, et choisir la bonne dès le départ évite de tout réinstaller. Le mode standard (parfois appelé mode entreprise) est un service partagé : plusieurs personnes peuvent l’utiliser, il prend en charge Power BI mais aussi Power Apps, Power Automate et Azure Analysis Services, et il tourne en tant que service Windows indépendant de la session utilisateur. Le mode personnel ne sert qu’à Power BI, qu’à une seule personne, et ne convient qu’à un usage individuel de test.

Pour tout déploiement destiné à durer, choisissez le mode standard. Il s’installe sur une machine serveur, survit aux déconnexions de session, et se gère de façon centralisée. Le mode personnel a sa place uniquement quand un analyste isolé veut rafraîchir ses propres rapports sans dépendre de l’informatique, sur des sources qu’il est seul à utiliser.

Étape 4 — Installer la passerelle standard

L’installation se fait sur la machine interne choisie, pas sur votre poste de conception. Téléchargez l’installateur depuis le site officiel de la passerelle Power BI, puis lancez-le. L’assistant vous demande de choisir le mode : sélectionnez passerelle de données locale (mode standard).

L’installateur vous invite ensuite à vous connecter avec le compte professionnel qui administrera la passerelle. Une recommandation importante de production : utilisez un compte de service dédié plutôt qu’un compte personnel, afin que la passerelle ne tombe pas si la personne quitte l’entreprise. Une fois connecté, donnez un nom explicite à la passerelle (par exemple Passerelle-Siege-01) et définissez une clé de récupération que vous conserverez précieusement : elle est indispensable pour restaurer ou migrer la passerelle ultérieurement. À la fin, l’assistant confirme que la passerelle est « en ligne et prête à être utilisée ».

Étape 5 — Vérifier la connectivité réseau de la passerelle

La passerelle ne fonctionne que si elle peut atteindre, d’un côté votre SQL Server, de l’autre les services Microsoft. Il vaut mieux vérifier ces deux chemins maintenant que de déboguer une actualisation qui échoue plus tard. Côté base, testez depuis la machine de la passerelle que le port SQL répond :

Test-NetConnection -ComputerName SRV-SQL -Port 1433

Cette commande PowerShell tente une connexion TCP sur le port 1433, port par défaut de SQL Server. Si TcpTestSucceeded renvoie True, la passerelle voit bien la base. Sinon, vérifiez le pare-feu et la configuration réseau de SQL Server. Côté sortie, la passerelle initie des connexions sortantes uniquement : aucun port entrant à ouvrir. Elle communique vers *.servicebus.windows.net (le relais Azure) sur les ports sortants TCP 443, 5671, 5672 et la plage 9350 à 9354.

Si votre proxy n’autorise que les ports 80 et 443, vous pouvez forcer la passerelle à dialoguer exclusivement en HTTPS sur le port 443, via l’option correspondante de l’application de la passerelle. Ce réglage simplifie l’ouverture réseau dans les environnements verrouillés, au prix d’une latence légèrement supérieure.

Étape 6 — Publier le rapport dans le service

La passerelle étant en place, il faut maintenant amener votre rapport dans le cloud pour qu’il puisse en bénéficier. Depuis Power BI Desktop, cliquez sur Publier et choisissez l’espace de travail de destination. Le rapport et son modèle de données (le jeu de données) sont alors envoyés dans le service.

À ce stade, le rapport est visible en ligne mais son actualisation n’est pas encore automatisée : les données restent figées à la version publiée. C’est l’objet des deux étapes suivantes que de relier ce jeu de données à la passerelle. La gestion fine des espaces de travail et du partage est traitée dans le tutoriel publier et partager dans le service Power BI.

Étape 7 — Déclarer la source de données dans la passerelle

Le service doit savoir avec quelles informations d’identification se connecter à SQL Server à travers la passerelle. On déclare donc une source de données rattachée à la passerelle, une fois pour toutes. Rendez-vous dans le service Power BI, menu Paramètres > Gérer les connexions et passerelles.

Sélectionnez votre passerelle, ajoutez une nouvelle source de données de type SQL Server, et renseignez exactement le même nom de serveur et la même base que dans Power BI Desktop — la correspondance doit être au caractère près, sinon le service ne reliera pas le jeu de données à cette source. Indiquez enfin la méthode d’authentification et les identifiants qui serviront aux actualisations. Ces identifiants sont stockés chiffrés et ne quittent jamais la passerelle en clair. Un voyant vert confirme que la connexion test a réussi.

Étape 8 — Mapper le jeu de données et planifier l’actualisation

Dernière ligne droite : indiquer au jeu de données qu’il doit passer par cette passerelle, puis définir la fréquence de rafraîchissement. Dans l’espace de travail, ouvrez les Paramètres du jeu de données. La section Connexion de passerelle doit maintenant proposer votre passerelle ; sélectionnez-la et associez la source que vous venez de déclarer.

Déroulez ensuite la section Actualisation planifiée, activez-la, et choisissez les créneaux (par exemple tous les jours à 5 h du matin, avant l’arrivée des équipes). Validez. À partir de maintenant, le service réveille la passerelle aux heures prévues, qui interroge SQL Server et met à jour le modèle. Le signal de réussite : l’historique d’actualisation, accessible depuis les mêmes paramètres, affiche des exécutions « Terminé » avec leur horodatage. La logique complète de l’actualisation, y compris l’incrémentielle pour les gros volumes, est approfondie dans le tutoriel actualisation planifiée et incrémentielle.

Étape 9 — Diagnostiquer une actualisation qui échoue

Quand une actualisation échoue, le message d’erreur de l’historique pointe presque toujours vers l’une de trois causes. Savoir les distinguer fait gagner des heures. Si l’erreur mentionne qu’aucune passerelle n’est disponible, c’est que le mapping de l’étape 8 n’a pas été fait ou que la passerelle est hors ligne — vérifiez son état dans l’application de la passerelle sur la machine hôte.

Si l’erreur évoque des identifiants invalides, reprenez la source de données de l’étape 7 : un mot de passe a peut-être expiré, ou le compte de service a perdu ses droits sur la base. Enfin, si l’erreur parle de délai d’attente ou de connexion refusée, le problème est réseau : rejouez le test Test-NetConnection depuis la machine de la passerelle pour confirmer que SQL Server répond toujours. Cette grille de lecture en trois temps couvre la grande majorité des incidents.

Renforcer la disponibilité avec un cluster de passerelles

Une passerelle installée sur une seule machine crée un point de défaillance unique : si cette machine tombe ou redémarre pour des mises à jour, toutes les actualisations s’arrêtent. Dès que les rapports deviennent critiques pour l’activité, il est sage d’éliminer ce risque en regroupant plusieurs passerelles en cluster à haute disponibilité.

Le principe est simple à mettre en œuvre. Lors de l’installation d’une seconde passerelle sur une autre machine, l’assistant propose de l’ajouter à un cluster existant plutôt que d’en créer une nouvelle : vous indiquez alors la passerelle principale et sa clé de récupération. Les deux passerelles forment dès lors un groupe : le service distribue les demandes d’actualisation entre les membres en ligne, et si l’un tombe, l’autre prend automatiquement le relais. Pour la PME, deux machines suffisent à transformer une chaîne fragile en un dispositif résilient. Veillez simplement à ce que toutes les passerelles du cluster aient accès aux mêmes sources de données et utilisent la même version du logiciel, que Microsoft met à jour mensuellement.

Erreurs fréquentes

Tutoriels liés

• Publier et partager dans le service Power BI — l’étape qui précède la mise en place de l’actualisation.
• Actualisation planifiée et incrémentielle — pour optimiser les rafraîchissements volumineux.
• Importer et nettoyer avec Power Query — préparer les données avant la connexion.

Références

• Documentation « On-premises data gateway » (Microsoft Learn) : installation, modes standard et personnel.
• « Adjust communication settings for the on-premises data gateway » (Microsoft Learn) : ports sortants et mode HTTPS.
• Documentation du connecteur SQL Server pour Power BI.

Questions fréquentes

La passerelle expose-t-elle ma base sur Internet ?
Non. Toutes les connexions sont initiées par la passerelle vers l’extérieur. Aucun port entrant n’est ouvert, et la base reste invisible depuis Internet.

Faut-il une passerelle si ma base est déjà dans le cloud (Azure SQL) ?
Pas nécessairement. Une base accessible publiquement ou via une connexion cloud directe peut être rafraîchie sans passerelle. La passerelle est requise pour les sources qui vivent derrière un pare-feu privé.

Sur quelle machine installer la passerelle ?
Sur une machine Windows toujours allumée, dotée d’un accès réseau à la source et d’une sortie Internet. Un serveur dédié est idéal ; éviter un poste de travail qu’on éteint le soir.

Une seule passerelle suffit-elle pour plusieurs rapports ?
Oui. En mode standard, une passerelle dessert plusieurs jeux de données, plusieurs sources et plusieurs utilisateurs. Pour la robustesse, on peut regrouper plusieurs passerelles en cluster à haute disponibilité.