How to Manage Windows Azure SQL Database Servers Using Windows PowerShell

Article
1/17/2024

Click here to change the language (ja-JP).

This article provides information on using PowerShell to perform database management operations against Windows Azure SQL Database through the Management REST APIs.

Note

If you wish to contribute to this page, use the Edit tab at the top (sign-in required). If you wish to provide feedback for this documentation please either send e-mail to azuredocs@microsoft.com or use the Comment field at the bottom of this page (sign-in required).

SQL Database Management REST APIs

SQL Database exposes REST APIs that allow you to create, list, and delete SQL Database servers, as well as firewall configuration APIs that allow you to configure the firewall rules for a given server. While the SQL Database documentation provides C# examples for using these REST APIs (http://msdn.microsoft.com/en-us/library/gg715271.aspx,) you can also use them from Windows PowerShell.

For the purposes of this article, using the REST APIs can be thought of as a simple HTTPS request to a specific URL. Some of the APIs call for a specific verb (GET, POST, DELETE,) and/or an XML message containing parameters. A full list of the URLs, verbs, and message formats can be found in the Management Rest API Reference topics (http://msdn.microsoft.com/en-us/library/gg715271.aspx).

Authentication

The SQL Database REST APIs require certificate authentication; a public key is installed on your Windows Azure Platform subscription and the private key is used to authenticate every call to a REST API.

To Create a Management Certificate

You can create a certificate for management purposes by using the makecert.exe utility by performing the following steps:

Open the Visual Studio Command Prompt window as an administrator.
Change the directory to location where you want to save the certificate file.
Type the the following command: makecert -sky exchange -r -n "CN=<CertificateName>" -pe -a sha1 -len 2048 -ss My "<CertificateName>.cer"

Where <CertificateName> is the name that you want to use for the certificate. It must have a .cer extension. For more information about using the tool, see Certificate Creation Tool (Makecert.exe).

NOTE: You can also use the Internet Information Services (IIS) Manager to create an X.509 server certificate. For more information about using IIS Manager to create certificates, see Create a Self-Signed Server Certificate in IIS 7.

After performing the steps above, the public key for this certificate should exist in the file you specified for <CertificateName>.cer parameter. This is the file that will be uploaded to your Windows Azure Platform subscription. The private key for this certificate has been installed in the personal certificate store for your user account (the ‘My’ store referenced in the command above.)

To Upload the Public Key

Perform the following steps to upload the public key to your Windows Azure Platform subscription:

Open a browser and navigate to the portal at http://windows.azure.com/, and then sign in with your account information.
In the navigation bar to the left of the page, select Hosted Services, Storage Accounts & CDN and then select Management Certificates.
Select Add Certificate from the ribbon at the top of the page, and then select your subscription.
Click Browse, and then select the <CertifificateName>.cer file you created earlier. Click OK to upload the certificate.

After performing these steps, the public key for this certificate should appear in the Management Certificates section of Windows Azure, and will allow you to authenticate REST API calls.

To Export the Private Key

Perform the following steps to export the private key:

Open the Certificate Manager snap-in for the management console by typing certmgr.msc in the Start menu textbox.
If you used the procedure that includes using the makecert program to create a certificate, the new certificate was automatically added to the personal certificate store. If your certificate is not listed under Personal Certificates, import your X.509 certificate.
Export the certificate by right-clicking the certificate in the right pane, pointing to All Tasks, and then clicking Export.
On the Export Private Key page, ensure that you select Yes, export the private key.
Finish the wizard, entering a password for the certificate file when prompted.

Store the private key in a secure location. While we will use this file to authenticate REST API requests from a PowerShell script in this article, the file should be stored in a secure location at all times. It provides the key that unlocks REST API management requests against your subscription; if it is lost or you believe someone has accessed the private key file without your consent, you should immediately remove the matching public key from the Windows Azure certificate store and generate a new set of keys.

Creating the Request Message

The following code demonstrates how to generate a request message. This code will perform the following actions:

Prompt the user for the path to the certificate file
Prompt the user for the password on the certificate
Prompt the user for the subscription ID
Load the certificate
Construct an HttpWebRequest request to the URI ‘https://management.database.windows.net:8443/<subscriptionID>/servers’
- Add the certificate
- Set the headers
- Set the method to GET
- Issue the request

The URI and verb combination used in this example will simply return a list of existing SQL Database servers for this subscription.

$certpath=Read-Host "Path to certificate file?"
$certpass=Read-Host –assecurestring "Password for certificate file?"
$subscription=Read-Host "Subscription ID?"

$cert= new-object System.Security.Cryptography.X509Certificates.X509Certificate $certpath, $certpass
$request = [System.Net.HttpWebRequest]::Create("https://management.database.windows.net:8443/{0}/servers"-f $subscription)
$request.ClientCertificates.Add($cert)
$request.Headers["x-ms-version"]="1.0"
$request.Method="GET"

$response=$request.GetResponse()

You can read the response stream and return the results using the following code:

$requestStream=$response.GetResponseStream()
$readStream=new-object System.IO.StreamReader $requestStream
new-variable db
$db=$readStream.ReadToEnd()
$readStream.Close()
$response.Close()
write-host $db

To Create an XML Body

The previous example demonstrated how to create the message, however the REST API for listing existing SQL Database servers doesn’t require a body. In the following example we will demonstrate creating an XML body for the message.

For this example, we will call the REST API to create a new SQL Database server. The URL for this is exactly the same as the one for returning a list of servers, however the verb used for the method is POST instead of GET. Also, this REST API requires a body that specifies the administrator login and password for this server, as well as the region it will be created in.

$certpath=Read-Host "Path to certificate file?"
$certpass=Read-Host –assecurestring "Password for certificate file?"
$subscription=Read-Host "Subscription ID?"

$salogin=Read-Host "Enter Administrator name for new server:”
$sapassword=Read-Host –assecurestring "Enter a password for the new Administrator:”
Write-Host "Enter the region the server will be created in:"
Write-Host "North Central US | South Central US | North Europe | West Europe | East Asia | Southeast Asia"
$location=Read-Host "Region?"

$cert= new-object System.Security.Cryptography.X509Certificates.X509Certificate $certpath, $certpass
$request = [System.Net.HttpWebRequest]::Create("https://management.database.windows.net:8443/{0}/servers"-f $subscription)
$request.ClientCertificates.Add($cert)
$request.Headers["x-ms-version"]="1.0"
$request.Method="POST"

$message='<[default] http://schemas.microsoft.com/sqlazure/2010/12/:SERVER xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
    <[default] http://schemas.microsoft.com/sqlazure/2010/12/:ADMINISTRATORLOGIN>{0}
    <[default] http://schemas.microsoft.com/sqlazure/2010/12/:ADMINISTRATORLOGINPASSWORD>{1}
    <[default] http://schemas.microsoft.com/sqlazure/2010/12/:LOCATION>{2}
  ' -f $salogin, $sapassword, $location
$requeststream=new-object System.IO.StreamWriter $request.GetRequestStream()
$requeststream.Write($message)
$requeststream.Flush()
$requeststream.Close()

$request.ContentType="application/xml;charset=utf-8"


$response=$request.GetResponse()
$requestStream=$response.GetResponseStream()
$readStream=new-object System.IO.StreamReader $requestStream
new-variable db
$db=$readStream.ReadToEnd()
$readStream.Close()
$response.Close()
write-host $db

Share via