I am using Terraform to build an Azure DB and set the correct Azure AD Admin etc - all working well.
I now need to create
CREATE LOGIN [XXX-XXX] FROM EXTERNAL PROVIDER;
CREATE USER [XXX-XXX] FOR LOGIN [XXX-XXX];
ALTER ROLE db_datareader ADD MEMBER [XXX-XXX]
Any ideas if this is possible within Terraform - thinking its the easiest way as the user is already authorised to create the database.
Its not possible to directly run the commands that you have mentioned in the question but you can use Invoke-sqlcmd and authenticate with your AAD admin credentials and run the commands .
I tested the scenario with the below code :
provider "azurerm" {
features {}
}
data "azurerm_client_config" "current" {}
resource "azurerm_resource_group" "example" {
name = "example-resources"
location = "West Europe"
}
resource "azurerm_sql_server" "example" {
name = "ansumansqlserver"
resource_group_name = azurerm_resource_group.example.name
location = azurerm_resource_group.example.location
version = "12.0"
administrator_login = "admin"
administrator_login_password = "password"
tags = {
environment = "production"
}
}
resource "azurerm_storage_account" "example" {
name = "ansumansacc"
resource_group_name = azurerm_resource_group.example.name
location = azurerm_resource_group.example.location
account_tier = "Standard"
account_replication_type = "LRS"
}
resource "azurerm_sql_database" "example" {
name = "ansumansqldatabase"
resource_group_name = azurerm_resource_group.example.name
location = azurerm_resource_group.example.location
server_name = azurerm_sql_server.example.name
extended_auditing_policy {
storage_endpoint = azurerm_storage_account.example.primary_blob_endpoint
storage_account_access_key = azurerm_storage_account.example.primary_access_key
storage_account_access_key_is_secondary = true
retention_in_days = 6
}
tags = {
environment = "production"
}
}
resource "azurerm_sql_active_directory_administrator" "example" {
server_name = azurerm_sql_server.example.name
resource_group_name = azurerm_resource_group.example.name
login = "sqladmin"
tenant_id = data.azurerm_client_config.current.tenant_id
object_id = data.azurerm_client_config.current.object_id
}
## creating Login in master database first
resource "null_resource" "master" {
provisioner "local-exec" {
command = <<EOT
Set-AzContext -SubscriptionId "<SubscriptionID>"
$token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
Invoke-SqlCmd -ServerInstance ${azurerm_sql_server.example.fully_qualified_domain_name} -Database master -AccessToken $token -Query "CREATE LOGIN [[email protected]] FROM EXTERNAL PROVIDER"
EOT
interpreter = ["PowerShell", "-Command"]
}
depends_on = [
azurerm_sql_active_directory_administrator.example,
azurerm_sql_database.example
]
}
## creating the user from the login created in master and assigning role
resource "null_resource" "database" {
provisioner "local-exec" {
command = <<EOT
Set-AzContext -SubscriptionId "<SubscriptionID>"
$token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
$query= @'
CREATE USER [AJAY] FOR LOGIN [[email protected]];
GO
ALTER ROLE [db_datareader] ADD MEMBER [AJAY];
GO
'@
Invoke-SqlCmd -ServerInstance ${azurerm_sql_server.example.fully_qualified_domain_name} -Database ${azurerm_sql_database.example.name} -AccessToken $token -Query $query
EOT
interpreter = ["PowerShell", "-Command"]
}
depends_on = [
null_resource.master
]
}
Output:
Note: Please make sure to have Azure Powershell Module and SQLServer Powershell Module.
