Problématique
Dans certains cas nous somme amené à utiliser l’instruction BULK INSERT pour injecter dans une table des enregistrements qui proviennent d’un fichier texte stocké sur un partage réseau
Supposons maintenant que nous disposons de 3 serveurs :
- 1 contrôleur de domaine DC01
- 2 serveurs SQL 2008 R2 SQL01 et SQL02, le serveur SQL01
Dans le premier scénario nous allons exécuter une requête BULK INSERT sur le serveur SQL01 qui injectera dans la table #TEST les lignes d’un fichier texte nommé ListCustomers.txt accessible via le chemin UNC \\SQL01\FILES\Input
Dans ce premier scénario la requête aboutit sans aucun problème
Dans le deuxième scénario nous allons exécuter une requête BULK INSERT sur le serveur SQL01 mais depuis le serveur SQL02 qui injectera dans la table #TEST les lignes d’un fichier texte nommé ListCustomers.txt accessible via le chemin UNC \\SQL01\FILES\Input
Dans ce deuxième scénario la requête aboutit sans aucun problème
Dans le troisième scénario nous allons exécuter une requête BULK INSERT sur le serveur SQL01 mais depuis le serveur SQL02 qui injectera dans la table #TEST les lignes d’un fichier texte nommé ListCustomers.txt accessible via le chemin UNC \\SQL02\FILES\Input
Dans ce troisième scénario une erreur est générée, en effet l’instruction BULK INSERT n’arrive pas à ouvrir le fichier source pour y lire les enregistrements !
Le message complet de l’erreur peut être:
Explication
L’erreur enregistrée dans le scénario 3 est dû au fait que lorsqu’un utilisateur se connecte à SQL Server avec l’authentification Windows il n’est autorisé à lire que les fichiers accessibles par le biais de son compte utilisateurs, quelque soit le profil de sécurité du processus SQL Server.
Résolution
Pour remédier à ce problème on pourra procéder de deux manières, soit qu’on utilise une connexion SQL Server au lieu de l’authentification Windows soit configurer Windows pour activer la délégation des comptes de sécurité.
Pour la configuration de Windows il faudra procéder comme suit :
Supposons que le compte de service utilisé pour le démarrage du service SQL sur le serveur SQL01 est SvcSQL01 alors il faudra créer un SPN sur ce compte en utilisant la commande suivante :
SETSPN –A MSSQLSvc/SQL01.domain.lan:1433 DOMAIN\SvcSQL01
Pour vérifier que l’SPN a été bien créé on peut exécuter la commande suivante :
SETSPN –L DOMAIN\SvcSQL01
Une fois le nom principal de service créé il faudra approuver le compte SvcSQL01 à la délégation et le compte de la machine SQL01 à la délégation, ceci se fait via la console Utilisateurs et Ordinateurs Active Directory
Attention : l’onglet “Delegation” n’apparaitra sur le compte SvcSQL01 qu’après avoir créé le nom principal de servie avec la commande SETSPN
On peut vérifier maintenant que la requête ne gènère plus d’erreur comme le montre la figure suivante.
l’exécution de la requête
SELECT session_id, auth_scheme FROM sys.dm_exec_connections nous permet de confirmer l’authentification utilisé pour nitre session est bien KERBEROS
Remarque
Nous avons pu remarquer que si nous créons le nom principal de service avec le nom de l’instance et non le numéro de port d’une instance nommée l’erreur 4861 persiste, il a fallut fixer le port utilisé par l’instance et l’utiliser pour la configuration du nom principal du service lié au compte de service SQL.