Configurer la sécurité pour SQL Server copie des journaux de transaction

Article
06/20/2023

Cet article explique comment configurer la sécurité pour SQL Server la copie des journaux de transaction et fournit des informations sur le problème qui peut se produire lorsque vous configurez la sécurité pour SQL Server la copie des journaux de transaction.

Version du produit d’origine : SQL Server
Numéro de la base de connaissances d’origine : 321247

Résumé

Cet article fournit des informations sur la configuration de la sécurité pour la copie des journaux de transaction. Il existe plusieurs problèmes à prendre en compte lorsque vous configurez la sécurité pour SQL Server la copie des journaux de transaction, qui vont du compte de démarrage pour SQL Server au partage réseau où résident les sauvegardes du journal des transactions. Ces problèmes sont décrits dans cet article.

Compte de domaine

Si vous avez placé SQL Server dans un domaine, Microsoft vous recommande d’utiliser un compte de domaine pour démarrer SQL Server services. Vous devez utiliser un compte de domaine si vous envisagez de configurer SQL Server pour qu’il s’exécute en tant que serveur virtuel sous clustering Windows. Un compte de domaine offre l’avantage d’une maintenance minimale en cas de modification du mot de passe. Toutefois, il se peut que vous ne puissiez pas démarrer SQL sous un compte de domaine si SQL Server réside sur un serveur qui se trouve dans un groupe de travail.

Compte de réseau local

Vous pouvez utiliser SQL Server pour démarrer sous un compte réseau créé localement. Dans le cas où un processus de SQL Server nécessite un accès réseau, ce qui est le cas si vous avez configuré SQL Server pour utiliser la copie des journaux de transaction, vous pouvez utiliser la sécurité directe réseau. Avec la sécurité directe, toutes les machines auxquelles SQL Server accèdent doivent avoir le même compte réseau avec le même mot de passe et les autorisations appropriées, configurées localement. En outre, lorsque le processus SQL Server demande des ressources à partir du deuxième ordinateur, la sécurité réseau traditionnelle est contournée si le même compte (sous lequel le service de SQL Server demandeur est démarré) existe avec le même mot de passe. Tant que le compte sur le deuxième ordinateur est configuré avec suffisamment d’autorisations pour effectuer la tâche demandée en appelant SQL Server, la tâche réussit.

Compte système local

Vous pouvez également configurer SQL Server pour démarrer sous le compte Système local. La modification du mot de passe du compte LocalSystem peut entraîner la défaillance de certains services qui sont essentiels pour la stabilité du système. Ce compte est local sur l’ordinateur où il réside, ce qui signifie que le contexte de sécurité utilisé par SQL Server services est local. Comme indiqué dans la section Compte de réseau local, vous ne pouvez pas utiliser la sécurité directe réseau lorsque vous démarrez SQL Server sous le compte LocalSystem, car les mots de passe du compte LocalSystem sur différents ordinateurs sont différents. Le démarrage de SQL Server sous ce compte lorsque l’accès aux ressources réseau est requis entraîne probablement l’échec de l’exécution des tâches.

Pour plus d’informations sur les droits minimaux requis pour qu’un compte réseau démarre et exécute correctement SQL Server et SQL Server Agent services, consultez Configuration des comptes de service Windows.

Présentation du modèle de sécurité SQL Server

Pour bien comprendre les implications en matière de sécurité, il est important de comprendre le modèle de sécurité implémenté par Microsoft dans SQL Server 2000. Lorsque vous créez une connexion, elle est ajoutée à la syslogins table dans la base de données MASTER. Pour chaque base de données à laquelle cette connexion nouvellement ajoutée est fournie, elle est ajoutée à la sysusers table de cette base de données. Le mappage entre syslogins la table et sysusers la table se trouve sur le champ SID.

Si une base de données utilisateur est déplacée vers un autre serveur, les valeurs SID sont transférées à partir du serveur précédent. La sécurité de la base de données s’interrompt lorsque les connexions sur le deuxième serveur ne sont pas créées avec les mêmes valeurs SID ou si la sécurité n’est pas configurée de manière incorrecte en raison de valeurs de SID incompatibles.

Pour plus d’informations, consultez Comment résoudre les problèmes d’autorisation lorsque vous déplacez une base de données entre des serveurs qui exécutent SQL Server.

Exigences de sécurité

Partage de sauvegarde

Configurez le partage réseau configuré pour contenir les sauvegardes du journal des transactions afin de disposer d’autorisations de lecture/modification pour le compte sous lequel les services SQL Server (sur le serveur secondaire configuré pour la copie des journaux de transaction) démarrent.

Le partage réseau configuré pour contenir les sauvegardes du journal des transactions doit être configuré pour disposer d’autorisations de lecture/modification pour le compte sous lequel les services SQL Server, sur le serveur secondaire configuré pour la copie des journaux de transaction, démarrent. Ce partage est accessible par le travail de copie sur le serveur secondaire pour copier les sauvegardes du journal des transactions dans le dossier local sur le serveur secondaire respectif. Le travail de chargement charge ensuite ces sauvegardes à partir du dossier local.
Copie des journaux de transaction inter-domaines

Si les ordinateurs qui exécutent SQL Server sont placés dans un environnement multi-domaine, Microsoft vous recommande de configurer des approbations bidirectionnelle entre tous les domaines impliqués dans la copie des journaux de transaction. Toutefois, si vous ne pouvez pas établir d’approbations entre les domaines, vous pouvez utiliser la sécurité directe réseau pour la copie des journaux de transaction. Reportez-vous à la section de cet article qui décrit l’option de démarrage de compte réseau LocalSystem pour les services liés à SQL Server.
Sélection du mode d’authentification pour se connecter au serveur monitor

Vous pouvez sélectionner l’authentification Authentification Windows ou SQL (par les serveurs principaux et secondaires) pour vous connecter au serveur d’analyse et mettre à jour les tables d’analyse. Vous pouvez le sélectionner lors de la configuration de la copie des journaux de transaction ou une fois que vous avez configuré la copie des journaux de transaction et qu’elle est fonctionnelle. Par défaut, SQL Server utilise Authentification Windows . Toutefois, si vous sélectionnez Authentification SQL, un nouveau log_shipping_monitor_probe de connexion SQL est créé sur les serveurs principal, secondaire et de surveillance s’il n’en existe pas. Si vous sélectionnez Authentification SQL à cet effet, configurez SQL Server pour utiliser l’option SQL et Authentification Windows.

Configuration de la sécurité sur le serveur secondaire pour les bases de données de secours

Si vous configurez la base de données secondaire en mode veille, vous pouvez accéder à cette base de données en lecture seule. En restaurant la base de données secondaire dans ce mode, cela peut fournir un moyen d’exécuter des rapports hors connexion, déchargeant ainsi une partie du travail du système de production. Toutefois, pour que la base de données de secours puisse prendre en charge la fonctionnalité en lecture seule, vous devrez peut-être appliquer les mêmes paramètres de sécurité sur le serveur secondaire. Étant donné que la base de données est en état de secours, vous ne pouvez même pas apporter de modifications aux fins de la configuration de la sécurité. Dans ce cas, vous devez créer toutes les connexions SQL Server avec les mêmes valeurs SID sur le serveur secondaire. Les connexions Windows conservent automatiquement les mêmes SID, car le GUID Windows est globalement unique, même en cas d’utilisation de plusieurs domaines.

Pour plus d’informations sur la création de connexions SQL avec le même SID sur différents serveurs, consultez Comment accorder l’accès aux connexions SQL sur une base de données de secours lorsque l’invité est désactivé dans SQL Server.

Configuration de la sécurité lors de l’exécution d’un changement de rôle

La procédure de changement de rôle pour la copie des journaux de transaction implique la promotion d’un serveur secondaire pour qu’il prenne le relais en tant que serveur principal. Vous pouvez le faire avec ou sans que le serveur principal soit en ligne. Dans le cadre du changement de rôle, jusqu’à quatre procédures stockées sont exécutées. L’une de ces procédures stockées, , sp_resolve_logins permet de corriger les valeurs de SID pour les connexions qui résident dans la base de données de secours juste avant qu’elle ne soit disponible pour une utilisation en tant que base de données primaire.

Dans le cadre de cette procédure stockée, un .bcp fichier de la syslogins table de l’ancien serveur principal est chargé dans une table temporaire. Chaque connexion présente dans cette table temporaire est ensuite comparée à la table de la syslogins base de données MASTER du serveur secondaire et à la sysusers table de la base de données secondaire. Pour chaque connexion dans la table temporaire qui a une connexion portant le même nom dans la syslogins table et le même SID que celui de la sysusers table de la base de données secondaire, le SID est corrigé (dans la base de données secondaire) en utilisant sp_change_users_login pour correspondre à celui qui se trouve dans la syslogins table.

La configuration de la sécurité à l’aide de cette procédure stockée nécessite les éléments suivants :

Les connexions SQL doivent déjà être créées sur le serveur secondaire. Pour ce faire, utilisez la tâche DTS de transfert de connexions qui est expliquée dans SQL Server rubrique de la documentation en ligne : Comment configurer et effectuer la modification du rôle de copie des journaux de transaction.
Vous devez fournir un .bcp fichier de la syslogins table à partir du serveur principal. Ce fichier doit être à jour, car un fichier obsolète peut entraîner sp_resolve_logins l’échec de la correction des connexions.

Vous devez remplir les trois conditions suivantes avant sp_resolve_logins de pouvoir réellement corriger les connexions dans la base de données secondaire :

Le nom de connexion du .bcp fichier de la syslogins table doit correspondre au nom de la syslogins table du serveur principal.
La valeur SID doit correspondre entre le fichier de connexion .bcp et la sysusers table dans la base de données secondaire.
La valeur SID de la base de données secondaire doit être différente de la valeur SID dans la syslogins table de la base de données MASTER sur le serveur secondaire.

Si vous créez des connexions SQL Server comme décrit dans Q303722, il n’est pas nécessaire d’exécuter cette procédure stockée, car toutes les connexions sont déjà présentées avec la même valeur SID dans la table (dans la syslogins base de données MASTER sur le serveur secondaire) et la sysusers table (dans la base de données secondaire).

Foire aux questions

Question : La copie des journaux de transaction propage-t-elle automatiquement les modifications liées à la sécurité sur un serveur secondaire ?

Réponse : oui. Étant donné que toutes les modifications apportées aux tables système sont des opérations journalisées, celles-ci sont propagées automatiquement au serveur secondaire (ou aux serveurs).
Question : Pouvez-vous avoir deux connexions sur le serveur secondaire avec le même SID ? J’en ai besoin, car j’utilise le même ordinateur SQL Server pour gérer plusieurs bases de données de secours à partir de plusieurs serveurs.

Réponse : non. SQL Server modèle de sécurité ne permet pas d’avoir deux connexions avec le même SID. En cas de conflit sur le SID lors de l’utilisation de la copie des journaux de transaction avec plusieurs serveurs, la seule façon de corriger ce problème consiste à supprimer la connexion en conflit sur le serveur principal, puis à la créer avec un SID qui n’existe pas sur le serveur secondaire.