SQL Server 2016 Рgroupes de disponibilit̩s et gMSAs

Mis en avant

Ce billet ne concernera probablement pas les clients qui soit n’ont pas de règles de changement de mot de passe pour les comptes de services ou qui n’utilisent pas l’authentification Kerberos. Mais je suis sûr que le nombre de ces clients a diminué durant ces 10 dernières années. C’est en tout cas ce que j’ai pu remarquer chez différents clients.

Mais avant de commencer avec les groupes de services managés laissez moi introduire le concept de compte managé. Pourquoi les utiliser à la place des comptes de services traditionnels? Je pense que les administrateurs systèmes peuvent imaginer l’importance de ce type de compte …

> Lire la suite (en anglais)

David Barbarin
MVP & MCM SQL Server

SQL Server 2014 : sortie du livre « Développer et administrer pour la performance »

Mis en avant

2e1ax_default_entry_sqlserver2014_livre

Un billet en cette fin d’année qui approche à grand pas pour vous annoncer la sortie prochaine (fin décembre 2014) du livre SQL Server 2014 : Développer et administrer pour la performance en français et auquel j’ai eu l’immense plaisir de participer avec Frédéric Brouard (alias SQLPro – MVP SQL Server), Nicolas Souquet (alias Elsuket – MVP SQL Server) et Christian Soutou.

Ce livre est destiné aussi bien aux développeurs qu’aux administrateurs débutants ou confirmés soucieux de la performance et couvre un ensemble complet de domaines (l’administration, le développement, la sécurité ou encore la haute disponibilité).

Bonne lecture !

David BARBARIN (Mikedavem)
MVP et MCM SQL Server

SQL Server 2014 : SELECT ALL USERS SECURABLES & DB admins

Mis en avant

Microsoft a introduit 4 nouvelles permissions. L’un d’entre elles (SELECT ALL USERS SECURABLES) peut permettre à un administrateur de bases de données de pouvoir gérer une instance SQL Server sans forcément voir les données sensibles. Cependant cette phrase est à prendre avec du recul pour éviter des erreurs d’interprétations.

Lire la suite (en anglais)

Bonne lecture !

David BARBARIN (Mikedavem)
MVP et MCM SQL Server

A propos de l’erreur 33203 : SQL Server audit could not write to the security log

Mis en avant

Lors de la mise en place des audits de sécurité je me suis heurté à cette fameuse erreur 33203 sur une instance nommée qui indique explicitement que SQL Server ne peut pas écrire dan le journal de sécurité Windows.  Ma configuration est la suivante : SQL Server 2012 SP1  et Windows 2012 Server.

Je précise avant de commencer que les prérequis de fonctionnement des audits avec le journal de sécurité Windows sont respectés. La conséquence directe de l’erreur 33203 est qu’au évènement n’est enregistré dans le journal de sécurité Windows, ce qui est plutôt gênant pour des audits. De plus les journaux des erreurs Windows et SQL Server ne sont pas spécialement bavards quant à la cause de cette erreur.

 

image

 

Dans ce cas comment savoir ce qui empêche SQL Server d’écrire dans le journal ? Je dois dire que les outils sysinternals sont bien utiles dans ces moments et en particulier procmon.

Une capture procmon me donne ceci lorsque j’active un objet d’audit depuis SQL Server :

 

image

On peut remarquer ici que SQL Server tente un accès à la clé de registre HKLM\System\CurrentControlSet\Services\EventLog\Security et que cet accès lui est visiblement refusé. Un message est également inscrit dans le journal des erreurs SQL. Pas de souci, nous allons configurer les permissions adéquates sur cette clé de registre.

 

image

 

Le paramétrage des permissions a visiblement permis à l’instance SQL Server de créer une nouvelle clé de registre dans HKLM\System\CurrentControlSet\Services\EventLog\Security :

image

 

image

 

Me croyant sauvé, j’active mes audits mais je me heurte à une nouvelle erreur. Une nouvelle trace procmon me révèle le problème suivant :

 

image

 

SQL Server tente de lire le fichier C:\Windows\System32\LogFiles\Sum\Api.log mais encore un fois ce dernier se voit refuser l’accès. Le paramétrage en lecture seule du fichier Api.log me permet enfin d’activer mes audits et de valider que les évènements soient bien enregistrés dans le journal de sécurité Windows. Ce deuxième problème visiblement a été remonté chez Microsoft et est toujours d’actualité. A noter que je n’ai eu ce souci que pour mon instance nommé dans mon cas.

 

Quelques remarques supplémentaires :

  • Le fait d’enlever le compte de service SQL des permissions de la clé de registre HKLM\System\CurrentControlSet\Services\EventLog\Security ne semble plus être gênant à partir du moment où la nouvelle clé a été créé.
  • Il n’est pas possible cependant d’enlever les permissions sur le fichier C:\Windows\System32\LogFiles\Sum\Api.log sous peine d’avoir dans le journal des évènements Windows le type d’erreur suivant et plus d’évènement d’audit enregistré. A voir si le problème sera résolu plus tard par Microsoft (dans un CU4 peut être ?) avec Windows Server 2012. L’ensemble des problèmes répertoriés concerneraient Windows Server 2012.
  • Je ne pense pas que le problème soit lié à la version de SQL Server. Si j’ai le temps je testerai sur une version SQL Server 2008 R2 et mettrait à jour mon blog.

 

image

 

Bonne activation d’audit !! Sourire

 

David BARBARIN (Mikedavem)
MVP SQL Server

Problème d’utilisation de l’option WITH EXECUTE AS dans une procédure stockée avec les serveurs liés

Mis en avant

L’emprunt d’identité peut s’avérer nécessaire lorsque l’on commence à vouloir faire de la sécurité à un niveau granulaire relativement bas. Par exemple la politique de sécurité en vigueur peut nous contraindre à restreindre l’accès aux données via des procédures stockées. Cependant si ces données sont distantes et implique l’implémentation d’un serveur lié pour y avoir accès, cela peut donner matière à plus de réflexion. En effet, dans ce cas il va falloir donner les droits adéquates aux utilisateurs concernés sur le serveur distant pour accéder aux données, ce qui risque pour le coup d’augmenter la surface d’exposition. Un utilisateur A devra pouvoir exécuter une procédure stockée et sélectionner les données sur le serveur distant. L’utilisateur A peut par conséquent passer outre la procédure stockée et se connecter directement au serveur distant pour accéder aux données. Pour éviter cela on peut tout à fait utiliser le mécanisme d’emprunt d’identité dans une procédure stockée à l’aide de WITH EXECUTE AS et associer l’utilisateur dédié à cette tâche à un utilisateur sur le serveur distant. On bénéficie ainsi d’un contrôle d’accès beaucoup fort et maitrisé.

 

Le schéma ci-dessous illustre bien notre problématique :

 

icon_arrow Dans le 1er cas un mapping est nécessaire pour chaque utilisateur qui va vouloir accéder aux données du serveur distant Instance 2, ce qui signifie qu’il devra exister les mêmes logins sur le serveur distant Instance 2. Comme je l’ai évoqué au début cela implique également que les utilisateurs concernés pourront directement accéder aux données du serveur distant avec leurs informations de connexion.

 

image

 

icon_arrow Dans le 2ème cas nous mettons en place un mécanisme d’emprunt d’identité via l’option WITH EXECUTE AS USER dans la procédure stockée. L’idée ici est de n’autoriser que cet utilisateur à avoir accès aux données distantes.

 

image

 

La mise en place d’un tel mécanisme dans le contexte des serveurs liés nécessitent un certain paramétrage :

  • L’activation obligatoire de l’option de bases de données trustworthy. Malheureusement seule cette option est viable lorsqu’il s’agit d’accès inter instances. L’utilisation des certificats dans ce cas ne fonctionne pas.
  • Différer l’exécution de la procédure stockée concernée. Je n’ai pas trouvé d’article Microsoft sur le sujet mais simplement un item connect. C’est ce point que je vais détailler ici.

 

Si l’on utilise directement l’option WITH EXECUTE AS dans une procédure stockée de la manière suivante :

 

CREATE PROCEDURE dbo.GetRemoteData
WITH EXECUTE AS 'USER1'
AS

SELECT col1, col2
FROM [linked_server].[DATABASE].[schema].[TABLE]
GO

 

… avec l’appel suivant …

 

EXEC dbo.GetRemoteData

 

… il y a de grandes chances que cela ne fonctionne pas surtout si l’on a définit le contexte d’authentification de serveur lié suivant :

 

image

 

Dans ce cas seul le mapping défini pour l’utilisateur USER1 fonctionnera et aucun autre login ou aucun autre type d’authentification ne fonctionnera.

 

Si maintenant j’exécute ou je crée la procédure stockée dbo.GetRemoteData dans le contexte d’un utilisateur différent de l’utilisateur USER1 ayant les droits de création et d’exécution je peux me retrouver avec le message suivant :

 

image

 

Pourtant me direz-vous que nous avons bien défini l’emprunt d’identité dans la procédure stockée et qu’ici visiblement SQL Server n’est pas capable de trouver le mapping défini dans les options de sécurité du serveur lié. En réalité il faut bien comprendre ce qui se passe lorsqu’une procédure stockée est compilée et lorsque celle-ci est exécutée (run-time context). SQL Server compile une procédure stockée avec les credentials de l’appelant de la procédure et non ceux de l’utilisateur associée à l’emprunt d’identité. C’est pendant l’exécution de la procédure que le mécanisme d’emprunt d’identité jouera tout son rôle. Lorsqu’on accède aux données aux travers d’un serveur lié la compilation va générer l’erreur 7416 si l’appelant de la procédure n’a pas un mapping inter-instances configuré (SQL Server va vérifier lors de la compilation l’accès aux différents objets du serveur distant).

Pour contourner ce problème on peut user de 2 mécanismes qui permettent de différer l’exécution de la procédure stockée et de laisser le mécanisme d’emprunt d’identité opérer en premier lieu :

  • Créer une nouvelle procédure stockée qui va scinder l’exécution de notre procédure stockée initiale en 2 étapes
  • Encapsuler le code à exécuter de la procédure stockée avec EXEC( »), ce qui permettra au final de faire la même que dans la 1ère solution.

 

Par exemple :

CREATE PROCEDURE dbo.GetRemoteDataWithImpersonate
WITH EXECUTE AS 'USER1'  
AS

EXEC dbo.GetRemoteData
GO

ou encore :

CREATE PROCEDURE dbo.GetRemoteData    
WITH EXECUTE AS 'USER1'    
AS

EXEC('SELECT col1, col2 FROM [linked_server].[database].[schema].[table]')    
GO

 

Le choix de la méthode dépendra bien du contexte.

 

Bonne programmation !

David BARBARIN (Mikedavem)
MVP SQL Server

Utiliser le même certificat pour chiffrer les connexions SQL Server en SSL et pour TDE ?

Mis en avant

Lors d’une de mes dernières interventions nous avions implémenté chez un client le chiffrage des connexions SQL Server via SSL avec la mise en place d’un certificat. Ce même client m’ a demandé s’il était possible d’utiliser ce même certificat pour chiffrer une de leurs bases de données via TDE au lieu d’utiliser un certificat auto-signé. Vu de loin on pourrait penser que oui et que l’implémentation du certificat dans SQL Server est une chose aisée mais il n’en est rien. La raison est la suivante : l’export du certificat avec la clé privée au travers du magasin de certificat Windows génère un fichier avec l’extension .pfx qui n’est pas exploitable directement par SQL Server. Il faut donc trouver un moyen d’extraire de ce fichier le certificat et la clé privée dans 2 fichiers .cer et .pvk. Pour cela nous allons utiliser openssl.

 

Pour commencer et après avoir exporté le certificat du magasin certificat, on se retrouve avec un fichier ayant l’extension .pfx. Ce fichier stocke notre certificat, la clé publique et la clé privée.

image

image

 

Ensuite j’ai utilisé la version light de SSL (Win32_OpenSSL_v1.0.1c_light) et j’ai installé au préalable les composants redistribuables C++ 2008 (prérequis à l’installation de openSSL).

image

 

Une fois installée il suffit de lancer les commandes suivantes pour :

  • Extraire dans un fichier PEM la clé privée et le convertir dans un format compréhensible pour SQL Server (fichier pvk)

openssl.exe pkcs12 –in cert_ssl.pfx –nocerts –nodes –out cert_ssl_pvk.pem
openssl.exe rsa –in cert_ssl_pvk.pem –outform PVK –pvk-strong –out cert_ssl.pvk

 

  • Extraire dans un fichier PEM  le certificat et le convertir dans un format compréhensible pour SQL Server (fichier cer)

openssl.exe pkcs12 –in cert_ssl.pfx –nokeys –out cert_ssl_cer.pem
openssl.exe x509 –outform DER –in cert_ssl_cer.pem –out cert_ssl.cer

 

Ce qui nous donne les fichiers suivants :

image

 

Il suffit ensuite de créer le certificat correspondant dans SQL Server dans le contexte de la base master avec le certificat et la clé publique (.cer) et la clé privée correspondante (.pvk) :

,

CREATE CERTIFICATE TDE_CERT_2
FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\cert_ssl.cer'
WITH PRIVATE KEY
(
 FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\cert_ssl.pvk',
 DECRYPTION BY PASSWORD = 'P@$$w0rd'
);

 

Vérification de la création correcte de notre certificat à l’aide de la vue système sys.certificates :

 

image

 

Bien entendu j’avais déjà créé la clé de service master (SERVICE MASTER KEY) sur le serveur SQL. C’est donc en toute logique que la clé privée du certificat que je viens de créer soit chiffrée par la clé master ici.  Il ne reste ici plus qu’à créer la clé de chiffrement de la base de données qui sera elle même chiffrée par notre certificat.

Bon chiffrement !

David BARBARIN (Mikedavem)
MVP SQL Server