PI Services

Le blog des collaborateurs de PI Services

SQL Server : BULK INSERT – Msg 4861 - Operating system error code 5

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  

image

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

image

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 !

image 

Le message complet de l’erreur peut être:

 image

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

    image

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 

imageimage

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 

image

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.