List of blobs in Azure Storage from SQL Server

Question

We're currently using Azure storage to back up our SQL Server databases, and I've been tasked with recreating a list of the blobs in the database. Currently we use BACKUP TO URL with the credentials stored in sys.credentials.

Is it possible to get a list of containers in a storage account and/or get a list of blobs in a container straight from SQL Server with the saved credentials from the database?

You actually want to make Azure Storage API calls from SQL Server to Azure Storage??? You'd need CLR procedures to accomplish that, as you'd need to make REST calls (but this is a major "code smell"). — David Makogon

Fei Han Fei Han · Accepted Answer · 2016-10-28T07:39:07

Is it possible to get a list of containers in a storage account and/or get a list of blobs in a container straight from SQL Server with the saved credentials from the database?

As David Makogon said, if you’d like to list containers/blobs from SQL Server, you could try to use SQL CLR stored procedure. And I have a working sample to list blobs from a container, you could refer to it.

SQL CLR StoredProcedure

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void SqlStoredProcedure1 ()
{

    // Put your code here

    string StorageAccount = "myaccount";
    string StorageKey = "accountkey";

    string containername = "mycontainer";

    string requestMethod = "GET";
    string mxdate = "";
    string storageServiceVersion = "2014-02-14";

    HttpWebRequest req = (HttpWebRequest)WebRequest.Create(string.Format(CultureInfo.InvariantCulture,
            "https://{0}.blob.core.windows.net/{1}?restype=container&comp=list",
            StorageAccount,
            containername
            ));

    req.Method = requestMethod;

    //specify request header

    mxdate = DateTime.UtcNow.ToString("R");

    string canonicalizedHeaders = string.Format(
            "x-ms-date:{0}\nx-ms-version:{1}",
            mxdate,
            storageServiceVersion);

    string canonicalizedResource = string.Format("/{0}/{1}\ncomp:list\nrestype:container", StorageAccount, containername);

    string stringToSign = string.Format(
        "{0}\n\n\n\n\n\n\n\n\n\n\n\n{1}\n{2}",
        requestMethod,
        canonicalizedHeaders,
        canonicalizedResource);

    HMACSHA256 hmac = new HMACSHA256(Convert.FromBase64String(StorageKey));

    string signature = Convert.ToBase64String(hmac.ComputeHash(Encoding.UTF8.GetBytes(stringToSign)));

    String authorization = String.Format("{0} {1}:{2}",
        "SharedKey",
        StorageAccount,
        signature
        );
    string AuthorizationHeader = authorization;

    req.Headers.Add("Authorization", AuthorizationHeader);
    req.Headers.Add("x-ms-date", mxdate);
    req.Headers.Add("x-ms-version", storageServiceVersion);
    DataTable dt = new DataTable();
    using (HttpWebResponse response = (HttpWebResponse)req.GetResponse())
    {
        var stream = response.GetResponseStream();

        StreamReader reader = new StreamReader(stream);
        string content = reader.ReadToEnd();

        StringReader theReader = new StringReader(content);
        DataSet theDataSet = new DataSet();
        theDataSet.ReadXml(theReader);

        dt = theDataSet.Tables[2];
    }

    string blobs = "";

    foreach (DataRow item in dt.Rows)
    {
        blobs += item[0].ToString() + ";";
    }

    SqlContext.Pipe.Send(blobs);
}
}

The common language runtime (CLR) integration feature is off by default in Microsoft SQL Server and must be enabled in order to use SQL Server project items. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure.

And you could refer to this tutorial to know how to use SQL CLR stored procedure.

List of blobs in Azure Storage from SQL Server

1 Answers