{"id":2945,"date":"2020-09-08T10:30:00","date_gmt":"2020-09-08T08:30:00","guid":{"rendered":"https:\/\/laurentvanacker.com\/?p=2945"},"modified":"2024-09-26T11:09:15","modified_gmt":"2024-09-26T09:09:15","slug":"iis-sql-and-constrained-kerberos-delegation-automatedlab","status":"publish","type":"post","link":"https:\/\/laurentvanacker.com\/index.php\/2020\/09\/08\/iis-sql-and-constrained-kerberos-delegation-automatedlab\/","title":{"rendered":"IIS, SQL Server et D\u00e9l\u00e9gation Kerberos Contrainte via AutomatedLab \/ IIS, SQL Server and Constrained Kerberos Delegation via AutomatedLab"},"content":{"rendered":"<p><a href=\"#en-us\" name=\"fr-fr\">Go to English version<\/a><\/p>\n<p>Toujours dans la veine d&rsquo;<a href=\"https:\/\/github.com\/AutomatedLab\/AutomatedLab\" target=\"_blank\" rel=\"noopener noreferrer\">AutomatedLab<\/a>, je propose dans cette article (qui fait suite \u00e0 <a href=\"https:\/\/laurentvanacker.com\/index.php\/tag\/automatedlab\/\">ceux-ci<\/a>) un petit environnement de test la mise en place de la d\u00e9l\u00e9gation Kerberos contrainte entre IIS et SQL Server<\/p>\n<p>J&rsquo;ai donc \u00e9labor\u00e9 le script <a href=\"https:\/\/github.com\/lavanack\/laurentvanacker.com\/blob\/master\/Windows%20Powershell\/IIS\/AutomatedLab\/SQL%20%26%20Kerberos%20Delegation\/AutomatedLab%20-%20IIS%2C%20SQL%20%26%20Kerberos%20Delegation.ps1\">suivant.<\/a> Ce <a href=\"https:\/\/github.com\/lavanack\/laurentvanacker.com\/blob\/master\/Windows%20Powershell\/IIS\/AutomatedLab\/SQL%20%26%20Kerberos%20Delegation\/AutomatedLab%20-%20IIS%2C%20SQL%20%26%20Kerberos%20Delegation.ps1\">script\u00a0<\/a> a besoin de :<\/p>\n<ul>\n<li><a id=\"a1c12e5284a026a9c64e60982cd65099-5229870145b432fed53217e27bede5f07debcc1a\" class=\"js-navigation-open\" title=\"contoso.com.zip\" href=\"https:\/\/github.com\/lavanack\/laurentvanacker.com\/blob\/master\/Windows%20Powershell\/IIS\/AutomatedLab\/SQL%20%26%20Kerberos%20Delegation\/WideWorldImporters.zip\">WideWorldImporters.zip<\/a> : qui contient la source de notre site Web de test (d\u00e9ploy\u00e9 automatiquement via <a href=\"https:\/\/www.iis.net\/downloads\/microsoft\/web-deploy\">Web Deploy<\/a> &#8211; J&rsquo;ai \u00e9galement un sc\u00e9nario de d\u00e9ploiement <a href=\"https:\/\/www.iis.net\/downloads\/microsoft\/web-deploy\">Web Deploy<\/a>\u00a0simple via <a href=\"https:\/\/github.com\/AutomatedLab\/AutomatedLab\" target=\"_blank\" rel=\"noopener noreferrer\">AutomatedLab<\/a> pour une application IIS qui utilise une base SQL server <a href=\"https:\/\/github.com\/lavanack\/laurentvanacker.com\/tree\/master\/Windows%20Powershell\/IIS\/AutomatedLab\/WebDeploy\">ici<\/a>).<\/li>\n<\/ul>\n<p>L&rsquo;environnement est compos\u00e9 de 4 serveurs :<\/p>\n<ul>\n<li>DC01 : Contr\u00f4leur de domaine (contoso.com)<\/li>\n<li>SQL01: Serveur SQL Server<\/li>\n<li>IIS01 : Serveur <a href=\"https:\/\/www.iis.net\/\">IIS<\/a><\/li>\n<li>CLIENT01 : Poste client<\/li>\n<\/ul>\n<p>Le nom des serveurs est \u00e9crit dans le code (faites un \u00ab\u00a0Global Replace\u00a0\u00bb CTRL+H si les noms ne vous conviennent pas). De m\u00eame que certains autres param\u00e8tres tels que :<\/p>\n<ul>\n<li>Le nom du Lab (\u00ab\u00a0IISSQLKerbDeleg\u00a0\u00bb par d\u00e9faut)<\/li>\n<li>Le compte d&rsquo;administration \u00e0 utiliser (\u00ab\u00a0Administrator\u00a0\u00bb par d\u00e9faut)<\/li>\n<li>Le mot de passe associ\u00e9 (\u00ab\u00a0P@ssw0rd\u00a0\u00bb par d\u00e9faut)<\/li>\n<li>Le nom du domaine (FQDN et NetBIOS) (\u00ab\u00a0contoso.com\u00a0\u00bb et \u00ab\u00a0CONTOSO\u00a0\u00bb par d\u00e9faut)<\/li>\n<li>Le compte du compte de d\u00e9marrage de l&rsquo;application pool <a href=\"https:\/\/www.iis.net\/\">IIS<\/a> (\u00ab\u00a0IISAppPoolUser\u00a0\u00bb par d\u00e9faut)<\/li>\n<li>Le nom du sites web utilis\u00e9 (wideworldimporters.contoso.com)<\/li>\n<li>&#8230;<\/li>\n<\/ul>\n<p>Une fois le script termin\u00e9, connectez-vous sur CLIENT01 et d\u00e9marrez Internet Explorer (et cliquez sur l&rsquo;ic\u00f4ne \u00ab\u00a0Home\u00a0\u00bb &#8211; Si rien n&rsquo;appara\u00eet lancer un \u00ab\u00a0gpupdate \/force \/wait:-1\u00a0\u00bb). Le site web http:\/\/wideworldimporters.contoso.com s&rsquo;ouvrira automatiquement et vous obtiendrez alors :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-2951 aligncenter\" src=\"https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg.jpg\" alt=\"\" width=\"828\" height=\"667\" srcset=\"https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg.jpg 828w, https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg-300x242.jpg 300w, https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg-768x619.jpg 768w\" sizes=\"auto, (max-width: 828px) 100vw, 828px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>Connectez-vous alors sur le serveur SQL01 et lancer un \u00ab\u00a0SQL Server Profiler\u00a0\u00bb (puis cliquer sur \u00ab\u00a0File &gt; New Trace &#8211; ou CTRL + N et ensuite \u00ab\u00a0Connect\u00a0\u00bb puis \u00ab\u00a0Run\u00a0\u00bb en laissant les valeurs par d\u00e9faut ).<\/p>\n<p>Revenez ensuite \u00e0 CLIENT01 et cliquer sur le Bouton \u00ab\u00a0People\u00a0\u00bb (cf. capture d&rsquo;\u00e9cran ci-dessus). En revenant sur la fen\u00eatre du \u00ab\u00a0SQL Server Profiler\u00a0\u00bb sur le serveur SQL01 vous constaterez que l&rsquo;identit\u00e9 du client (\u00ab\u00a0CONTOSO\\Administrator \u00ab\u00a0) est reconnu jusque sur le serveur SQL01 gr\u00e2ce \u00e0 la d\u00e9l\u00e9gation contrainte Kerberos (qui est configur\u00e9 sur le compte \u00ab\u00a0IISAppPoolUser\u00a0\u00bb &#8211; cf. sur DC01).<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-2954 aligncenter\" src=\"https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg2-1.jpg\" alt=\"\" width=\"925\" height=\"189\" srcset=\"https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg2-1.jpg 925w, https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg2-1-300x61.jpg 300w, https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg2-1-768x157.jpg 768w\" sizes=\"auto, (max-width: 925px) 100vw, 925px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-2955 aligncenter\" src=\"https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg3-1.jpg\" alt=\"\" width=\"408\" height=\"534\" srcset=\"https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg3-1.jpg 408w, https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg3-1-229x300.jpg 229w\" sizes=\"auto, (max-width: 408px) 100vw, 408px\" \/><\/p>\n<p>Quelques liens utiles :<\/p>\n<ul>\n<li><a href=\"https:\/\/techcommunity.microsoft.com\/t5\/iis-support-blog\/setting-up-kerberos-authentication-for-a-website-in-iis\/ba-p\/347882\">https:\/\/techcommunity.microsoft.com\/t5\/iis-support-blog\/setting-up-kerberos-authentication-for-a-website-in-iis\/ba-p\/347882<\/a><\/li>\n<li><a href=\"https:\/\/docs.microsoft.com\/en-us\/archive\/blogs\/chiranth\/all-about-kerberos-the-three-headed-dog-with-respect-to-iis-and-sql\">https:\/\/docs.microsoft.com\/en-us\/archive\/blogs\/chiranth\/all-about-kerberos-the-three-headed-dog-with-respect-to-iis-and-sql<\/a><\/li>\n<\/ul>\n<p><img decoding=\"async\" src=\"http:\/\/laurentvanacker.com\/wp-content\/uploads\/2017\/01\/012717_1333_Fusionnerde1.png\" alt=\"\" \/><\/p>\n<p><a href=\"#fr-FR\" name=\"en-us\">Aller \u00e0 la version fran\u00e7aise<\/a><\/p>\n<div class=\"tlid-results-container results-container\">\n<div class=\"tlid-result result-dict-wrapper\">\n<div class=\"result tlid-copy-target\">\n<div class=\"text-wrap tlid-copy-target\">\n<div class=\"result-shield-container tlid-copy-target\" tabindex=\"0\">Still working on <a href=\"https:\/\/github.com\/AutomatedLab\/AutomatedLab\" target=\"_blank\" rel=\"noopener noreferrer\">AutomatedLab<\/a>, I propose in this article (which follows these <a href=\"https:\/\/laurentvanacker.com\/index.php\/tag\/automatedlab\/\">ones<\/a>) a small test environment for setting up Constrained Kerberos Delegation between IIS and SQL Server<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<p>So I developed the following <a href=\"https:\/\/github.com\/lavanack\/laurentvanacker.com\/blob\/master\/Windows%20Powershell\/IIS\/AutomatedLab\/SQL%20%26%20Kerberos%20Delegation\/AutomatedLab%20-%20IIS%2C%20SQL%20%26%20Kerberos%20Delegation.ps1\">script<\/a>. This <a href=\"https:\/\/github.com\/lavanack\/laurentvanacker.com\/blob\/master\/Windows%20Powershell\/IIS\/AutomatedLab\/SQL%20%26%20Kerberos%20Delegation\/AutomatedLab%20-%20IIS%2C%20SQL%20%26%20Kerberos%20Delegation.ps1\">script<\/a>\u00a0needs:<\/p>\n<ul>\n<li><a id=\"a1c12e5284a026a9c64e60982cd65099-5229870145b432fed53217e27bede5f07debcc1a\" class=\"js-navigation-open\" title=\"contoso.com.zip\" href=\"https:\/\/github.com\/lavanack\/laurentvanacker.com\/blob\/master\/Windows%20Powershell\/IIS\/AutomatedLab\/SQL%20%26%20Kerberos%20Delegation\/WideWorldImporters.zip\">WideWorldImporters.zip<\/a> : which contains the source of our website (automatically deployed via <a href=\"https:\/\/www.iis.net\/downloads\/microsoft\/web-deploy\">Web Deploy<\/a> &#8211; I also have a simple <a href=\"https:\/\/www.iis.net\/downloads\/microsoft\/web-deploy\">Web Deploy<\/a> deployment scenario &#8211; via <a href=\"https:\/\/github.com\/AutomatedLab\/AutomatedLab\" target=\"_blank\" rel=\"noopener noreferrer\">AutomatedLab<\/a>\u00a0&#8211; for an IIS application that uses a SQL server base <a href=\"https:\/\/github.com\/lavanack\/laurentvanacker.com\/tree\/master\/Windows%20Powershell\/IIS\/AutomatedLab\/WebDeploy\">here<\/a>).<\/li>\n<\/ul>\n<p>The environment is composed of 4 servers:<\/p>\n<ul>\n<li>DC01: Domain Controller (contoso.com)<\/li>\n<li>SQL01: SQL Server<\/li>\n<li>IIS01: <a href=\"https:\/\/docs.microsoft.com\/en-us\/windows-server\/networking\/technologies\/network-load-balancing\">IIS<\/a> server<\/li>\n<li>CLIENT01: Client machine<\/li>\n<\/ul>\n<p>The name of the servers is written in the code (make a \u00ab\u00a0Global Replace\u00a0\u00bb CTRL+H if the names do not suit you). As well as certain other parameters such as:<\/p>\n<div class=\"tlid-results-container results-container\">\n<div class=\"tlid-result result-dict-wrapper\">\n<div class=\"result tlid-copy-target\">\n<div class=\"text-wrap tlid-copy-target\">\n<ul>\n<li class=\"result-shield-container tlid-copy-target\" tabindex=\"0\"><span class=\"tlid-translation translation\" lang=\"en\"><span title=\"\">Lab name (\u00ab\u00a0IISSQLKerbDeleg\u00a0\u00bb by default)<\/span><\/span><\/li>\n<li class=\"result-shield-container tlid-copy-target\" tabindex=\"0\"><span class=\"tlid-translation translation\" lang=\"en\"><span title=\"\">The administration account (\u00ab\u00a0Administrator\u00a0\u00bb by default)<\/span><\/span><\/li>\n<li class=\"result-shield-container tlid-copy-target\" tabindex=\"0\"><span class=\"tlid-translation translation\" lang=\"en\"><span title=\"\">The associated password (\u00ab\u00a0P@ssw0rd\u00a0\u00bb by default)<\/span><\/span><\/li>\n<li class=\"result-shield-container tlid-copy-target\" tabindex=\"0\"><span class=\"tlid-translation translation\" lang=\"en\"><span title=\"\">The domain name (FQDN and NetBIOS) (\u00ab\u00a0contoso.com\u00a0\u00bb and \u00ab\u00a0CONTOSO\u00a0\u00bb by default)<\/span><\/span><\/li>\n<li class=\"result-shield-container tlid-copy-target\" tabindex=\"0\"><span class=\"tlid-translation translation\" lang=\"en\"><span class=\"\" title=\"\">The <\/span><\/span><a href=\"https:\/\/docs.microsoft.com\/en-us\/windows-server\/networking\/technologies\/network-load-balancing\">IIS<\/a> <span class=\"tlid-translation translation\" lang=\"en\"><span class=\"\" title=\"\">application pool identity (\u00ab\u00a0IISAppPoolUser\u00a0\u00bb by default)<\/span><\/span><\/li>\n<li class=\"result-shield-container tlid-copy-target\" tabindex=\"0\"><span class=\"tlid-translation translation\" lang=\"en\"><span class=\"\" title=\"\">The name of the used website (wideworldimporters.contoso.com)<\/span><\/span><\/li>\n<\/ul>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<p>Once the script is done, connect to CLIENT01 and start Internet Explorer (and click on the \u00ab\u00a0Home\u00a0\u00bb button &#8211; If nothing appears run a \u00ab\u00a0gpupdate \/force \/wait:-1\u00a0\u00bb command). The http:\/\/wideworldimporters.contoso.com website will open automatically and you will get:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-2951 aligncenter\" src=\"https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg.jpg\" alt=\"\" width=\"828\" height=\"667\" srcset=\"https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg.jpg 828w, https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg-300x242.jpg 300w, https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg-768x619.jpg 768w\" sizes=\"auto, (max-width: 828px) 100vw, 828px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>Connect to the SQL01 server and launch a \u00ab\u00a0SQL Server Profiler\u00a0\u00bb (then click on \u00ab\u00a0File&gt; New Trace &#8211; or CTRL + N and then\u00a0\u00bb Connect \u00ab\u00a0then\u00a0\u00bb Run\u00a0\u00bb, leaving the default values).<\/p>\n<p>Then come back to CLIENT01 and click on the \u00ab\u00a0People\u00a0\u00bb button (see screenshot above). Returning to the \u00ab\u00a0SQL Server Profiler\u00a0\u00bb window on the SQL01 server, you will notice that the identity of the client (\u00ab\u00a0CONTOSO\\Administrator\u00a0\u00bb) is recognized up to the SQL01 server thanks to the Kerberos constrained delegation (which is configured on the account \u00ab\u00a0IISAppPoolUser\u00a0\u00bb &#8211; see on DC01).<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-2954 aligncenter\" src=\"https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg2-1.jpg\" alt=\"\" width=\"925\" height=\"189\" srcset=\"https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg2-1.jpg 925w, https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg2-1-300x61.jpg 300w, https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg2-1-768x157.jpg 768w\" sizes=\"auto, (max-width: 925px) 100vw, 925px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-2955 aligncenter\" src=\"https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg3-1.jpg\" alt=\"\" width=\"408\" height=\"534\" srcset=\"https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg3-1.jpg 408w, https:\/\/laurentvanacker.com\/wp-content\/uploads\/2020\/09\/IISSQLKerbDeleg3-1-229x300.jpg 229w\" sizes=\"auto, (max-width: 408px) 100vw, 408px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>Some useful links:<\/p>\n<ul>\n<li><a href=\"https:\/\/techcommunity.microsoft.com\/t5\/iis-support-blog\/setting-up-kerberos-authentication-for-a-website-in-iis\/ba-p\/347882\">https:\/\/techcommunity.microsoft.com\/t5\/iis-support-blog\/setting-up-kerberos-authentication-for-a-website-in-iis\/ba-p\/347882<\/a><\/li>\n<li><a href=\"https:\/\/docs.microsoft.com\/en-us\/archive\/blogs\/chiranth\/all-about-kerberos-the-three-headed-dog-with-respect-to-iis-and-sql\">https:\/\/docs.microsoft.com\/en-us\/archive\/blogs\/chiranth\/all-about-kerberos-the-three-headed-dog-with-respect-to-iis-and-sql<\/a><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>Laurent.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Go to English version Toujours dans la veine d&rsquo;AutomatedLab, je propose dans cette article (qui fait suite \u00e0 ceux-ci) un petit environnement de test la [&#8230;]<\/p>\n","protected":false},"author":2,"featured_media":2465,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,19,12],"tags":[20,54,48,25,39,41],"class_list":["post-2945","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-iis","category-powershell","category-securite-security","tag-net","tag-automatedlab","tag-github","tag-iis","tag-powershell","tag-securite"],"_links":{"self":[{"href":"https:\/\/laurentvanacker.com\/index.php\/wp-json\/wp\/v2\/posts\/2945","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/laurentvanacker.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/laurentvanacker.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/laurentvanacker.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/laurentvanacker.com\/index.php\/wp-json\/wp\/v2\/comments?post=2945"}],"version-history":[{"count":12,"href":"https:\/\/laurentvanacker.com\/index.php\/wp-json\/wp\/v2\/posts\/2945\/revisions"}],"predecessor-version":[{"id":3084,"href":"https:\/\/laurentvanacker.com\/index.php\/wp-json\/wp\/v2\/posts\/2945\/revisions\/3084"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/laurentvanacker.com\/index.php\/wp-json\/wp\/v2\/media\/2465"}],"wp:attachment":[{"href":"https:\/\/laurentvanacker.com\/index.php\/wp-json\/wp\/v2\/media?parent=2945"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/laurentvanacker.com\/index.php\/wp-json\/wp\/v2\/categories?post=2945"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/laurentvanacker.com\/index.php\/wp-json\/wp\/v2\/tags?post=2945"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}