Executing SQL Queries via SharePoint Web Services

Can you really execute native SQL Queries from SharePoint Web Services?

Microsoft SharePoint is great for building enterprise systems tying together various data sources.  If the information you are looking for is in a SharePoint List or Document Library, it is straightforward to call the built-in Web Services to query or manipulate that data.  Through custom Web Parts, you can run server-side code and easily retrieve data that lives outside SharePoint.

But what if you don’t have the access to run Server-Side code?  How can you get to the data that lives in SQL Server from your client-side web application?  You can’t call external XML web services because of the Same Origin Policy Restrictions.  True, you can work around this if you have access to a JSONP web service, and some browsers and servers are starting to support CORS to allow limited cross-site access.  But if you don’t have access to the server, can’t control the browser environment and no JSONP web services are available, you aren't out of luck.  I'll show you how to get SharePoint to execute the SQL Queries on your behalf and return the results to your web browser.  With a few tweaks, this same technique can also be used to access arbitrary XML Web Services.  In a later article, I'll expand this example to do just that.

A Word of Warning:

There is a downside to this approach.  Since you will be using the SharePoint Server as a proxy, the SQL logs will show the connection coming from the SharePoint Server.  Also, you must either pass in a username/password with the web service call or use a guest SQL account.  You will have to consider the security impact of either approach carefully.

How does it work?

You will be using the WebPartPages.GetDataFromDataSourceControl method.  This method is intended to be used by SharePoint Designer to render data during page design and is very sparsely documented.  According to MSDN, it takes two string parameters: dscXml and contextUrl.  That is the extent of the MSDN documentation.

In order to make it easier to work with, I created a helper function called SqlQuery that accepts a Server name, Database name, User name, password, Sql Query and a callback function.  Pass in the proper parameters and your callback will be executed with the results.  You can paste the source code below into the HTML source of a Content Editor Web Part on a page on your SharePoint Server to test.  Note, I am using JQuery to simplify the AJAX calls and form interaction.

Source Code:

    <script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.10.2.min.js" type="text/javascript"></script>

    <script type="text/javascript">
        $(document).ready(function () {

            //SqlQuery function - proxies a SQL Server call through sharepoint web services and executes your callback with the results.
            function SqlQuery(server, database, user, password, query, callback) {
                var soapMessage = ["<?xml version='1.0' encoding='utf-8'?>"];
                soapMessage.push("<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>");
                soapMessage.push("  <soap:Body>");
                soapMessage.push("      <GetDataFromDataSourceControl xmlns='http://microsoft.com/sharepoint/webpartpages'>");
                soapMessage.push("          <dscXml>&lt;asp:SqlDataSource runat=&quot;server&quot;  __designer:commandsync=&quot;true&quot; ProviderName=&quot;System.Data.SqlClient&quot;  ConnectionString=&quot;");
                soapMessage.push("Data Source=" + server + ";User ID=" + user + ";Password=" + password + ";Initial Catalog=" + database + ";&quot; ");
                soapMessage.push(" SelectCommand=&quot;" + query + " &quot;/&gt;</dscXml>");
                soapMessage.push("          <contextUrl></contextUrl>");
                soapMessage.push("      </GetDataFromDataSourceControl>");
                soapMessage.push("  </soap:Body>");
                soapMessage.push("</soap:Envelope>");
                var message = soapMessage.join("");
                var thisSite = window.location.href.split(window.location.pathname).shift(); 
                $.ajax({
                    url: thisSite + "/_vti_bin/webpartpages.asmx",
                    beforeSend: function (xhr) { xhr.setRequestHeader("SOAPAction", "http://microsoft.com/sharepoint/webpartpages/GetDataFromDataSourceControl"); },
                    type: "POST",
                    dataType: "xml",
                    contentType: "text/xml; charset=\"utf-8\"",
                    data: message,
                    complete: function (xData, status) {
                        if (status == "success") {
                            var rows = $($.parseXML($(xData.responseXML).find("GetDataFromDataSourceControlResult").prop("text"))).find("Row");
                            callback(rows, status);
                        } else {
                            callback(xData, status);
                        }
                    }
                });
            };
            //Set up some variables to interact with the form.
            var executeButton = $("#executeQuery"), serverInput = $("#server"), databaseInput = $("#database"), userNameInput = $("#userName"), passwordInput = $("#password"), queryInput = $("#query");


            //When the user clicks the button, execute the query, parse and display the results.
            executeButton.click(function () {
                window.status = "calling SQL Query";

                SqlQuery(serverInput.val(), databaseInput.val(), userNameInput.val(), passwordInput.val(), queryInput.val(), function (rows, status) {
                    window.status = status;
                    if (status == "success") {
                        var output = ["<ol>"];

                       //rows is now a jquery object with the response from the sql query
                        rows.each(function (i, element) {
                            var row = [];
                            for (var j = 0; j < element.attributes.length; j++) {
                                var attribute = element.attributes[j];
                                row.push("'" + attribute.nodeName + "'='" + attribute.value + "'");
                            }
                            output.push("<li>" + row.join() + "</li>");
                        });
                        $("#output").html(output.join("") + "</ol>");
                    } else {
                        $("#output").html($(rows.responseXML).text());
                    }
                });
            });
        });
    
    </script>

<!-- And the form to let the user interact with the SqlQuery function. -->

    <h1>
        SQL Query Demo</h1>
    <label for='userName'>
        User Name:</label>
    <input type='text' id='userName' value='SQLUser' />
    <label for='password'>
        Password:</label>
    <input type='password' id='password' value='********' />
    <label for='server'>
        Server:</label>
    <input type='text' id='server' value='SERVERNAME' />
    <label for='database'>
        Database:</label>
    <input type='text' id='database' value='Food' />
    <label for='query'>
        Query:</label>
    <textarea id='query' rows='5' cols='80'>SELECT TOP 10 [NDB_No] ,[Seq] ,[Amount] ,[Msre_Desc] ,[Gm_Wgt] FROM [Food].[dbo].[WEIGHT]</textarea>
    <input type='button' id='executeQuery' value='Execute Query' />
    <h1>
        Output</h1>
    <div id='output'>
    </div>

Example: