SQL Azure Management REST API

The SQL Azure Management REST API was released with the May 2011 SQL Azure release. This API is very similar to the Windows Azure Service Management REST API that has been available for some time. They both authenticate using an X.509 certificate that has been uploaded as a management certificate to the Windows Azure Portal.

The primary differences are that the SQL Azure Management REST API uses:

  • service endpoint: management.database.windows.net:8443
  • x-ms-version: 1.0

while the Windows Azure Service Management REST API uses:

  • service endpoint: management.core.windows.net
  • x-ms-version: 2011-02-25

This post is a sequel to an earlier post on the Windows Azure Service Management REST API.

SQL Azure Management Operations

The SQL Azure Management REST API supports the following operations:

The Create Server, Set Server Administrator Password and the Set Server Firewall Rule operations all require a request body adhering to a specified XML schema.

Creating the HTTP Request

Each operation in the SQL Azure Management REST API requires that a specific HTTP request be made against the service management endpoint. The request must be authenticated using an X.509 certificate that has previously been uploaded to the Windows Azure Portal. This can be a self-signed certificate.

The following shows how to retrieve an X.509, identified by thumbprint, from the Personal (My) level of the certificate store for the current user:

X509Certificate2 GetX509Certificate2(String thumbprint)
{
    X509Certificate2 x509Certificate2 = null;
    X509Store store = new X509Store(“My”, StoreLocation.CurrentUser);
    try
    {
        store.Open(OpenFlags.ReadOnly);
        X509Certificate2Collection x509Certificate2Collection =
             store.Certificates.Find(X509FindType.FindByThumbprint, thumbprint, false);
        x509Certificate2 = x509Certificate2Collection[0];
    }
    finally
    {
        store.Close();
    }
    return x509Certificate2;
}

The following shows how to create an HttpWebRequest object, add the certificate (for a specified thumbprint), and add the required x-ms-version request header:

HttpWebRequest CreateHttpWebRequest(
    Uri uri, String httpWebRequestMethod, String version)
{
    X509Certificate2 x509Certificate2 = GetX509Certificate2(“THUMBPRINT”);

    HttpWebRequest httpWebRequest = (HttpWebRequest)HttpWebRequest.Create(uri);
    httpWebRequest.Method = httpWebRequestMethod;
    httpWebRequest.Headers.Add(“x-ms-version”, version);
    httpWebRequest.ClientCertificates.Add(x509Certificate2);
    httpWebRequest.ContentType = “application/xml”;

    return httpWebRequest;
}

Making a Request on the Service Management API

As with other RESTful APIs, the Service Management API uses a variety of HTTP operations – with GET being used to retrieve data, DELETE being used to delete data, and POST or PUT being used to add elements.

The following example invoking the Get Servers operation is typical of those operations that require a GET operation:

XDocument EnumerateSqlAzureServers(String subscriptionId)
{
    String uriString =
       String.Format(“https://management.database.windows.net:8443/{0}/servers”,
           subscriptionId);
    String version = “1.0”;

    XDocument responseDocument;
    Uri uri = new Uri(uriString);
    HttpWebRequest httpWebRequest = CreateHttpWebRequest(uri, “GET”, version);
    using (HttpWebResponse httpWebResponse =
        (HttpWebResponse)httpWebRequest.GetResponse())
    {
        Stream responseStream = httpWebResponse.GetResponseStream();
        responseDocument = XDocument.Load(responseStream);
    }
    return responseDocument;
}

The response containing the list of servers is loaded into an XML document where it can be further processed as necessary. The following is an example response:

<Servers xmlns=”http://schemas.microsoft.com/sqlazure/2010/12/”&gt;
  <Server>
    <Name>SERVER</Name>
    <AdministratorLogin>LOGIN</AdministratorLogin>
    <Location>North Central US</Location>
  </Server>
</Servers>

Some operations require that a request body be constructed. Each operation requires that the request body be created in a specific format – and a failure to do so causes an error when the operation is invoked.

The following shows how to create the request body for the Set Server Firewall Rule operation:

XDocument GetRequestBodyForAddFirewallRule(String startIpAddress, String endIpAddress)
{
    XNamespace defaultNamespace =
        XNamespace.Get(“http://schemas.microsoft.com/sqlazure/2010/12/&#8221;);
    XNamespace xsiNamespace =
        XNamespace.Get(“http://www.w3.org/2001/XMLSchema-instance&#8221;);
    XNamespace schemaLocation = XNamespace.Get(“http://schemas.microsoft.com/sqlazure/2010/12/FirewallRule.xsd&#8221;);

    XElement firewallRule = new XElement(defaultNamespace + “FirewallRule”,
        new XAttribute(“xmlns”, defaultNamespace),
        new XAttribute(XNamespace.Xmlns + “xsi”, xsiNamespace),
        new XAttribute(xsiNamespace + “schemaLocation”, schemaLocation),
        new XElement(defaultNamespace + “StartIpAddress”, startIpAddress),
        new XElement(defaultNamespace + “EndIpAddress”, endIpAddress));

    XDocument requestBody = new XDocument(
        new XDeclaration(“1.0″, “utf-8″, “no”),
        firewallRule
    );
    return requestBody;
}

This method creates the request body and returns it as an XML document. The following is an example:

<FirewallRule
    xmlns=”http://schemas.microsoft.com/sqlazure/2010/12/&#8221;
    xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance&#8221;
    xsi:schemaLocation
        =”http://schemas.microsoft.com/sqlazure/2010/12/FirewallRule.xsd”&gt;
  <StartIpAddress xmlns=””>10.0.0.1</StartIpAddress>
  <EndIpAddress xmlns=””>10.0.0.255</EndIpAddress>
</FirewallRule>

The following example shows the invocation of the Set Server Firewall Rule operation:

void AddFirewallRule(String subscriptionId, String serverName, String ruleName,
      String startIpAddress, String endIpAddress)
{
    String uriString = String.Format(
https://management.database.windows.net:8443/{0}/servers/{1}/firewallrules/{2}”,
       subscriptionId, serverName, ruleName);
    String apiVersion = “1.0”;

    XDocument requestBody = GetRequestBodyForAddFirewallRule(
         startIpAddress, endIpAddress);
    String StatusDescription;
    Uri uri = new Uri(uriString);
    HttpWebRequest httpWebRequest = CreateHttpWebRequest(uri, “PUT”, apiVersion);
    using (Stream requestStream = httpWebRequest.GetRequestStream())
    {
        using (StreamWriter streamWriter =
            new StreamWriter(requestStream, System.Text.UTF8Encoding.UTF8))
        {
            requestBody.Save(streamWriter, SaveOptions.DisableFormatting);
        }
    }
    using (HttpWebResponse httpWebResponse =
     (HttpWebResponse)httpWebRequest.GetResponse())
    {
        StatusDescription = httpWebResponse.StatusDescription;
    }
}

About these ads

About Neil Mackenzie

Azure Architect at Satory Global.
This entry was posted in SQL Azure, Windows Azure and tagged , , . Bookmark the permalink.

3 Responses to SQL Azure Management REST API

  1. Pingback: Květen přináší SQL Azure Management API…konečně!!! « cloud(ikka)

  2. Pingback: Service Management API in Windows Azure | Convective

  3. Pingback: Creating a Windows Azure hosted service | Convective

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s