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.

 

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.

 

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.