Discussion :

[janlouk]

Bonjour à tous,

Je dois pour la première fois, désactiver le compte SA sur toutes nos instances.

J'ai des réponses ici : https://www.developpez.net/forums/d1...vation-compte/ : mais j'ai besoin de précisions et désolé si je me répète sur la précédente discussion.

Mais je vais en profiter pour revoir tous les owners et les modifier si nécessaire sur 50 instances (Prod et NonProd)

Je commence par l'environnement DEV bien sûr.

Sur 82 DB, il y en a :
34 avec le compte SA
18 sans owner, le champ est vide
25 avec comme owner un compte AD
5 avec un compte SQL

Pour les jobs, il y en a 20 :
3 avec le compte SA
7 sans owner, le champ est vide
6 avec comme owner un compte AD
4 avec un compte SQL

Pour les jobs, dans le post, Nicolas dit :

Code :

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

Ceci est du au fait que le propriétaire d'un job indique seulement qui peut l'exécuter, Un job peut ne pas avoir de propriétaire, et dans ce cas, seul un utilisateur membre du rôle de serveur sysadmin peut l'exécuter.

Si je comprends bien donc, si je mets SA comme owner des jobs et que je le désactive ensuite, cela veut dire que seul un membre du rôle de sysadmin pourra exécuter le job manuellement. Mais s'il y a un schedule, ça ça ne change rien, il sera exécuté normalement?
Et si je mets un compte AD, seul ce compte ou un sysadmin pourra exécuter ce job?


Pour les DB, Nicolas dit :

Code :

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

1
2
3
4
5
6
Dans le cas des bases de données non-contenues, le fait que le login sa soit désactivé empêche simplement de se connecter à l'instance sous ce nom de login.
Cela n'empêche pas un membre du rôle se serveur sysadmin de l'utiliser une fois qu'il est authentifié et qu'il a été établi qu'il est bien membre de ce rôle de serveur.
 
L'avantage d'utiliser ce nom de login, bien qu'il soit désactivé, est qu'il est présent sur toute instance SQL Server.
Dès lors, un déplacement de base de données ou une copie de job se fait sans encombre.
Par ailleurs, si le propriétaire de la base de données est un groupe AD, ou pire, un utilisateur AD, le jour où l'on modifie le groupe ou désactive le compte d'utilisateur AD ... patatra !

Donc idéalement, je devrais mettre SA comme owner de toutes les DB même si après je le désactive pour éviter le problème avec un groupe ou user AD qu'on désactiverait, cela n'a pas de conséquences?
Mais alors pourquoi c'est différent si on met un compte ou groupe AD et qu'on le désactive dans l'AD, par rapport à SA qu'on désactive?

Donc si je résume, si je désactive SA:

Pour les jobs : Mettre SA comme owner sauf si un compte "extérieur" doit l'exécuter manuellement?
Et quelle est la conséquence si je ne mets pas d'owner à un job? Que je laisse le champ vide?
Pour les DB : Mettre SA comme owner n'a pas de conséquences sur les applications qui se connectent à ces DB (mais qui n'utilisent pas le compte SA bien sûr!), et donc pour éviter le problème avec un groupe/compte AD, c'est mieux de mettre SA?
Et quelle est la conséquence si je ne mets pas d'owner à une DB? Que je laisse le champ vide?

Si j'ai oublié des problèmes à la désactivation du compte SA, je suis à votre écoute.

[hmira]

Bonjour janlouk,

Avoir 'sa' comme propriétaire d'une base de données est incontestablement une mauvaise pratique en terme de sécurité. Ceci dit, dans la pratique, il n'est pas rare que les bases de données aient comme propriétaire 'sa', ou bien pire encore, l'utilisateur connecté (login autre que 'sa' ) au moment de la création de la base, qui sont généralement aussi à 99.99 % des cas, membre du rôle sysadmin, qu'il s'agisse de compte AD ou autre compte SQL !!!

En résume, voilà ce que je peux te dire ce sujet, sans donner d'explications détaillées :

1 - Eviter à tout prix que le propriétaire de la base soit membre du rôle 'sysadmin'. Donc cela exclue 'sa' et autres principaux de niveau Serveur membres du rôle 'sysadmin'

2 - La situation devient carrément critique en terme de sécurité lorsque la base est marquée "digne de confiance" au niveau de l'instance et que le propriétaire de la dite base est membres du rôle 'sysadmin' !!!

3 - Une solution consiste à créer un "login" spécial, destiné à être le propriétaire de la base. Ce "login" ne sera doté d'aucune autorisation particulière, sera désactivé (comme pour 'sa'), et même avec un refus explicite de connexion (DENY CONNECT SQL).

4 - Généralement, les DBA, commencent à s'"intéresser" au propriétaire de la base lorsque, sous SSMS, ils obtiennent des messages erreurs en voulant consulter les propriétés de la base de données, parce que, tout simplement, le propriétaire de la base n'existe plus !
Attention : le nom du propriétaire peut d'apparence afficher un nom correct, en consultant les vues système, mais "ID du principal" au niveau de la base de données peut être différent et ne pas correspondre à l'identificateur de sécurité du propriétaire externe de la base de données, tel qu'il est enregistré sur le serveur (dans la base 'master'). Cela arrive généralement après restauration de la base depuis un autre Serveur SQL et où le propriétaire de la base portait le même nom sur l'autre serveur, d'où le confusion !

5 - Autre scénario classique où les DBA, s'"intéressent" au propriétaire de la base, c'est lorsqu'ils mettent en œuvre les fonctionnalités du Service Broker et obtiennent une batterie d'erreurs lorsque le propriétaire de la base est incorrect.

A+

[mikedavem]

Hello

3 - Une solution consiste à créer un "login" spécial, destiné à être le propriétaire de la base. Ce "login" ne sera doté d'aucune autorisation particulière, sera désactivé (comme pour 'sa'), et même avec un refus explicite de connexion (DENY CONNECT SQL).

C'est également la recommendation que l'on donne à nos clients lors de nos audits.

Si je comprends bien donc, si je mets SA comme owner des jobs et que je le désactive ensuite, cela veut dire que seul un membre du rôle de sysadmin pourra exécuter le job manuellement. Mais s'il y a un schedule, ça ça ne change rien, il sera exécuté normalement?

Cela veut dire que n'importe qui ayant les droits d'exécuter ce job pourra l'exécuter. De plus comme le propriétaire de ce job est membre du rôle sysadmin alors le contexte d'exécution sera le compte de service l'agent SQL Server (à moins d'avoir configuré un proxy). Le problème avec les jobs SQL Server c'est que le propriétaire peut très vite être volatile car devient propriétaire le dernier qui touche au job. Tu vas régler le problème une fois mais rien ne dit que la situation ne change pas (en fonction de ton contexte bien sûr). Pour ma part je vérifie toujours que le propriétaire d'un job qui n'est pas "sa". On revient à ta problématique de base: si le compte utilisé n'est plus valide (compte de domaine par exemple) alors le job ne s'exécutera plus correctement.

++

[janlouk]

3 - Une solution consiste à créer un "login" spécial, destiné à être le propriétaire de la base. Ce "login" ne sera doté d'aucune autorisation particulière, sera désactivé (comme pour 'sa'), et même avec un refus explicite de connexion (DENY CONNECT SQL)

Bonjour Amid (je ne met pas de H pour être certains que tu sais que je ne prononce pas le H comme un H aspiré :-) )

Ok, alors si je "mélange" les conseils David, Nicolas et les tiens je vais créer un login (le même) sur toutes mes instances, choisir un mot de passe très compliqué, le désactiver + DENY CONNECT SQL.

[janlouk]

Pour ma part je vérifie toujours que le propriétaire d'un job qui n'est pas "sa".

Salut David,

Tu veux plutôt dire que tu vérifies toujours que le propriétaire d'un job N'EST PAS sa?

Sorry mais j'ai un peu plus de mal avec les jobs pour savoir la bonne pratique. En fait, tout dépend du contenu de ce job, ce qu'il fait, où cela ne change rien? Cela doit être du cas par cas ou je peux faire "comme" les DB?
Car j'ai tellement de jobs, dont beaucoup que je ne connais pas car je viens d'arriver, j'aimerais trouver une "solution" générale.
Vous faites quoi dans ce cas? Aussi un login pour les jobs, mais sans le désactiver, avec des droits public simplement?

[mikedavem]

Tu veux plutôt dire que tu vérifies toujours que le propriétaire d'un job N'EST PAS sa?

Oui erreur de ma part désolé .. j'ai oublié mon français ce week-end. J'identifie les propriétaires des jobs qui ne sont pas sa et je me focalise sur les comptes Windows qui peuvent ne peut exister ou être bloqués sur le domaine.

Sorry mais j'ai un peu plus de mal avec les jobs pour savoir la bonne pratique. En fait, tout dépend du contenu de ce job, ce qu'il fait, où cela ne change rien? Cela doit être du cas par cas ou je peux faire "comme" les DB?

Non bien sûr en fonction du contenu, du type de job il faut faire attention car il peut avoir un impact certain sur les permissions des ressources qui doivent être accédées. La tâche que tu mènes n'est pas simple, chronophage et une solution générique n'est malheureusement pas envisageable dans la plupart des cas.

++

[janlouk]

Non bien sûr en fonction du contenu, du type de job il faut faire attention car il peut avoir un impact certain sur les permissions des ressources qui doivent être accédées. La tâche que tu mènes n'est pas simple, chronophage et une solution générique n'est malheureusement pas envisageable dans la plupart des cas.

Bon, c'est bien ce que je craignais, je vais m'y attelé et ça ne va pas être simple.

Merci à tous les deux.

[hmira]

Bonjour Amid (je ne met pas de H pour être certains que tu sais que je ne prononce pas le H comme un H aspiré :-) )

Maintenant que je suis "rassuré" que tu ne prononces pas le 'H', tu peux désormais l'écrire soit "Hamid"

Ok, alors si je "mélange" les conseils David, Nicolas et les tiens je vais créer un login (le même) sur toutes mes instances, choisir un mot de passe très compliqué, le désactiver + DENY CONNECT SQL.

Oui, c'est exactement cela. Il s'agit d'une règle de bonne pratique confirmée et approuvée également par notre expert David (mikedavem).

Une remarque cependant : Le fait de créer un login dépourvu d'autorisation, désactivé, avec un "DENY CONNECT SQL" et de faire en sorte que ce login soit le propriétaire de la base, (c.à.d. appliquer tout simplement la règle de bonne pratique énoncée ci-dessus) peut potentiellement générer des régressions fonctionnelles dans les applications, si, d'aventure, celles-ci mettent en œuvre la "Personnalisation des autorisations avec l'emprunt d'identité", ou utilisent des procédures avec la clause "WITH EXECUTE AS OWNER", etc. Dans ce cas, il te faudra corriger ces erreurs et remédier à ces problèmes de régression. Une solution, parmi d'autres consiste à signer les procédures concernées, par des Certificats.

A toi de voir, mais il serait plus prudent de tester et de qualifier ce changement du propriétaire de la base, le cas échéant, corriger les problèmes, par exemple en signant les procédures par des Certificats, etc., et ce , avant le déploiement en environnement de production.

A+

[janlouk]

Et pour terminer, pour les DB, j'aimerais changer le owner de la DB Model, mais impossible bien sûr...
Dans le cas des DB system, est-ce "grave" que ces DB aient encore SA comme owner?

[hmira]

Oublies les bases systèmes, du moins les 3 bases master, model et tempdb !

La modification du propriétaire de chacune de ces 3 bases système model, master et tempdb est strictement interdire. Pour ces 3 bases systèmes (master, model et tempdb) le propriétaire est toujours 'sa' et on ne peut pas le changer.

En ce qui concerne la base msdb, je crois que c'est possible de changer le propriétaire de la base msdb, mais personnellement je ne me suis jamais aventuré sur ce terrain !
Modifier le propriétaire de msdb, même si techniquement cela est possible, sans maitriser les tenants et les aboutissants, risque fort d'engendrer un tas de problèmes de droits etc.. notamment pour la gestion des tâches planifiées (Jobs), et les Plans de maintenance etc.. Problèmes qui seraient très difficiles à cerner et à corriger.

En conclusion:
Personnellement, je ne m'avise jamais à changer le propriétaire des 4 bases systèmes que sont master, model, tempdb et msdb.

A+

[janlouk]

1 - Eviter à tout prix que le propriétaire de la base soit membre du rôle 'sysadmin'. Donc cela exclue 'sa' et autres principaux de niveau Serveur membres du rôle 'sysadmin'

Bonjour Hamid,

Je reviens sur ce point car j'ai justement rencontré un problème lors du changement d'owner d'une DB pour l'application SCCM qui malheureusement n'existe qu'en Prod...

J'ai dû remettre temporairement SA, même si celui-ci est désactivé, pour que cela fonctionne à nouveau.

Aurais-tu l'amabilité de me dire pourquoi il faut éviter à tout prix que le propriétaire soit sysadmin?

Merci.

[mikedavem]

Tu peux te retrouver dans des cas où il faille activer TRUSTWORTHY par exemple et cela peut ouvrir une brèche de sécurité sur ton serveur (même si le comportement est totalement voulu).
Pour faire simple, si le propriétaire de ta base est sysadmin + TRUSTWORTHY activé + droit d'exécution d'un utilisateur lambda de ta base de données à faire un EXECUTE AS = 'dbo' alors cet utilisateur devient automatiquement sysadmin.

++

[janlouk]

Merci David.

Je me répète peut-être mais finalement qu'est-ce que cela change d'avoir sa désactivé comme owner des db et un login sql avec droit public qui lui aussi est désactivé? Dans les 2 cas, ce sont des comptes désactivés... Et si un hacker arrive à réactiver SA, avoir un login sql sans droit comme owner des db n'y fera rien?

[i.chafai]

Bonjour,
A la mise en place d'un nouveau serveur chez nous, j'ai créé un login sql avec le role public et j'ai désactivé la connexion pour ce login.
Mais j'ai trouvé un problème de connexion avec Sage L100 Gestion Commerciale.
A moment de l'authentification d'un utilisateur sur la gestion commerciale, Sage créé deux connexions (une pour la gestion commerciale et une autre pour la compta) et stocke les deux SPID dans une autre table propre a lui.
Mais en changeant le propriétaire de la base vers un login qui n'a aucun droit particulier je tombe sur un problème d'authentification, le problème n'est résolu que lorsque j'ai ajouté le rôle db_owner a l'utilisateur de la base

[hmira]

Bonjour Hamid,

Je reviens sur ce point car j'ai justement rencontré un problème lors du changement d'owner d'une DB pour l'application SCCM qui malheureusement n'existe qu'en Prod...

J'ai dû remettre temporairement SA, même si celui-ci est désactivé, pour que cela fonctionne à nouveau.

Aurais-tu l'amabilité de me dire pourquoi il faut éviter à tout prix que le propriétaire soit sysadmin?

Merci.

Bonjour janlouk,

mikedavem, dans sa réponse ci-dessus, a déjà dit l'essentiel et a répondu à la question.

Notes au passage qu'ici on ne parle pas d'activation/désactivation de 'sa' ! ça c'est un tout autre sujet ! Ici On parle du propriétaire de la base de données en tant que 'sa', que 'sa' soit activé ou non.

C'est principalement à cause des risques réels de sécurité liés la fonctionnalité "Emprunt d'identité" lorsque les conditions suivantes sont réunies
- Le propriétaire de la base de données fait partie du rôle de niveau Serveur sysadmin, c'est le cas notamment de 'sa' en tant que propriétaire de la base.
- la base de données est marquée digne de confiance
- La base de données contient du code malveillant ou du code T-SQL, écrit par des développeurs, même involontairement, présentant des failles de sécurité (injection SQL etc.) et exécutées sous l'emprunt d'identité EXECUTE AS OWNER

Notons que le propriétaire de la base détient toutes les autorisations sur la dite base, que ce denier fasse partie ou non du rôle sysadmin. Donc cela ne change rien par rapport aux dégâts, potentiels, de niveau base de données (il peut même supprimer la base), que le propriétaire de la base ('sa' ou pas 'sa') peut occasionner.

En revanche, pour les actions de niveau Serveur, nécessitant des autorisations de niveau Serveur, et effectuées, sous une autre identité, par exemple sous la casquette Owner (EXECUTE AS OWNER), là ça change tout ! quant aux dégâts potentiels, de niveau serveur, voire même des dégâts sur d'autres bases de données du même serveur, lorsque le OWNER, n'est autre que 'sa' !

C'est aussi une des raisons pour laquelle, j'ai rajouté dans la dernière version des scripts Brent Ozar sur GitHub, dans la procédure dbo.sp_Blitz un nouveau paramètre nommée @UsualDBOwner.
@UsualDBOwner sysname = NULL
utilisé pour le Contrôle n° 55 'Database Owner <> ' + @UsualDBOwner
au lieu de la version d'avant : Contrôle n° 55 'Database Owner <> sa', où 'sa' était mentionné en dur pour ce contrôle, comme si toutes les bases devaient avoir comme propriétaire 'sa' !

A+

[janlouk]

Ok, merci Hamid.

Je pense que ça été le méli mélo vu que j'avais en tête que l'owner était de toute façon désactivé.

Et effectivement, j'étais surpris de voir dans sp_blitz qu'il fait remarquer quand SA n'est pas owner des db...

Mais tu as une idée pourquoi SCCM ne fonctionnerait plus quand je ne mets pas SA comme owner de la db même si celui-ci est désactivé? Pour moi, si un login est désactivé, tu ne peux plus l'utiliser...

[hmira]

Un login, en l'occurrence 'sa', désactivé empêche la connexion au Serveur avec ce login, par exemple via SSMS ou tout autre application utilisant le login 'sa' dans la chaîne de connexion.

Une connexion (login) désactivé n'empêche pas l’emprunt d’identité (impersonation) avec la dite connexion (login) même désactivée. En d'autres termes, les connexions désactivées (logins) conservent leurs autorisations et peuvent toujours être usurpées.

Si tu nous fournissais le ou les messages d'erreurs générés par l'application SCCM (?), d'aucuns pourraient peut-être avoir une idée.

A+

[janlouk]

Le voici, mais je le mets pour info car tu as répondu à ma question comme j'en avais besoin. Donc que même si un login est désactivé, cela n'empêche pas de pouvoir utiliser ses droits et donc cela me donne la réponse et les raisons pour lesquels il faut malgré tout ne pas mettre SA comme owner d'une DB même s'il est désactivé.

Code :

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

1
2
3
4
5
6
7
8
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65539. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: 
System.IO.FileLoadException: Could not load file or assembly 'cryptoutility, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
System.IO.FileLoadException: 
   at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
   at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
   at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)
   at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
   at System.Reflection.Assembly.Load(String assemblyString)