Powershell script for SQL Azure back up to Microsoft Azure Blob

1
votes

I want to take SQL Azure back up to Azure Blob storage using PowerShell. I have used the following script, but it is popping up for credential whenever I try to run.

I will be using this script from windows task scheduler, so how can I put user id and password inside PowerShell script so that it won't ask for username/password for subscription?

$subscriptionId = "xxxxxxxxxxxxxxxxxxxxx"

Login-AzureRmAccount
Set-AzureRmContext -SubscriptionId $subscriptionId

# Database to export
$DatabaseName = "xxxxxxxxxxx"
$ResourceGroupName = "xxxxxxxxxxx"
$ServerName = "xxxxxxxxxxxx"
$serverAdmin = "xxxxxxxxxxxxxxx"
$serverPassword = "xxxxxxxxxxx" 
$securePassword = ConvertTo-SecureString -String $serverPassword -AsPlainText -Force
$creds = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $serverAdmin, $securePassword

# Generate a unique filename for the BACPAC
$bacpacFilename = $DatabaseName + (Get-Date).ToString("yyyyMMddHHmm") + ".bacpac"


# Storage account info for the BACPAC
$BaseStorageUri = "https://xxxxxxx.blob.core.windows.net/xxxxx/"
$BacpacUri = $BaseStorageUri + $bacpacFilename
$StorageKeytype = "StorageAccessKey"
$StorageKey = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

$exportRequest = New-AzureRmSqlDatabaseExport -ResourceGroupName $ResourceGroupName -ServerName $ServerName `
  -DatabaseName $DatabaseName -StorageKeytype $StorageKeytype -StorageKey $StorageKey -StorageUri $BacpacUri `
  -AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password
2
powershellazureazure-sql-databasepowershell-2.0

2 Answers

1
votes

You need to implement a non-interactive login in your script. Just modify your script as below:

##login Azure  
$subscriptionId = "xxxxxxxxxxxxxxxxxxxxx"
$username = "<username>"
$password = "<password>"
$secstr = New-Object -TypeName System.Security.SecureString
$password.ToCharArray() | ForEach-Object {$secstr.AppendChar($_)}
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $secstr
Login-AzureRmAccount -Credential $cred
Select-AzureRmSubscription -SubscriptionId $subscriptionId

Note: you cannot login non-intereractively to Azure using a Microsoft Live account, such as *@hotmail.com, *@outlook.com.

1
votes

There are probably two ways you can achieve this.

The first is to use the credential parameter as part of the Login-AzureRMAccount call. You can create a PSCredential in the powershell code and then use that. For example: Login-AzureRmAccount -Credential $credential

The second, and probably the safer/more secure way, is to create a service principal and then place the certificate on the machine in question. You can find instructions on how to do this here. After you have that created, you can use the Login-AzureRMAccount with the -ServicePrincipalparameter. See this link for more information.