Home
%3CLINGO-SUB%20id%3D%22lingo-sub-369061%22%20slang%3D%22en-US%22%3EUpdated%20ARM%20Template%20to%20deploy%20server%20with%20Auditing%20and%20Threat%20Detection%20turned%20ON%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-369061%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Jun%2026%2C%202018%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20%5B2018-07-31%5D%20Note%3A%20new%20template%20version%20is%20now%20available.%20It%20includes%203%20changes%3A%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20There%20is%20no%20reason%20to%20enable%20both%20server%20policy%20and%20database%20policy.%20It%20is%20enough%20to%20enable%20only%20server%20policy.%20%3C%2FSTRONG%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20No%20need%20to%20supply%20storage%20account%20for%20Threat%20Detection%20policy.%20It%20is%20needed%20only%20for%20auditing%20%3C%2FSTRONG%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20Use%20the%20updated%20API%20version%20for%20auditing%20and%20Threat%20Detection%20(2017-03-01-preview)%20%3C%2FSTRONG%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20I%20would%20like%20to%20share%20an%20example%20of%20a%20template%20that%26nbsp%3Bcan%20be%20used%20to%20deploy%20server%20with%20multiple%20databases%20and%20to%20turn%20ON%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-auditing-get-started%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20Auditing%20%3C%2FA%3E%20and%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-threat-detection-get-started%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20Threat%20Detection%20%3C%2FA%3E%20at%20server%20and%20individual%20database%20levels.%26nbsp%3B%20Please%20be%20aware%20that%20when%20server%20-%20level%20auditing%20is%20enabled%2C%20it%20is%20applied%20to%20all%20databases%20on%20this%20server.%20You%20can%20also%20enable%20database%20-%20level%20auditing%2C%20for%26nbsp%3Bexample%2C%20if%26nbsp%3Bdifferent%20storage%20account%20or%20retention%20period%20should%20be%20used%20for%20a%20specific%20database.%20For%20more%20details%20about%20server%20and%20database%20-%20level%20auditing%20policy%20please%20refer%20to%20the%20following%20article%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-auditing%23subheading-8%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20Define%20server-level%20vs.%20database-level%20auditing%20policy%20%3C%2FA%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22%24schema%22%3A%20%22%3CA%20href%3D%22https%3A%2F%2Fschema.management.azure.com%2Fschemas%2F2015-01-01%2FdeploymentTemplate.json%23%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fschema.management.azure.com%2Fschemas%2F2015-01-01%2FdeploymentTemplate.json%23%3C%2FA%3E%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22contentVersion%22%3A%20%221.0.0.0%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22parameters%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22databaseserver%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22type%22%3A%20%22string%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22databaselist%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22type%22%3A%20%22array%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22metadata%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22firewallruleList%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22type%22%3A%20%22array%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22metadata%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22sqladminpassword%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22type%22%3A%20%22securestring%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22emailaddresses%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22type%22%3A%20%22array%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22metadata%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22variables%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22databaseServerName%22%3A%20%22%5BtoLower(parameters('databaseServer'))%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22databaseServerLocation%22%3A%20%22West%20US%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22defaultSecondaryLocation%22%3A%20%22East%20US%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22databaseServerAdminLogin%22%3A%20%22Standard%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22databaseServerAdminLoginPassword%22%3A%20%22%5Bparameters('sqlAdminPassword')%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22storageAccountName%22%3A%20%22%5BtoLower(parameters('databaseServer'))%5D%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22resources%22%3A%20%5B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22type%22%3A%20%22Microsoft.Storage%2FstorageAccounts%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22name%22%3A%20%22%5Bvariables('storageAccountName')%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22apiVersion%22%3A%20%222016-01-01%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22location%22%3A%20%22%5BresourceGroup().location%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22sku%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22name%22%3A%20%22Standard_LRS%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22kind%22%3A%20%22Storage%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22properties%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22name%22%3A%20%22%5Bvariables('databaseServerName')%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22type%22%3A%20%22Microsoft.Sql%2Fservers%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22location%22%3A%20%22%5Bvariables('databaseServerLocation')%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22apiVersion%22%3A%20%222014-04-01-preview%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22dependsOn%22%3A%20%5B%20%5D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22tags%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22DisplayName%22%3A%20%22%5Bvariables('databaseServerName')%5D%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22properties%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22administratorLogin%22%3A%20%22%5Bvariables('databaseServerAdminLogin')%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22administratorLoginPassword%22%3A%20%22%5Bvariables('databaseServerAdminLoginPassword')%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22version%22%3A%20%2212.0%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22resources%22%3A%20%5B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22apiVersion%22%3A%20%222017-03-01-preview%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22type%22%3A%20%22auditingSettings%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22name%22%3A%20%22DefaultAuditingSettings%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22dependsOn%22%3A%20%5B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22%5Bvariables('databaseServerName')%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22%5Bconcat('Microsoft.Storage%2FstorageAccounts%2F'%2C%20variables('storageAccountName'))%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22DatabaseLoop%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22properties%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22State%22%3A%20%22Enabled%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22storageEndpoint%22%3A%20%22%5Bconcat('https%3A%2F%2F'%2C%20variables%20('storageAccountName')%2C%20'.blob.core.windows.net%2F')%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22storageAccountAccessKey%22%3A%20%22%5BlistKeys(resourceId('Microsoft.Storage%2FstorageAccounts'%2C%20variables('storageAccountName'))%2C%20providers('Microsoft.Storage'%2C%20'storageAccounts').apiVersions%5B0%5D).keys%5B0%5D.value%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22storageAccountSubscriptionId%22%3A%20%22%5Bsubscription().subscriptionId%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22retentionDays%22%3A%200%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22auditActionsAndGroups%22%3A%20null%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22isStorageSecondaryKeyInUse%22%3A%20false%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22apiVersion%22%3A%20%222017-03-01-preview%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22type%22%3A%20%22securityAlertPolicies%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22name%22%3A%20%22DefaultSecurityAlert%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22dependsOn%22%3A%20%5B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22%5Bvariables('databaseServerName')%5D%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22properties%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22state%22%3A%20%22Enabled%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22disabledAlerts%22%3A%20%5B%5D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22emailAddresses%22%3A%20%22%5Bparameters('emailaddresses')%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22emailAccountAdmins%22%3A%20true%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22type%22%3A%20%22Microsoft.Sql%2Fservers%2Ffirewallrules%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22name%22%3A%20%22%5Bconcat(variables('databaseServerName')%2C%20'%2F'%2C%20parameters('firewallRuleList')%5BcopyIndex()%5D.name)%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22apiVersion%22%3A%20%222014-04-01-preview%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22location%22%3A%20%22%5Bvariables('databaseServerLocation')%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22properties%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22startIpAddress%22%3A%20%22%5Bparameters('firewallRuleList')%5BcopyIndex()%5D.startIpAddress%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22endIpAddress%22%3A%20%22%5Bparameters('firewallRuleList')%5BcopyIndex()%5D.endIpAddress%5D%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22resources%22%3A%20%5B%20%5D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22dependsOn%22%3A%20%5B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22%5Bconcat('Microsoft.Sql%2Fservers%2F'%2C%20variables('databaseServerName'))%5D%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22copy%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22name%22%3A%20%22FirewallLoop%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22count%22%3A%20%22%5Blength(parameters('firewallRuleList'))%5D%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22apiVersion%22%3A%20%222014-04-01-preview%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22type%22%3A%20%22Microsoft.Sql%2Fservers%2Fdatabases%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22copy%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22name%22%3A%20%22DatabaseLoop%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22count%22%3A%20%22%5Blength(parameters('databaseList'))%5D%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22dependsOn%22%3A%20%5B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22%5Bconcat('Microsoft.Sql%2Fservers%2F'%2C%20variables('databaseServerName'))%5D%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22location%22%3A%20%22%5Bvariables('databaseServerLocation')%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22name%22%3A%20%22%5Bconcat(variables('databaseServerName')%2C%20'%2F'%2C%20string(parameters('databaseList')%5BcopyIndex()%5D.databaseName))%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22properties%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22collation%22%3A%20%22%5Bparameters('databaseList')%5BcopyIndex()%5D.collation%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22edition%22%3A%20%22%5Bparameters('databaseList')%5BcopyIndex()%5D.databaseEdition%5D%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22maxSizeBytes%22%3A%20%22%5Bparameters('databaseList')%5BcopyIndex()%5D.maxSizeBytes%5D%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22tags%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22DisplayName%22%3A%20%22%5Bvariables('databaseServerName')%5D%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22resources%22%3A%20%5B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22name%22%3A%20%22current%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22type%22%3A%20%22transparentDataEncryption%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22dependsOn%22%3A%20%5B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22%5Bparameters('databaseList')%5BcopyIndex()%5D.databaseName%5D%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22location%22%3A%20null%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22apiVersion%22%3A%20%222014-04-01-preview%22%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22properties%22%3A%20%7B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22status%22%3A%20%22Enabled%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5D%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%22outputs%22%3A%20%7B%20%7D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Have%20a%26nbsp%3Bnice%20day!%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Olga%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-369061%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jun%2026%2C%202018%20%5B2018-07-31%5D%20Note%3A%20new%20template%20version%20is%20now%20available.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-369061%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Earm%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Earm%20template%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eauditing%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20Resource%20Manager%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20SQL%20DB%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EJson%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Ethreat%20detection%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Not applicable
First published on MSDN on Jun 26, 2018
[2018-07-31] Note: new template version is now available. It includes 3 changes:

  • There is no reason to enable both server policy and database policy. It is enough to enable only server policy.

  • No need to supply storage account for Threat Detection policy. It is needed only for auditing

  • Use the updated API version for auditing and Threat Detection (2017-03-01-preview)


I would like to share an example of a template that can be used to deploy server with multiple databases and to turn ON Auditing and Threat Detection at server and individual database levels.  Please be aware that when server - level auditing is enabled, it is applied to all databases on this server. You can also enable database - level auditing, for example, if different storage account or retention period should be used for a specific database. For more details about server and database - level auditing policy please refer to the following article: Define server-level vs. database-level auditing policy

{

"$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",



"contentVersion": "1.0.0.0",



"parameters": {



"databaseserver": {



"type": "string"

},

"databaselist": {



"type": "array",



"metadata": {

}

},

"firewallruleList": {



"type": "array",



"metadata": {

}

},

"sqladminpassword": {



"type": "securestring"

},

"emailaddresses": {



"type": "array",



"metadata": {

}

}

},

"variables": {



"databaseServerName": "[toLower(parameters('databaseServer'))]",



"databaseServerLocation": "West US",



"defaultSecondaryLocation": "East US",



"databaseServerAdminLogin": "Standard",



"databaseServerAdminLoginPassword": "[parameters('sqlAdminPassword')]",



"storageAccountName": "[toLower(parameters('databaseServer'))]"

},

"resources": [

{

"type": "Microsoft.Storage/storageAccounts",



"name": "[variables('storageAccountName')]",



"apiVersion": "2016-01-01",



"location": "[resourceGroup().location]",



"sku": {



"name": "Standard_LRS"

},

"kind": "Storage",



"properties": {

}

},

{

"name": "[variables('databaseServerName')]",



"type": "Microsoft.Sql/servers",



"location": "[variables('databaseServerLocation')]",



"apiVersion": "2014-04-01-preview",



"dependsOn": [ ],



"tags": {



"DisplayName": "[variables('databaseServerName')]"

},

"properties": {



"administratorLogin": "[variables('databaseServerAdminLogin')]",



"administratorLoginPassword": "[variables('databaseServerAdminLoginPassword')]",



"version": "12.0"

},

"resources": [

{

"apiVersion": "2017-03-01-preview",



"type": "auditingSettings",



"name": "DefaultAuditingSettings",



"dependsOn": [



"[variables('databaseServerName')]",



"[concat('Microsoft.Storage/storageAccounts/', variables('storageAccountName'))]",



"DatabaseLoop"

],

"properties": {



"State": "Enabled",



"storageEndpoint": "[concat('https://', variables ('storageAccountName'), '.blob.core.windows.net/')]",



"storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', variables('storageAccountName')), providers('Microsoft.Storage', 'storageAccounts').apiVersions[0]).keys[0].value]",



"storageAccountSubscriptionId": "[subscription().subscriptionId]",



"retentionDays": 0,



"auditActionsAndGroups": null,



"isStorageSecondaryKeyInUse": false

}

},

{

"apiVersion": "2017-03-01-preview",



"type": "securityAlertPolicies",



"name": "DefaultSecurityAlert",



"dependsOn": [



"[variables('databaseServerName')]"

],

"properties": {



"state": "Enabled",



"disabledAlerts": [],



"emailAddresses": "[parameters('emailaddresses')]",



"emailAccountAdmins": true

}

}

]

},

{

"type": "Microsoft.Sql/servers/firewallrules",



"name": "[concat(variables('databaseServerName'), '/', parameters('firewallRuleList')[copyIndex()].name)]",



"apiVersion": "2014-04-01-preview",



"location": "[variables('databaseServerLocation')]",



"properties": {



"startIpAddress": "[parameters('firewallRuleList')[copyIndex()].startIpAddress]",



"endIpAddress": "[parameters('firewallRuleList')[copyIndex()].endIpAddress]"

},

"resources": [ ],



"dependsOn": [



"[concat('Microsoft.Sql/servers/', variables('databaseServerName'))]"

],

"copy": {



"name": "FirewallLoop",



"count": "[length(parameters('firewallRuleList'))]"

}

},

{

"apiVersion": "2014-04-01-preview",



"type": "Microsoft.Sql/servers/databases",



"copy": {



"name": "DatabaseLoop",



"count": "[length(parameters('databaseList'))]"

},

"dependsOn": [



"[concat('Microsoft.Sql/servers/', variables('databaseServerName'))]"

],

"location": "[variables('databaseServerLocation')]",



"name": "[concat(variables('databaseServerName'), '/', string(parameters('databaseList')[copyIndex()].databaseName))]",



"properties": {



"collation": "[parameters('databaseList')[copyIndex()].collation]",



"edition": "[parameters('databaseList')[copyIndex()].databaseEdition]",



"maxSizeBytes": "[parameters('databaseList')[copyIndex()].maxSizeBytes]"

},

"tags": {



"DisplayName": "[variables('databaseServerName')]"

},

"resources": [

{

"name": "current",



"type": "transparentDataEncryption",



"dependsOn": [



"[parameters('databaseList')[copyIndex()].databaseName]"

],

"location": null,



"apiVersion": "2014-04-01-preview",



"properties": {



"status": "Enabled"

}

}

]

}

],

"outputs": { }

}



Have a nice day!

Olga