Archives mensuelles : janvier 2013

Techdays 2013 à Paris : Je serais présent en tant qu’ATE SQL Server

Mis en avant

Publié le par
Répondre

techdays_ate

ATE sur les Microsoft TechDays 2013

 

J’aurais le plaisir d’être ATE sur la technologie SQL Server aux techdays 2013 à Paris les 13 et 14 février prochain. Ca sera l’occasion pour moi d’échanger autour de SQL Server avec vous !

Au plaisir de vous voir !

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

Publié le par
1

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

Publié le par
Répondre

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

Journées SQL Server 2012 : Les slides

Mis en avant

Publié le par
Répondre

Un petit billet pour commencer cette année 2013 comme il se doit ! A disposition les slides des sessions que j’ai présenté aux journées SQL Server 2012 à savoir :

  • SQL Server et stockage interne
  • SQL Server et infrastructure

 

image

 

Merci à vous de laisser vos commentaires. Ceux-ci sont importants pour savoir ce que vous avez pu apprécier ou non. C’est l’occasion pour nous également d’améliorer encore plus nos sessions. Au plaisir de vous revoir aux journées SQL Server 2013 Clignement d'Å“il

 

David BARBARIN (Mikedavem)
MVP SQL Server