Error: 18456, Severity: 14, State: 11 et State: 12 (login failed) sur SQL Server 2005

Vous le savez sans doute déjà, un login failed SQL Server n’envoie quasiment aucun détail sur les raisons de l’échec au client, dans un but de sécurité du système (le client en question pourrait être un hacker en train de tester le système).

Par contre, le DBA a accès à l’erreur côté serveur qui fournit un peu plus d’informations sur les causes exactes, notamment grâce au ‘State’ (ou état) de l’erreur 18456.

https://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx discute de la plupart des scénarios.

Vous noterez que 2 valeurs sortent du lot :

Logon       Error: 18456, Severity: 14, State: 11.
Logon       Login failed for user 'domain\user'. [CLIENT: xxx.xxx.xx.xx]

et

Logon       Error: 18456, Severity: 14, State: 12.
Logon       Login failed for user toto. [CLIENT: xxx.xxxxxx]

Le blog et ses commentaires reflètent en effet une multitude de causes possibles, regroupées sous le terme générique ‘Valid login but server access failure’.

J’ai pu récemment découvrir une nouvelle cause à cette erreur que je n’ai vu décrite nulle part : la tentative de connexion à un ENDPOINT TCP sur lequel l’utilisateur n’a pas le droit (alors même qu’il a les permissions d’accès au serveur SQL). Le state 11 correspondant à une tentative de connexion ‘trusted’ (utilisant l’authentification Windows) et le 12, à une connexion ’non-trusted’ (utilisant l’authentification SQL login/password).

L’utilisation des ENDPOINTS TCP reste une fonctionnalité avancée, et ceux qui se plongent dans ces détails ont souvent assez d’expérience sur la connectivité SQL pour retomber sur leur pattes, mais un scénario vient à l’esprit qui pourrait facilement poser problème :

Si pour une raison x ou y (“tiens, je vais tester…”) vous créez un ENDPOINT TCP, cela révoque automatiquement les droits de connection de ‘public’ sur le Endpoint [TSQL Default TCP].

C’est un élément suffisamment important pour que SQL Server vous avertisse en retour de l’exécution :

Creation of a TSQL endpoint will result in the revocation of any 'Public' connect permissions on the 'TSQL Default TCP' endpoint. If 'Public' access is desired on this endpoint, reapply this permission using 'GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]'.

Mais si vous ratez/oubliez/ignorez ce message, peut-être en croyant que la suppression du endpoint de test rétablira la configuration d’origine (ce n’est pas le cas), il y a toutes les chances que des erreurs “Error: 18456, Severity: 14, State: 11.” commencent à pulluler dans vos ERRORLOGs, pour des utilisateurs qui pourtant ont tous les droits de login nécessaires et n’avaient aucun problème d’accès quelques minutes auparavant. Seuls les membres de Sysadmins seront épargnés, car ce groupe shunte la majorité du code de sécurité.

La révocation des droits sur le 'TSQL Default TCP’ pour public va en effet empêcher tout personne non sysadmin de se connecter au portTCP de l’instance. Ce droit est complètement indépendant des permissions sur les objects SQL du serveur : je peux être DBOwner de 3 bases et ne pas avoir le droit de me connecter au port TCP. 99% des SQL Server (ceux sur lesquels personne n’a joué avec les ENDPOINTS :) ) fonctionnent avec le droit par défaut pour public et donc leurs utilisateurs ne réalisent probablement pas qu’il existe ce premier niveau de vérification de permission. C’est aussi la raison pour laquelle le réflexe de vérifier les droits sur les endpoints n’est pas vraiment répandu, et le message d’erreur ne met pas réellement sur la voie (à part peut-être une découverte de ce post par une recherche web !).

Récapitulons :

Les permissions de login de ‘toto’ n’ont pas été modifiées, pourtant cet utilisateur a bien été confronté à un login failed…

Attention, les défauts de permission d’accès à un ENDPOINT TCP ne constituent en aucun cas la seuleexplication aux état 11 et 12, qui sont des valeurs qui vont couvrir la plupart des scénarios pour lesquels aucune erreur plus spécifique n’a été codée.

Guillaume Fourrat