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

MVP SQL Server : Quatrième chapitre pour l’année 2013

Mis en avant

Pas de poisson d’avril et une vraie bonne nouvelle de la part de Microsoft aujourd’hui : le renouvèlement de mon titre de MVP SQL Server pour la 4ème fois.

J’en profite pour remercier Martine Tiphaine et Microsoft une nouvelle fois !

 

mvp

 

David BARBARIN (Mikedavem)
MVP SQL Server

Instant File Initialization et SetFileValidData()

Mis en avant

Instant File Initialization est une option connue des DBA et permet d’accélérer certaines opérations faisant de la réservation d’espace disque. Avec SQL Server cela se traduit par les opérations de création, restauration de bases de données ou encore ajout de fichiers, modification de taille ou encore expansion de fichiers … Bref cette option est souvent très bénéfique pour les performances mais celle-ci exige quelques prérequis qui sont les suivants :

  • Le fichier n’est pas un fichier journal d’une base de données
  • Le système d’exploitation supporte l’utilisation de la fonction SetFileValidData()
  • Le compte de service SQL possède le privilège SE_MANAGE_VOLUME_NAME
  • Le fichier n’est pas un fichier sparse
  • Le fichier n’est pas concernée par du chiffrement TDE
  • Trace flag 1806 n’est pas activé sur l’instance SQL (ce traceflag désactive l’utilisation de cette fonctionnalité)

 

Ce qui nous intéresse ici c’est l’utilisation de la fonction SetFileValidData(). Nous voila donc plongé dans la documentation des  API Windows et de notre fonction SetFileValidData(). Voici ce que la documentation nous propose :

Sets the valid data length of the specified file. This function is useful in very limited scenarios. For more information, see the Remarks section.

Dans la section remarques nous avons :

The SetFileValidData function sets the logical end of a file. To set the size of a file, use the SetEndOfFile function. The physical file size is also referred to as the end of the file.

Each file stream has the following properties:

  • File size: the size of the data in a file, to the byte.
  • Allocation size: the size of the space that is allocated for a file on a disk, which is always an even multiple of the cluster size.
  • Valid data length: the length of the data in a file that is actually written, to the byte. This value is always less than or equal to the file size.

The SetFileValidData function allows you to avoid filling data with zeros when writing nonsequentially to a file. The function makes the data in the file valid without writing to the file. As a result, although some performance gain may be realized, existing data on disk from previously existing files can inadvertently become available to unintended readers

A caller must have the SE_MANAGE_VOLUME_NAME privilege enabled when opening a file initially

 

Pour résumer : cette fonction va nous permettre d’éviter le remplissage de zéros dans un fichier lorsque les écritures ne sont pas séquentielles

Pourquoi pour des opérations non séquentielles ?  Effectivement si l’on écrit de manière séquentielle dans un fichier la phase d’écriture de zéro est relativement rapide puisque l’on écrit toujours à la suite des données précédentes

Cependant si l’on écrit de manière aléatoire, le problème est tout autre. Imaginez que l’on écrive la première au début du fichier et la 2ème fois à la fin, il va donc falloir remplir de zéros le fichier de la première écriture jusqu’à la 2ème. La documentation le stipule bien : chaque fichier doit posséder notamment une propriété Valid data length qui correspond à la longueur des données écrites dans le fichier.  Cette opération, vous l’avez compris, peut prendre du temps en fonction de la longueur à initialiser. SQL Server écrit de manière aléatoire dans le fichier même lors de son initialisation. C’est la raison pour laquelle l’utilisation de cette option est très intéressante ici.

 

Ecriture séquentielle :

 

image

 

Ecriture aléatoire :

 

image

 

L’utilisation de procmon peut nous permettre de visualiser l’utilisation des fonctions SetEndOfFile() et SetFileValidData(). J’ai simplement créé une base de données simple avec le fichier de données TestIntantFileInitializationWith.mdf.

 

  • Dans le cas où Instant File Initialization n’est pas activé

 

image

 

On ne voit ici que l’utilisation de la fonction SetEndOfFile(). Cette fonction est utilisée dans tous les cas pour fixer physiquement la fin du fichier. Dans le cas présent la fin du fichier est fixée à 10485760 octets soit 10Go.

 

  • Dans le cas où Instant File Initialization est activé

 

image

 

Une fonction supplémentaire est utilisée ici SetFileValidDate() qui permet de valider la longueur des données dans le fichier mdf :  ValidDataLength : 10485760 (10Go de données dans un fichier de 10Go)

 

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

SQL Server mirroring : Ajout d’un fichier de bases de données et impact

Mis en avant

Lors d’une prestation chez un client, celui-ci m’a demandé s’il était possible d’ajouter un fichier à une base de données concernée par une session en miroir. La réponse est évidement oui mais cette opération nécessite quelques actions supplémentaires pour qu’elle soit également validée sur le serveur en miroir. En effet l’ajout d’un fichier de bases de données va suspendre la session en miroir comme on peut le voir sur l’image ci-dessous :

image

image

 

Le fait de reprendre la session ne changera rien. Si on jette un coup d’œil dans le journal des évènements SQL Server sur l’instance en miroir on peut voir le message suivant :

image

 

La modification du schéma de bases de données n’a pas été répercuté automatiquement sur l’instance en miroir et par conséquence la session en miroir est suspendue. La question évidente ici est comment répercuter la modification de schéma de base sur l’instance en miroir ? L’opération n’est pas très compliquée en réalité : il suffit de :

  • supprimer la session en miroir qui concerne nos bases de données
  • initier une sauvegarde du journal de la base de données sur le serveur principal
  • restaurer la sauvegarde du journal sur la base de données en mode NORECOVERY sur le serveur en miroir (il n’est pas obligatoire de restaurer une sauvegarde complète pour resynchroniser l’instance principale et l’instance en miroir)
  • recréer la session miroir entre les 2 instances concernées

 

Sur le serveur principal :

 -- Désactivation session miroir
ALTER DATABASE DXXXXXX_MIRRORING SET PARTNER OFF;
GO

-- Sauvegarde des données du journal non envoyées sur le miroir
BACKUP LOG DXXXXXX_MIRRORING TO DISK = 'E:\MSSQL\BACKUP\DXXXXXX_MIRRORING.TRN'
WITH INIT;
GO

 

Sur le serveur miroir :

 -- Restauration des données du journal + fichier manquant
RESTORE DATABASE DXXXXXX_MIRRORING
FILE = 'DXXXXXX_2'
FROM DISK = 'E:\MSSQL2\BACKUP\DXXXXXX_MIRRORING.TRN'
WITH MOVE 'DXXXXXX_2' TO 'E:\MSSQL2\DXXXXXX_2.ndf',
     NORECOVERY;
GO
     
-- Réactivation session en miroir
ALTER DATABASE DXXXXXX_MIRRORING SET PARTNER = 'TCP://SQL2008.insentia.lab:5022';

 

Sur le serveur principal :

 -- Réactivation session en miroir
ALTER DATABASE DXXXXXX_MIRRORING SET PARTNER = 'TCP://SQL2008.insentia.lab:5023';
ALTER DATABASE DXXXXXX_MIRRORING SET WITNESS = 'TCP://MCM.insentia.lab:5022';

 

Et voilà cette manipulation permet de pouvoir resynchroniser nos bases de données en miroir sans avoir à effectuer une restauration complète

 

David BARBARIN (Mikedavem)
MVP SQL Server

Cluster Resource DTC in clustered service or application ‘Cluster Group’ Failed

Mis en avant

Un problème assez particulier que je viens de rencontrer au cours d’un audit d’un cluster SQL chez un de mes clients. Je vois une multitude d’erreurs dans le journal des évènements 1205 et 1069 liée à une ressource DTC. Le hic c’est qu’il existait bien une ressource DTC sur le serveur mais elle n’était pas concernée. Me voilà bien avancé avec mon erreur …

Pour bien situer le contexte voici l’erreur rencontré dans le journal des évènements:

 

image

 

Seulement en regardant les ressources au niveau du cluster management en mode GUI on voit que la ressource DTC est en ligne et fonctionne correctement. Je passe en mode commande (cluster.exe) et voici ce que je remarque :

 

image

 

J’ai en réalité 2 DTC sur mon cluster SQL. La ressource MSDTC-SQL2005Dtc est la ressource qui a été créée lors de l’installation du cluster SQL. En revanche la ressource DTC (statut failed) est présente dans le groupe Cluster Group mais n’est pas visible dans la console cluadmin.msc. Visiblement c’est cette 2ème ressource qui pose problème. A noter que les ressources qui font parti du groupe Cluster Group ne sont pas visible en mode GUI.

La suppression de cette ressource fera disparaitre le message d’erreur intermittent présent dans les différents journaux d’évènements.

image

 

En vérifiant dans les services de composants on peut s’assurer que c’est bien la ressource cluster qui sera utilisé :

image

 

David BARBARIN (Mikedavem)
MVP SQL Server

Les journées SQL Server–second volet

Mis en avant

 

 

 

Le premier volet des journées SQL Server a visiblement connu un très grand succès. C’est la raison pour laquelle GUSS s’est relancé dans la préparation d’un second volet qui devrait se dérouler en décembre. Les dates définitives seront communiqués un peu plus tard. Cependant pour que cet évènement soit de nouveau un succès nous avons besoin de vos avis qui se présente sous la forme d’un sondage qui ne vous prendra que quelques minutes de votre temps Sourire et qui nous permettront de mieux cibler vos attentes à tous les niveaux (contenu des sessions, organisation etc …)

Pour le remplir c’est par ici

Merci par avance !!

 

David BARBARIN (Mikedavem)
MVP SQL Server

Interpréter les noms de statistiques créées automatiquement par l’optimisateur de requêtes de SQL Server

Pour ceux qui se demandent comment interpréter les noms de statistiques créées automatiquement par l’optimisateur de requêtes ce billet est pour vous Sourire. Comme vous le savez sans doute lorsqu’un index créé des statistiques lui sont également associées. Ces dernières servent à l’optimisateur de requêtes afin de déterminer un plan optimal pour retrouver les données initiés par une requête. Autrement dit en fonction des cardinalités ou de la sélectivité des données sous jacentes celui-ci va déterminer quelle(s) méthode(s) il devra pour utiliser pour les retrouver avec un minimum de coût.  Cependant que se passe-t-il lorsqu’une requête composée d’un prédicat ne concerne pas un index ? He bien celui-ci va créer des statistiques sur la ou les colonnes concernées pour pouvoir estimer la cardinalité des données à retourner. Ces statistiques se retrouvent sous la forme _WA_Sys_00000004_0F382DC6 par exemple. Comment interpréter ce nom plutôt abscons à première vue. C’est ce que nous allons voir dans la suite de billet.

Lire la suite

Trouver un login de type Windows par son SID Windows

Il peut arriver parfois de vouloir retrouver un login Windows via son SID (ou Security Identifier) au format Windows sur une instance SQL Server. Le problème est que le SID stocké sur SQL Server est au format varbinary(85). Il nous faut donc formater la valeur de cette colonne pour pouvoir la mettre au format Windows S-X-X-XX-XXXXXXXXXX-XXXXXXXXXX-XXXXXXXXX-XXXX.

Lire la suite

Buffer Cache Hit Ratio : seul compteur à surveiller pour détecter une pression mémoire ?

Un petit billet sur un compteur que vous connaissez certainement tous et qui permet en autre de détecter une pression mémoire sur une instance SQL Server. Seulement en faisant un audit des compteurs de surveillance d’un de mes clients, je me suis aperçu qu’il n’utilisait que ce dernier pour détecter la présence ou non une pression mémoire sur l’ensemble des instances de son parc. Je lui ai donc expliqué que l’utilisation unique de ce compteur ne permettait pas à tous les coups de détecter un problème d’utilisation mémoire. Je vous propose de voir pourquoi dans ce billet.

Lire la suite