Travaux via l'Agent SQL Serveur en echec

**crashyear** · 27/10/2015, 14h57

Bonjour,

J'ai un travail qui s'exécute durant la journée tous les 1/4 d'heure et qui actuellement tombe en erreur dans la seconde étape avec le message suivant :
Exécuté en tant qu''utilisateur*: MonDomaine\SQL_Agent. Échec de la connexion. La connexion provient d'un domaine non approuvé et ne peut pas être utilisée avec l'authentification Windows. [SQLSTATE 28000] (erreur 18452). L'étape a échoué.

J'ai le même Job sur une autre instance qui fonctionne très bien.
J'ai comparé les droits de mon utilisateur "SQL_Agent" entre les 2 instances : RAS.

Remarques : l'étape 1 du job retourne le message suivant Exécuté en tant qu''utilisateur*: MonDomaine\SQL_Agent. L'étape a réussi.... ?!?!

**SQLpro** · 29/10/2015, 18h45

sous quels comptes tournent SQL Server et l'agent SQL?
sous quel sécurité est lancé votre travail ? et ses étapes ?

A +

**crashyear** · 30/10/2015, 07h51

sous quels comptes tournent SQL Server et l'agent SQL :
- SQL Serveur : SQL_Engine
- L'agent SQL : SQL_Agent

sous quel sécurité est lancé votre travail et ses étapes :
Pouvez-vous préciser la question svp ?
Le propriétaire du travail est un de nos développeurs, mais j'ai essayé de changer et d'utiliser SQL_Agent, cela ne change rien.
J'ai également tenté d'exécuter moi même ce job et j'obtiens la même erreur : ID de message SQL 18452 (uniquement sur l'étape 2)
L'étape 1 du job retourne : Exécuté en tant qu'utilisateur*: MonDomaine\SQL_Agent. L'étape a réussi.

**rudib** · 30/10/2015, 08h56

Peux-tu préciser ce que fait l'étape 2, par exemple en copiant ici son contenu ?

**crashyear** · 30/10/2015, 09h07

L'étape 2 exécute une procédure stockée d'une des bases de mon instance par défaut :
exec [dbo].[sp_**********] 'Param1', 'Param2', 'H:\MonRepertoire'

L'étape 1 fait la même chose mais avec une autre procédure stockée de la même base de données :
exec [dbo].[sp_**********] @Param = 0

**rudib** · 30/10/2015, 09h27

Comment la procédure de l'étape 2 utilise-t-elle le paramètre 'H:\MonRepertoire' ?
Peux-tu copier son code ici si ce n'est pas un secret industriel ou du code SQL écrit par le ministère de la défense ?

**crashyear** · 30/10/2015, 11h14

Le code SQL exécuté est un "simple" exec BACKUP DATABASE MaBase to DISK='H:\MonRepertoire'.
Voilà dans la PS finale le code en clair :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

EXEC ('Backup ' + @BackupType + ' [' + @DBName + '] to DISK=N''' + @BackupLocation + '''')

Après selon un autre paramètre il y a l'option WITH NO_COMPRESSION ou WITH COMPRESSION qui est ajouté.

**rudib** · 30/10/2015, 11h25

MonDomaine\SQL_Engine a la permission d'écrire dans H:\MonRepertoire ?
Si tu lances le même backup à la main, est-ce que ça fonctionne ? Je parle de la commande backup qui est générée et exécutée, pas l'appel de la procédure.

**crashyear** · 30/10/2015, 12h06

Bien vu !
J'ai exécuté l'ordre SQL manuellement et voilà le résultat :
Impossible d'ouvrir l'unité de sauvegarde 'H:\MonRepertoire'. Erreur 5(Accès refusé.) du système d'exploitation.
J'ai ajouté l'utilisateur SQL_Engine directement sur le disque H: en contrôle total pour faire un test mais j'obtiens de nouveau l'erreur, faut-il redémarrer mon instance pour que ce soit pris en compte ?

**rudib** · 30/10/2015, 12h29

Non, pas besoin. Vérifie bien que les permissions sont attribuées dans ce répertoire

au passage : tu fais une sauvegarde complète de ta base toutes les quinze minutes ?

**crashyear** · 30/10/2015, 12h55

Mon Utilisateur SQL_Engine a désormais les droits sur le répertoire, mais lorsque je relance la commande manuelle j'obtiens toujours la même erreur :
Impossible d'ouvrir l'unité de sauvegarde 'H:\MonRepertoire'. Erreur 5(Accès refusé.) du système d'exploitation.

Et apparemment oui, une sauvegarde est lancée toutes les 15min pour cette base de données (en plus de la sauvegarde quotidienne faite par le plan de maintenance).

**rudib** · 30/10/2015, 13h06

1/ cette sauvegarde complète est-elle nécessaire ?
2/ le répertoire H:\MonRepertoire existe-t-il ?
3/ dans ta syntaxe, il manque le nom du fichier : BACKUP DATABASE MaBase to DISK='H:\MonRepertoire' n'est pas une syntaxe valide, il faut ajouter le nom du fichier dans le répertoire, par exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

BACKUP DATABASE MaBase to DISK='H:\MonRepertoire\monfichier.bak'

4/ si ce fichier existe déjà, est-il en lecture seule ?
5/ y a-t-il une suite à la commande ? Si tu crées une sauvegarde complète dans le même fichier, les sauvegardes vont s'additionner dans le fichier jusqu'à faire exploser ton disque. Y a-t-il un WITH FORMAT ou WITH INIT ensuite dans la commande ?
6/ est-ce que la personne qui a fait ce script connaît un peu SQL Server (je pense deviner la réponse à cette question

)

**crashyear** · 30/10/2015, 13h42

1/ cette sauvegarde complète est-elle nécessaire ?
Je n'en suis pas sur, d'autant plus qu'on est en DEV, je dois voir le developpeur prochainement à ce sujet.

2/ le répertoire H:\MonRepertoire existe-t-il ?
Oui

3/ dans ta syntaxe, il manque le nom du fichier : BACKUP DATABASE MaBase to DISK='H:\MonRepertoire' n'est pas une syntaxe valide, il faut ajouter le nom du fichier dans le répertoire, par
4/ si ce fichier existe déjà, est-il en lecture seule ?
5/ y a-t-il une suite à la commande ? Si tu crées une sauvegarde complète dans le même fichier, les sauvegardes vont s'additionner dans le fichier jusqu'à faire exploser ton disque. Y a-t-il un WITH FORMAT ou WITH INIT ensuite dans la commande ?

Mea culpa, j'ai oublié de l'écrire. La procédure stockée génère un nom de fichier dynamique avec "Nom du serveur", "Nom de la BDD" et "N° de sequence", donc c'est bon de ce coté là, la commande fonctionne correctement et il y l'option "WITH COMPRESSION".

Donc, maintenant quand mon job se relance, il devrait fonctionner... Eh bah non... J'ai toujours la même erreur :
Exécuté en tant qu''utilisateur*: MonDomaine\SQL_Agent. Échec de la connexion. La connexion provient d'un domaine non approuvé et ne peut pas être utilisée avec l'authentification Windows. [SQLSTATE 28000] (erreur 18452). L'étape a échoué.

**rudib** · 30/10/2015, 13h51

et si tu exécutes toi-même la procédure

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

exec [dbo].[sp_**********] 'Param1', 'Param2', 'H:\MonRepertoire'

?

**crashyear** · 30/10/2015, 13h56

J'obtiens le même message d'erreur :
Échec de la connexion. La connexion provient d'un domaine non approuvé et ne peut pas être utilisée avec l'authentification Windows.

**rudib** · 30/10/2015, 14h34

ok, désolé, je sèche. si tu voulais copier la procédure ici ça nous donnerait peut-être une piste. Dernière chose : la procédure est-elle créée avec un EXECUTE AS ?

**crashyear** · 02/11/2015, 13h31

La procédure stockée exécutée est celle-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
ALTER PROCEDURE [dbo].[sp_BackupBizTalkFull] @seq nvarchar (128), @path nvarchar (3000), @BackupLocation nvarchar(4000) OUTPUT, @bCompression BIT = 0
AS
begin
 declare @DBName nvarchar(128), @ServerName nvarchar(256)
 
 select @DBName = db_name(), @ServerName = replace( cast( isnull( serverproperty('servername'), '' ) as nvarchar ), '\', '_' ) /* this will be a file path */
 
 if right( @path, 1 ) = '\'
  SET @BackupLocation=@path + @ServerName + N'_' + @DBName + N'_Full_' + @seq + N'.bak'
 else
  SET @BackupLocation=@path + N'\' + @ServerName + N'_' + @DBName + N'_Full_' + @seq + N'.bak'
 
 exec sp_BackupBizTalk @DBName, @BackupLocation, @bCompression, N'DATABASE'
 
 if @@ERROR <> 0
  goto FAILED
 
 return 0
 
FAILED:
 return -1
 
end

Qui donc fait appel à la PS suivante sp_BackupBizTalk :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
ALTER PROCEDURE [dbo].[sp_BackupBizTalk]
@DBName sysname,
@BackupLocation nvarchar(4000),
@bCompression bit,
@BackupType nvarchar(20)
AS
 declare @Version int
 exec [dbo].[sp_GetServerVersion] @Version output
 if (@Version <=9) -- SQL 2005 or earlier
 BEGIN
  declare @localized_string_compression_option_not_available nvarchar(128)
  set @localized_string_compression_option_not_available = N'Backup compression option is not available on this SQL version. Ignoring compression option'
  if (@bCompression = 1) RAISERROR(@localized_string_compression_option_not_available, 10, 1) WITH LOG
 
  EXEC ('Backup ' + @BackupType + ' [' + @DBName + '] to DISK=N''' + @BackupLocation + '''')
 END
 else -- SQL 2008 or later
 BEGIN
  if (@bCompression = 0)
   EXEC ('Backup ' + @BackupType + ' [' + @DBName + '] to DISK=N''' + @BackupLocation + ''' WITH NO_COMPRESSION')
  else
   EXEC ('
     BEGIN TRY
      Backup ' + @BackupType + ' [' + @DBName + '] to DISK=N''' + @BackupLocation + ''' WITH COMPRESSION
     END TRY
     BEGIN CATCH
      if (@@ERROR = 3013)
       Backup ' + @BackupType + ' [' + @DBName + '] to DISK=N''' + @BackupLocation + ''' WITH NO_COMPRESSION
     END CATCH
      ')
 END

**SQLpro** · 02/11/2015, 19h21

H est-il une unité mappée sur un serveur externe ? Si oui, cela ne peut jamais fonctionner. Utilisez un chemin UNC.

A +

Pour en savoir plus sur SQL Server :
Nom : Couverture livre SQL server Eyrolles.jpg
Affichages : 714
Taille : 105,0 Ko