Web Analytics
Skip to main content

Andosi - Blog

Where we discuss The Art of Great System Design

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.

More Blog Posts

Capturing (and using) raw SOAP messages in WCF

Lance Russell
WCF is great for building web services.  It's also great for interacting with existing web services.  Visual Studio makes it so easy . . . add a service reference, point to the WSDL of the service and just like that, you have a set of classes to h...

Connecting to a Microsoft CRM 2013 Internet Facing Deployment with C#

Tom Karpowich
Integrating and retrieving data from a Microsoft CRM 2013 instance is a common request, and I have found myself working on a number of these solutions lately. I encountered a recent example where a client was using an Internet Facing Deployment of...