Repository navigation
Expand file tree
/
Copy pathdeploy.sh
More file actions
executable file
·946 lines (834 loc) · 32.7 KB
/
Copy pathdeploy.sh
File metadata and controls
executable file
·946 lines (834 loc) · 32.7 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
#!/bin/bash
# Variables
PREFIX="${PREFIX:-local}"
SUFFIX="${SUFFIX:-test}"
LOCATION='westeurope'
RESOURCE_GROUP_NAME="${PREFIX}-rg"
SQL_SERVER_NAME="${PREFIX}-sqlserver-${SUFFIX}"
SQL_SERVER_IDENTITY_NAME="${PREFIX}-tde-identity-${SUFFIX}"
FIREWALL_RULE_NAME="AllowAllIPs"
ADMIN_USER='sqladmin'
ADMIN_PASSWORD='P@ssw0rd1234!'
DATABASE_USER_NAME='testuser'
DATABASE_USER_PASSWORD='TestP@ssw0rd123'
SQL_DATABASE_NAME='PlannerDB'
APP_SERVICE_PLAN_NAME="${PREFIX}-app-service-plan-${SUFFIX}"
APP_SERVICE_PLAN_SKU="S1"
WEB_APP_NAME="${PREFIX}-webapp-${SUFFIX}"
LOGIN_NAME="Paolo"
CURRENT_DIR="$(cd "$(dirname "$0")" && pwd)"
ZIPFILE="planner_website.zip"
RUNTIME="dotnetcore"
RUNTIME_VERSION="10.0"
DEPLOY_APP=1
KEY_VAULT_NAME="${PREFIX}-kv-${SUFFIX}"
SECRET_NAME="${PREFIX}-secret-${SUFFIX}"
CERT_NAME="${PREFIX}-cert-${SUFFIX}"
TDE_KEY_NAME="${PREFIX}-tde-key-${SUFFIX}"
LOG_ANALYTICS_NAME="${PREFIX}-log-analytics-${SUFFIX}"
APPLICATION_INSIGHTS_NAME="${PREFIX}-insights-${SUFFIX}"
DIAGNOSTIC_SETTINGS_NAME='default'
# Change the current directory to the script's directory
cd "$CURRENT_DIR" || exit
# Create a resource group
echo "Creating resource group [$RESOURCE_GROUP_NAME]..."
az group create \
--name $RESOURCE_GROUP_NAME \
--location $LOCATION \
--only-show-errors 1>/dev/null
if [ $? -eq 0 ]; then
echo "Resource group [$RESOURCE_GROUP_NAME] created successfully."
else
echo "Failed to create resource group [$RESOURCE_GROUP_NAME]."
exit 1
fi
# Check if the Log Analytics workspace already exists
echo "Checking if [$LOG_ANALYTICS_NAME] Log Analytics workspace already exists in the [$RESOURCE_GROUP_NAME] resource group..."
az monitor log-analytics workspace show \
--resource-group "$RESOURCE_GROUP_NAME" \
--workspace-name "$LOG_ANALYTICS_NAME" \
--only-show-errors &>/dev/null
if [[ $? != 0 ]]; then
echo "No [$LOG_ANALYTICS_NAME] Log Analytics workspace actually exists in the [$RESOURCE_GROUP_NAME] resource group"
echo "Creating [$LOG_ANALYTICS_NAME] Log Analytics workspace in the [$RESOURCE_GROUP_NAME] resource group..."
# Create the Log Analytics workspace that receives the telemetry of Application Insights and the logs and metrics
# of the diagnostic settings
az monitor log-analytics workspace create \
--name "$LOG_ANALYTICS_NAME" \
--resource-group "$RESOURCE_GROUP_NAME" \
--location "$LOCATION" \
--query-access "Enabled" \
--retention-time 30 \
--sku "PerNode" \
--tags environment=test iac=az-cli \
--only-show-errors 1>/dev/null
if [[ $? == 0 ]]; then
echo "[$LOG_ANALYTICS_NAME] Log Analytics workspace successfully created in the [$RESOURCE_GROUP_NAME] resource group"
else
echo "Failed to create [$LOG_ANALYTICS_NAME] Log Analytics workspace in the [$RESOURCE_GROUP_NAME] resource group"
exit 1
fi
else
echo "[$LOG_ANALYTICS_NAME] Log Analytics workspace already exists in the [$RESOURCE_GROUP_NAME] resource group"
fi
# Get the resource id of the Log Analytics workspace
LOG_ANALYTICS_ID=$(az monitor log-analytics workspace show \
--resource-group "$RESOURCE_GROUP_NAME" \
--workspace-name "$LOG_ANALYTICS_NAME" \
--query id \
--output tsv \
--only-show-errors)
if [[ -z $LOG_ANALYTICS_ID ]]; then
echo "Failed to retrieve the resource id of the [$LOG_ANALYTICS_NAME] Log Analytics workspace"
exit 1
fi
# Check if the Application Insights component already exists
echo "Checking if [$APPLICATION_INSIGHTS_NAME] Application Insights component exists in the [$RESOURCE_GROUP_NAME] resource group..."
az monitor app-insights component show \
--app "$APPLICATION_INSIGHTS_NAME" \
--resource-group "$RESOURCE_GROUP_NAME" \
--only-show-errors &>/dev/null
if [[ $? != 0 ]]; then
echo "No [$APPLICATION_INSIGHTS_NAME] Application Insights component exists in the [$RESOURCE_GROUP_NAME] resource group."
echo "Creating [$APPLICATION_INSIGHTS_NAME] Application Insights component in the [$RESOURCE_GROUP_NAME] resource group..."
# Create a workspace-based Application Insights component that stores the telemetry of the web app in the Log
# Analytics workspace
az monitor app-insights component create \
--app "$APPLICATION_INSIGHTS_NAME" \
--resource-group "$RESOURCE_GROUP_NAME" \
--location "$LOCATION" \
--application-type web \
--workspace "$LOG_ANALYTICS_NAME" \
--tags environment=test iac=az-cli \
--only-show-errors 1>/dev/null
if [[ $? == 0 ]]; then
echo "[$APPLICATION_INSIGHTS_NAME] Application Insights component created successfully in the [$RESOURCE_GROUP_NAME] resource group."
else
echo "Failed to create [$APPLICATION_INSIGHTS_NAME] Application Insights component in the [$RESOURCE_GROUP_NAME] resource group."
exit 1
fi
else
echo "[$APPLICATION_INSIGHTS_NAME] Application Insights component already exists in the [$RESOURCE_GROUP_NAME] resource group."
fi
# Get the connection string of the Application Insights component
echo "Getting the connection string of the [$APPLICATION_INSIGHTS_NAME] Application Insights component..."
APPLICATION_INSIGHTS_CONNECTION_STRING=$(az monitor app-insights component show \
--app "$APPLICATION_INSIGHTS_NAME" \
--resource-group "$RESOURCE_GROUP_NAME" \
--query connectionString \
--output tsv \
--only-show-errors)
if [[ -n $APPLICATION_INSIGHTS_CONNECTION_STRING ]]; then
echo "Connection string of the [$APPLICATION_INSIGHTS_NAME] Application Insights component retrieved successfully."
else
echo "Failed to retrieve the connection string of the [$APPLICATION_INSIGHTS_NAME] Application Insights component."
exit 1
fi
# Create the user-assigned managed identity the SQL server uses to reach the TDE protector key
echo "Creating user-assigned managed identity [$SQL_SERVER_IDENTITY_NAME]..."
az identity create \
--name "$SQL_SERVER_IDENTITY_NAME" \
--resource-group "$RESOURCE_GROUP_NAME" \
--location "$LOCATION" \
--only-show-errors 1>/dev/null
if [ $? -eq 0 ]; then
echo "User-assigned managed identity [$SQL_SERVER_IDENTITY_NAME] created successfully."
else
echo "Failed to create user-assigned managed identity [$SQL_SERVER_IDENTITY_NAME]."
exit 1
fi
SQL_SERVER_IDENTITY_ID=$(az identity show \
--name "$SQL_SERVER_IDENTITY_NAME" \
--resource-group "$RESOURCE_GROUP_NAME" \
--query "id" \
--output tsv)
SQL_SERVER_IDENTITY_PRINCIPAL_ID=$(az identity show \
--name "$SQL_SERVER_IDENTITY_NAME" \
--resource-group "$RESOURCE_GROUP_NAME" \
--query "principalId" \
--output tsv)
if [[ -z "$SQL_SERVER_IDENTITY_ID" || -z "$SQL_SERVER_IDENTITY_PRINCIPAL_ID" ]]; then
echo "Failed to retrieve the resource ID or principalId of [$SQL_SERVER_IDENTITY_NAME]"
exit 1
fi
# Create a sql server
echo "Checking if [$SQL_SERVER_NAME] sql server exists in the [$RESOURCE_GROUP_NAME] resource group..."
az sql server show \
--name $SQL_SERVER_NAME \
--resource-group $RESOURCE_GROUP_NAME \
--only-show-errors &>/dev/null
if [ $? -eq 0 ]; then
echo "[$SQL_SERVER_NAME] sql server already exists in the [$RESOURCE_GROUP_NAME] resource group."
else
echo "[$SQL_SERVER_NAME] sql server does not exist in the [$RESOURCE_GROUP_NAME] resource group. Proceeding to create it."
echo "Creating [$SQL_SERVER_NAME] sql server in the [$RESOURCE_GROUP_NAME] resource group..."
az sql server create \
--name $SQL_SERVER_NAME \
--resource-group $RESOURCE_GROUP_NAME \
--location $LOCATION \
--admin-user $ADMIN_USER \
--admin-password $ADMIN_PASSWORD \
--assign-identity \
--identity-type UserAssigned \
--user-assigned-identity-id "$SQL_SERVER_IDENTITY_ID" \
--primary-user-assigned-identity-id "$SQL_SERVER_IDENTITY_ID" \
--minimal-tls-version 1.2 \
--tags environment=test \
--only-show-errors 1>/dev/null
if [ $? == 0 ]; then
echo "[$SQL_SERVER_NAME] sql server successfully created in the [$RESOURCE_GROUP_NAME] resource group"
else
echo "Failed to create [$SQL_SERVER_NAME] sql server in the [$RESOURCE_GROUP_NAME] resource group"
exit 1
fi
fi
# Add firewall rule to allow all local network addresses (for testing/development)
echo "Creating firewall rule to allow all IP addresses..."
az sql server firewall-rule create \
--name $FIREWALL_RULE_NAME \
--resource-group $RESOURCE_GROUP_NAME \
--server $SQL_SERVER_NAME \
--start-ip-address 0.0.0.0 \
--end-ip-address 255.255.255.255 \
--only-show-errors 1>/dev/null
if [ $? -eq 0 ]; then
echo "Firewall rule [$FIREWALL_RULE_NAME] created successfully"
else
echo "Failed to create firewall rule"
exit 1
fi
# Create database if it does not exist
echo "Checking if [$SQL_DATABASE_NAME] database exists in the [$SQL_SERVER_NAME] sql server..."
az sql db show \
--name $SQL_DATABASE_NAME \
--resource-group $RESOURCE_GROUP_NAME \
--server $SQL_SERVER_NAME \
--only-show-errors &>/dev/null
if [ $? -eq 0 ]; then
echo "[$SQL_DATABASE_NAME] database already exists in the [$SQL_SERVER_NAME] sql server."
else
echo "Creating [$SQL_DATABASE_NAME] database with Provisioned compute model in the [$SQL_SERVER_NAME] sql server..."
az sql db create \
--name $SQL_DATABASE_NAME \
--resource-group $RESOURCE_GROUP_NAME \
--server $SQL_SERVER_NAME \
--service-objective S0 \
--compute-model Provisioned \
--zone-redundant false \
--tags environment=test \
--only-show-errors 1>/dev/null
if [ $? == 0 ]; then
echo "[$SQL_DATABASE_NAME] database with Provisioned compute model successfully created in the [$SQL_SERVER_NAME] sql server"
else
echo "Failed to create [$SQL_DATABASE_NAME] with Provisioned compute model database in the [$SQL_SERVER_NAME] sql server"
exit 1
fi
fi
# Get the resource id of the database
SQL_DATABASE_ID=$(az sql db show \
--name "$SQL_DATABASE_NAME" \
--server "$SQL_SERVER_NAME" \
--resource-group "$RESOURCE_GROUP_NAME" \
--query id \
--output tsv \
--only-show-errors)
if [[ -z "$SQL_DATABASE_ID" ]]; then
echo "Failed to retrieve the resource id of the [$SQL_DATABASE_NAME] database"
exit 1
fi
# Check whether the diagnostic settings for the database already exist
echo "Checking if [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$SQL_DATABASE_NAME] database already exist..."
az monitor diagnostic-settings show \
--name "$DIAGNOSTIC_SETTINGS_NAME" \
--resource "$SQL_DATABASE_ID" \
--only-show-errors &>/dev/null
if [[ $? != 0 ]]; then
echo "No [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$SQL_DATABASE_NAME] database actually exist"
echo "Creating [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$SQL_DATABASE_NAME] database..."
# Create the diagnostic settings for the database to send its resource logs and platform metrics to the Log
# Analytics workspace. The database writes its logs to the AzureDiagnostics table: none of its categories has a
# resource-specific table. The audit category (SQLSecurityAuditEvents) gets a diagnostic setting of its own below.
# Its metrics come in three categories, all enabled: Basic, InstanceAndAppAdvanced, and WorkloadManagement, which
# groups the metrics of dedicated SQL pools and so exports none for this database.
az monitor diagnostic-settings create \
--name "$DIAGNOSTIC_SETTINGS_NAME" \
--resource "$SQL_DATABASE_ID" \
--workspace "$LOG_ANALYTICS_NAME" \
--logs '[
{"category": "SQLInsights", "enabled": true},
{"category": "AutomaticTuning", "enabled": true},
{"category": "QueryStoreRuntimeStatistics", "enabled": true},
{"category": "QueryStoreWaitStatistics", "enabled": true},
{"category": "Errors", "enabled": true},
{"category": "DatabaseWaitStatistics", "enabled": true},
{"category": "Timeouts", "enabled": true},
{"category": "Blocks", "enabled": true},
{"category": "Deadlocks", "enabled": true}
]' \
--metrics '[
{"category": "Basic", "enabled": true},
{"category": "InstanceAndAppAdvanced", "enabled": true},
{"category": "WorkloadManagement", "enabled": true}
]' \
--only-show-errors 1>/dev/null
if [[ $? == 0 ]]; then
echo "[$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$SQL_DATABASE_NAME] database successfully created"
else
echo "Failed to create [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$SQL_DATABASE_NAME] database"
exit 1
fi
else
echo "[$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$SQL_DATABASE_NAME] database already exist"
fi
# Turn on the auditing of the database with Azure Monitor as its target. The Azure CLI keeps the audit category
# (SQLSecurityAuditEvents) in a diagnostic setting of its own, named SQLSecurityAuditEvents_<id>, which it creates the
# first time and updates afterwards: the records of each batch run on the database and of each sign-in reach the
# workspace.
echo "Enabling the auditing of the [$SQL_DATABASE_NAME] database to the [$LOG_ANALYTICS_NAME] Log Analytics workspace..."
az sql db audit-policy update \
--name "$SQL_DATABASE_NAME" \
--server "$SQL_SERVER_NAME" \
--resource-group "$RESOURCE_GROUP_NAME" \
--state Enabled \
--log-analytics-target-state Enabled \
--log-analytics-workspace-resource-id "$LOG_ANALYTICS_ID" \
--only-show-errors 1>/dev/null
if [[ $? == 0 ]]; then
echo "Auditing of the [$SQL_DATABASE_NAME] database to the [$LOG_ANALYTICS_NAME] Log Analytics workspace successfully enabled"
else
echo "Failed to enable the auditing of the [$SQL_DATABASE_NAME] database"
exit 1
fi
# Retrieve the fullyQualifiedDomainName of the SQL server
echo "Retrieving the fullyQualifiedDomainName of the [$SQL_SERVER_NAME] SQL server..."
SQL_SERVER_FQDN=$(az sql server show \
--name "$SQL_SERVER_NAME" \
--resource-group "$RESOURCE_GROUP_NAME" \
--query "fullyQualifiedDomainName" \
--output tsv)
if [ -z "$SQL_SERVER_FQDN" ]; then
echo "Failed to retrieve the fullyQualifiedDomainName of the SQL server"
exit 1
fi
#if [[ $ENVIRONMENT == "LocalStack" ]]; then
# MSSQL_HOST_PORT=$(docker ps --filter "ancestor=mcr.microsoft.com/mssql/server:2022-latest" --format "{{.Ports}}" | grep -oP '0\.0\.0\.0:\K[0-9]+(?=->1433)' | head -1)
# if [ -n "$MSSQL_HOST_PORT" ]; then
# SQL_SERVER_FQDN_WITH_PORT="127.0.0.1,$MSSQL_HOST_PORT"
# echo "Using local SQL Server at [$SQL_SERVER_FQDN_WITH_PORT]"
# fi
#fi
# Create server-level login
echo "Creating login [$DATABASE_USER_NAME] at server level..."
sqlcmd -S "$SQL_SERVER_FQDN" \
-d master \
-U "$ADMIN_USER" \
-P "$ADMIN_PASSWORD" \
-N -C \
-Q "IF NOT EXISTS (SELECT * FROM sys.sql_logins WHERE name = '$DATABASE_USER_NAME')
CREATE LOGIN [$DATABASE_USER_NAME] WITH PASSWORD = '$DATABASE_USER_PASSWORD';" \
-V 1
if [ $? -eq 0 ]; then
echo "Login [$DATABASE_USER_NAME] created successfully"
else
echo "Failed to create login [$DATABASE_USER_NAME]"
exit 1
fi
# Create database user
echo "Creating user [$DATABASE_USER_NAME] in database [$SQL_DATABASE_NAME]..."
sqlcmd -S "$SQL_SERVER_FQDN" \
-d "$SQL_DATABASE_NAME" \
-U "$ADMIN_USER" \
-P "$ADMIN_PASSWORD" \
-N -C \
-Q "IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '$DATABASE_USER_NAME')
CREATE USER [$DATABASE_USER_NAME] FOR LOGIN [$DATABASE_USER_NAME];" \
-V 1
if [ $? -eq 0 ]; then
echo "User [$DATABASE_USER_NAME] created successfully in database [$SQL_DATABASE_NAME]"
else
echo "Failed to create user [$DATABASE_USER_NAME]"
exit 1
fi
# Grant permissions including DDL rights
echo "Granting permissions to [$DATABASE_USER_NAME]..."
sqlcmd -S "$SQL_SERVER_FQDN" \
-d "$SQL_DATABASE_NAME" \
-U "$ADMIN_USER" \
-P "$ADMIN_PASSWORD" \
-N -C \
-Q "ALTER ROLE db_datareader ADD MEMBER [$DATABASE_USER_NAME];
ALTER ROLE db_datawriter ADD MEMBER [$DATABASE_USER_NAME];
ALTER ROLE db_ddladmin ADD MEMBER [$DATABASE_USER_NAME];" \
-V 1
if [ $? -eq 0 ]; then
echo "Permissions granted successfully to [$DATABASE_USER_NAME]"
else
echo "Failed to grant permissions to [$DATABASE_USER_NAME]"
exit 1
fi
# Test connection
echo "Testing connection with user [$DATABASE_USER_NAME]..."
sqlcmd -S "$SQL_SERVER_FQDN" \
-d "$SQL_DATABASE_NAME" \
-U "$DATABASE_USER_NAME" \
-P "$DATABASE_USER_PASSWORD" \
-N -C \
-Q "SELECT SYSTEM_USER AS CurrentUser, DB_NAME() AS CurrentDatabase, GETDATE() AS CurrentTime;" \
-V 1
if [ $? -eq 0 ]; then
echo "Connection test successful with user [$DATABASE_USER_NAME]"
else
echo "Connection test failed with user [$DATABASE_USER_NAME]"
exit 1
fi
# Create table
echo "Creating test [Products] table..."
sqlcmd -S "$SQL_SERVER_FQDN" \
-d "$SQL_DATABASE_NAME" \
-U "$DATABASE_USER_NAME" \
-P "$DATABASE_USER_PASSWORD" \
-N -C \
-Q "IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Activities' AND schema_id = SCHEMA_ID('dbo'))
CREATE TABLE dbo.Activities (
-- Primary Key: UNIQUEIDENTIFIER with a default of a new sequential GUID (best for indexing)
id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
-- Username field
username VARCHAR(32) NOT NULL,
-- Description of the activity
activity VARCHAR(128) NOT NULL,
-- Timestamp of the activity
timestamp DATETIME NOT NULL
);" \
-V 1
if [ $? -eq 0 ]; then
echo "Test [Activities] table created successfully"
else
echo "Failed to create test [Activities] table"
exit 1
fi
# Insert data
echo "Inserting test data into [Activities] table..."
sqlcmd -S "$SQL_SERVER_FQDN" \
-d "$SQL_DATABASE_NAME" \
-U "$DATABASE_USER_NAME" \
-P "$DATABASE_USER_PASSWORD" \
-N -C \
-Q "INSERT INTO Activities (username, activity, timestamp)
VALUES
('paolo', 'Go to Paris', GETDATE()),
('paolo', 'Go to London', GETDATE()),
('paolo', 'Go to Mexico', GETDATE());" \
-V 1
if [ $? -eq 0 ]; then
echo "Test data inserted successfully into [Activities] table"
else
echo "Failed to insert test data into [Activities] table"
exit 1
fi
# Query data
echo "Querying test data from [Activities] table..."
sqlcmd -S "$SQL_SERVER_FQDN" \
-d "$SQL_DATABASE_NAME" \
-U "$DATABASE_USER_NAME" \
-P "$DATABASE_USER_PASSWORD" \
-N -C \
-Q "SELECT * FROM Activities;" \
-V 1
if [ $? -eq 0 ]; then
echo "Test data queried successfully from [Activities] table"
else
echo "Failed to query test data from [Activities] table"
exit 1
fi
# Create App Service Plan
echo "Creating App Service Plan [$APP_SERVICE_PLAN_NAME]..."
az appservice plan create \
--resource-group "$RESOURCE_GROUP_NAME" \
--name "$APP_SERVICE_PLAN_NAME" \
--location "$LOCATION" \
--sku "$APP_SERVICE_PLAN_SKU" \
--is-linux \
--only-show-errors 1>/dev/null
if [ $? -eq 0 ]; then
echo "App Service Plan [$APP_SERVICE_PLAN_NAME] created successfully."
else
echo "Failed to create App Service Plan [$APP_SERVICE_PLAN_NAME]."
exit 1
fi
# Create the web app
echo "Creating web app [$WEB_APP_NAME]..."
az webapp create \
--resource-group "$RESOURCE_GROUP_NAME" \
--plan "$APP_SERVICE_PLAN_NAME" \
--name "$WEB_APP_NAME" \
--runtime "$RUNTIME:$RUNTIME_VERSION" \
--assign-identity \
--only-show-errors 1>/dev/null
if [ $? -eq 0 ]; then
echo "Web app [$WEB_APP_NAME] created successfully."
else
echo "Failed to create web app [$WEB_APP_NAME]."
exit 1
fi
# Get Web App principal ID
PRINCIPAL_ID=$(az webapp identity show \
--name "$WEB_APP_NAME" \
--resource-group "$RESOURCE_GROUP_NAME" \
--query "principalId" \
--output tsv)
if [ -z "$PRINCIPAL_ID" ]; then
echo "Failed to retrieve principalId for web app [$WEB_APP_NAME]"
exit 1
fi
# Create Key Vault
echo "Creating Key Vault [$KEY_VAULT_NAME]..."
az keyvault create \
--name "$KEY_VAULT_NAME" \
--resource-group "$RESOURCE_GROUP_NAME" \
--location "$LOCATION" \
--enable-rbac-authorization false \
--enable-purge-protection true \
--retention-days 7 \
--only-show-errors 1>/dev/null
if [ $? -eq 0 ]; then
echo "Key Vault [$KEY_VAULT_NAME] created successfully."
else
echo "Failed to create Key Vault [$KEY_VAULT_NAME]."
exit 1
fi
# Get the resource id of the key vault
KEY_VAULT_ID=$(az keyvault show \
--name "$KEY_VAULT_NAME" \
--resource-group "$RESOURCE_GROUP_NAME" \
--query id \
--output tsv \
--only-show-errors)
if [[ -z "$KEY_VAULT_ID" ]]; then
echo "Failed to retrieve the resource id of the [$KEY_VAULT_NAME] key vault"
exit 1
fi
# Check whether the diagnostic settings for the key vault already exist
echo "Checking if [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$KEY_VAULT_NAME] key vault already exist..."
az monitor diagnostic-settings show \
--name "$DIAGNOSTIC_SETTINGS_NAME" \
--resource "$KEY_VAULT_ID" \
--only-show-errors &>/dev/null
if [[ $? != 0 ]]; then
echo "No [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$KEY_VAULT_NAME] key vault actually exist"
echo "Creating [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$KEY_VAULT_NAME] key vault..."
# Create the diagnostic settings for the key vault to send logs to the Log Analytics workspace, in the
# resource-specific AZKVAuditLogs and AZKVPolicyEvaluationDetailsLogs tables rather than AzureDiagnostics
az monitor diagnostic-settings create \
--name "$DIAGNOSTIC_SETTINGS_NAME" \
--resource "$KEY_VAULT_ID" \
--workspace "$LOG_ANALYTICS_NAME" \
--export-to-resource-specific true \
--logs '[
{"category": "AuditEvent", "enabled": true},
{"category": "AzurePolicyEvaluationDetails", "enabled": true}
]' \
--metrics '[
{"category": "AllMetrics", "enabled": true}
]' \
--only-show-errors 1>/dev/null
if [[ $? == 0 ]]; then
echo "[$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$KEY_VAULT_NAME] key vault successfully created"
else
echo "Failed to create [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$KEY_VAULT_NAME] key vault"
exit 1
fi
else
echo "[$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$KEY_VAULT_NAME] key vault already exist"
fi
# Assign access policy to Web App managed identity
echo "Assigning Key Vault access policy to Web App..."
az keyvault set-policy \
--name "$KEY_VAULT_NAME" \
--object-id "$PRINCIPAL_ID" \
--secret-permissions get \
--certificate-permissions get \
--only-show-errors 1>/dev/null
if [ $? -eq 0 ]; then
echo "Key Vault access policy assigned successfully."
else
echo "Failed to assign Key Vault access policy."
exit 1
fi
# Build connection string
SQL_CONNECTION_STRING="Server=tcp:${SQL_SERVER_FQDN},1433;Database=${SQL_DATABASE_NAME};User ID=${DATABASE_USER_NAME};Password=${DATABASE_USER_PASSWORD};Encrypt=yes;TrustServerCertificate=yes;Connection Timeout=30;"
# Create secret
echo "Creating secret [$SECRET_NAME] in Key Vault..."
az keyvault secret set \
--vault-name "$KEY_VAULT_NAME" \
--name "$SECRET_NAME" \
--value "$SQL_CONNECTION_STRING" \
--only-show-errors 1>/dev/null
if [ $? -eq 0 ]; then
echo "Secret [$SECRET_NAME] created successfully."
else
echo "Failed to create secret [$SECRET_NAME]."
exit 1
fi
# Create certificate in Key Vault
echo "Creating certificate [$CERT_NAME] in Key Vault [$KEY_VAULT_NAME]..."
az keyvault certificate create \
--vault-name "$KEY_VAULT_NAME" \
--name "$CERT_NAME" \
--policy '{
"issuerParameters": {"name": "Self"},
"keyProperties": {"exportable": true, "keySize": 2048, "keyType": "RSA", "reuseKey": false},
"secretProperties": {"contentType": "application/x-pkcs12"},
"x509CertificateProperties": {"subject": "CN=sample-web-app-sql", "validityInMonths": 12}
}' \
--only-show-errors
if [ $? -eq 0 ]; then
echo "Certificate [$CERT_NAME] created successfully in Key Vault [$KEY_VAULT_NAME]."
else
echo "Failed to create certificate [$CERT_NAME] in Key Vault [$KEY_VAULT_NAME]."
exit 1
fi
# Assign access policy to the SQL server managed identity
echo "Assigning Key Vault access policy to the SQL server identity [$SQL_SERVER_IDENTITY_NAME]..."
az keyvault set-policy \
--name "$KEY_VAULT_NAME" \
--object-id "$SQL_SERVER_IDENTITY_PRINCIPAL_ID" \
--key-permissions get wrapKey unwrapKey \
--only-show-errors 1>/dev/null
if [ $? -eq 0 ]; then
echo "Key Vault access policy for [$SQL_SERVER_IDENTITY_NAME] assigned successfully."
else
echo "Failed to assign Key Vault access policy for [$SQL_SERVER_IDENTITY_NAME]."
exit 1
fi
# Create the RSA key that protects the database encryption keys of the SQL server
echo "Creating key [$TDE_KEY_NAME] in Key Vault [$KEY_VAULT_NAME]..."
TDE_KEY_ID=$(az keyvault key create \
--vault-name "$KEY_VAULT_NAME" \
--name "$TDE_KEY_NAME" \
--kty RSA \
--size 2048 \
--ops wrapKey unwrapKey \
--query "key.kid" \
--output tsv \
--only-show-errors)
if [ -n "$TDE_KEY_ID" ]; then
echo "Key [$TDE_KEY_ID] created successfully."
else
echo "Failed to create key [$TDE_KEY_NAME] in Key Vault [$KEY_VAULT_NAME]."
exit 1
fi
# Register the key on the SQL server and make it the TDE protector. The generic az resource create
# is used because az sql server key create and az sql server tde-key set only accept key ids on the
# public Key Vault domains, which rejects the key ids the LocalStack emulator issues.
SQL_SERVER_ID=$(az sql server show \
--name "$SQL_SERVER_NAME" \
--resource-group "$RESOURCE_GROUP_NAME" \
--query "id" \
--output tsv)
SERVER_KEY_NAME="${KEY_VAULT_NAME}_${TDE_KEY_NAME}_${TDE_KEY_ID##*/}"
echo "Adding key [$TDE_KEY_NAME] to the [$SQL_SERVER_NAME] sql server..."
az resource create \
--id "$SQL_SERVER_ID/keys/$SERVER_KEY_NAME" \
--api-version 2023-08-01 \
--properties "{\"serverKeyType\": \"AzureKeyVault\", \"uri\": \"$TDE_KEY_ID\"}" \
--only-show-errors 1>/dev/null
if [ $? -eq 0 ]; then
echo "Key [$TDE_KEY_NAME] added successfully to the [$SQL_SERVER_NAME] sql server."
else
echo "Failed to add key [$TDE_KEY_NAME] to the [$SQL_SERVER_NAME] sql server."
exit 1
fi
echo "Setting key [$TDE_KEY_NAME] as the TDE protector of the [$SQL_SERVER_NAME] sql server..."
az resource create \
--id "$SQL_SERVER_ID/encryptionProtector/current" \
--api-version 2023-08-01 \
--properties "{\"serverKeyType\": \"AzureKeyVault\", \"serverKeyName\": \"$SERVER_KEY_NAME\", \"autoRotationEnabled\": true}" \
--only-show-errors 1>/dev/null
if [ $? -eq 0 ]; then
echo "Key [$TDE_KEY_NAME] set successfully as the TDE protector of the [$SQL_SERVER_NAME] sql server."
else
echo "Failed to set key [$TDE_KEY_NAME] as the TDE protector of the [$SQL_SERVER_NAME] sql server."
exit 1
fi
# Get Key Vault URI
echo "Retrieving Key Vault URI..."
KEYVAULT_URI=$(az keyvault show \
--name "$KEY_VAULT_NAME" \
--resource-group "$RESOURCE_GROUP_NAME" \
--query "properties.vaultUri" \
--output tsv)
if [ -z "$KEYVAULT_URI" ]; then
echo "Failed to retrieve Key Vault URI."
exit 1
fi
echo "Key Vault URI: [$KEYVAULT_URI]"
# Set web app settings
# Pass Key Vault name and secret name as app settings.
# The .NET SDK will retrieve the actual connection string value from Key Vault.
echo "Setting web app settings for [$WEB_APP_NAME]..."
az webapp config appsettings set \
--name "$WEB_APP_NAME" \
--resource-group "$RESOURCE_GROUP_NAME" \
--settings \
SCM_DO_BUILD_DURING_DEPLOYMENT='true' \
ENABLE_ORYX_BUILD='true' \
KEY_VAULT_NAME="$KEY_VAULT_NAME" \
SECRET_NAME="$SECRET_NAME" \
LOGIN_NAME="$LOGIN_NAME" \
KEYVAULT_URI="$KEYVAULT_URI" \
CERT_NAME="$CERT_NAME" \
APPLICATIONINSIGHTS_CONNECTION_STRING="$APPLICATION_INSIGHTS_CONNECTION_STRING" \
--only-show-errors 1>/dev/null
if [ $? -eq 0 ]; then
echo "Web app settings for [$WEB_APP_NAME] set successfully."
else
echo "Failed to set web app settings for [$WEB_APP_NAME]."
exit 1
fi
# Get the resource ids of the web app and of its App Service plan
WEB_APP_ID=$(az webapp show \
--name "$WEB_APP_NAME" \
--resource-group "$RESOURCE_GROUP_NAME" \
--query id \
--output tsv \
--only-show-errors)
APP_SERVICE_PLAN_ID=$(az appservice plan show \
--name "$APP_SERVICE_PLAN_NAME" \
--resource-group "$RESOURCE_GROUP_NAME" \
--query id \
--output tsv \
--only-show-errors)
if [[ -z $WEB_APP_ID || -z $APP_SERVICE_PLAN_ID ]]; then
echo "Failed to retrieve the resource ids of the [$WEB_APP_NAME] web app and of its App Service plan"
exit 1
fi
# Check whether the diagnostic settings for the web app already exist
echo "Checking if [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$WEB_APP_NAME] web app already exist..."
az monitor diagnostic-settings show \
--name "$DIAGNOSTIC_SETTINGS_NAME" \
--resource "$WEB_APP_ID" \
--only-show-errors &>/dev/null
if [[ $? != 0 ]]; then
echo "No [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$WEB_APP_NAME] web app actually exist"
echo "Creating [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$WEB_APP_NAME] web app..."
# Create the diagnostic settings for the web app to send its logs and metrics to the Log Analytics workspace
az monitor diagnostic-settings create \
--name "$DIAGNOSTIC_SETTINGS_NAME" \
--resource "$WEB_APP_ID" \
--workspace "$LOG_ANALYTICS_NAME" \
--logs '[
{"category": "AppServiceHTTPLogs", "enabled": true},
{"category": "AppServiceConsoleLogs", "enabled": true},
{"category": "AppServiceAppLogs", "enabled": true},
{"category": "AppServiceAuditLogs", "enabled": true},
{"category": "AppServiceIPSecAuditLogs", "enabled": true},
{"category": "AppServicePlatformLogs", "enabled": true},
{"category": "AppServiceAuthenticationLogs", "enabled": true}
]' \
--metrics '[
{"category": "AllMetrics", "enabled": true}
]' \
--only-show-errors 1>/dev/null
if [[ $? == 0 ]]; then
echo "[$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$WEB_APP_NAME] web app successfully created"
else
echo "Failed to create [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$WEB_APP_NAME] web app"
exit 1
fi
else
echo "[$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$WEB_APP_NAME] web app already exist"
fi
# Check whether the diagnostic settings for the App Service plan already exist
echo "Checking if [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$APP_SERVICE_PLAN_NAME] App Service plan already exist..."
az monitor diagnostic-settings show \
--name "$DIAGNOSTIC_SETTINGS_NAME" \
--resource "$APP_SERVICE_PLAN_ID" \
--only-show-errors &>/dev/null
if [[ $? != 0 ]]; then
echo "No [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$APP_SERVICE_PLAN_NAME] App Service plan actually exist"
echo "Creating [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$APP_SERVICE_PLAN_NAME] App Service plan..."
# Create the diagnostic settings for the App Service plan to send its metrics to the Log Analytics workspace
az monitor diagnostic-settings create \
--name "$DIAGNOSTIC_SETTINGS_NAME" \
--resource "$APP_SERVICE_PLAN_ID" \
--workspace "$LOG_ANALYTICS_NAME" \
--metrics '[
{"category": "AllMetrics", "enabled": true}
]' \
--only-show-errors 1>/dev/null
if [[ $? == 0 ]]; then
echo "[$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$APP_SERVICE_PLAN_NAME] App Service plan successfully created"
else
echo "Failed to create [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$APP_SERVICE_PLAN_NAME] App Service plan"
exit 1
fi
else
echo "[$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$APP_SERVICE_PLAN_NAME] App Service plan already exist"
fi
# Check whether the diagnostic settings for the Log Analytics workspace already exist
echo "Checking if [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$LOG_ANALYTICS_NAME] Log Analytics workspace already exist..."
az monitor diagnostic-settings show \
--name "$DIAGNOSTIC_SETTINGS_NAME" \
--resource "$LOG_ANALYTICS_ID" \
--only-show-errors &>/dev/null
if [[ $? != 0 ]]; then
echo "No [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$LOG_ANALYTICS_NAME] Log Analytics workspace actually exist"
echo "Creating [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$LOG_ANALYTICS_NAME] Log Analytics workspace..."
# Create the diagnostic settings that send the audit records of the queries run on the workspace (LAQueryLogs) and its platform metrics to the workspace itself
az monitor diagnostic-settings create \
--name "$DIAGNOSTIC_SETTINGS_NAME" \
--resource "$LOG_ANALYTICS_ID" \
--workspace "$LOG_ANALYTICS_NAME" \
--logs '[
{"category": "Audit", "enabled": true}
]' \
--metrics '[
{"category": "AllMetrics", "enabled": true}
]' \
--only-show-errors 1>/dev/null
if [[ $? == 0 ]]; then
echo "[$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$LOG_ANALYTICS_NAME] Log Analytics workspace successfully created"
else
echo "Failed to create [$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$LOG_ANALYTICS_NAME] Log Analytics workspace"
exit 1
fi
else
echo "[$DIAGNOSTIC_SETTINGS_NAME] diagnostic settings for the [$LOG_ANALYTICS_NAME] Log Analytics workspace already exist"
fi
if [[ $DEPLOY_APP -eq 0 ]]; then
echo "Skipping web app deployment as DEPLOY_APP flag is set to 0."
exit 0
fi
# Change current directory to source folder
cd "../src" || exit
# Remove any existing zip package of the web app
if [ -f "$ZIPFILE" ]; then
rm "$ZIPFILE"
fi
# Create the zip package of the web app
echo "Creating zip package of the web app..."
zip -r "$ZIPFILE" . -x "bin/*" "obj/*" "publish/*" "*.zip"
# Deploy the web app
echo "Deploying web app [$WEB_APP_NAME] with zip file [$ZIPFILE]..."
az webapp deploy \
--resource-group "$RESOURCE_GROUP_NAME" \
--name "$WEB_APP_NAME" \
--src-path "$ZIPFILE" \
--type zip \
--async true 1>/dev/null
if [ $? -eq 0 ]; then
echo "Web app [$WEB_APP_NAME] created successfully."
else
echo "Failed to create web app [$WEB_APP_NAME]."
exit 1
fi
# Get web app URL
WEB_APP_URL=$(az webapp show \
--name "$WEB_APP_NAME" \
--resource-group "$RESOURCE_GROUP_NAME" \
--query "defaultHostName" \
--output tsv)
# Remove the zip package of the web app
if [ -f "$ZIPFILE" ]; then
rm "$ZIPFILE"
fi