Expose SQL Server database as web service to get data from

As from SQL Server 2005 you can expose native XML web services directly from the database.

SQL Server can be configured to listen natively for HTTP SOAP requests through an HTTP endpoint. In general you would want to expose stored procedures or user-defined functions as HTTP endpoints, so a little coding is required. But it should be very easy to follow from the examples.

You would normally start by creating a stored procedure as follows:

CREATE PROCEDURE [dbo].[getContact]   @ID [int]       ASBEGIN   SELECT * FROM [AdventureWorks].[Person].[Contact] WHERE ContactID = @ID   END;

And then you would define your HTTP endpoint like this:

CREATE ENDPOINT SQLEP_GetContact    STATE = STARTEDAS HTTP(    PATH = '/Contact',    AUTHENTICATION = (INTEGRATED),    PORTS = (CLEAR),    SITE = 'localhost')FOR SOAP(    WEBMETHOD 'ContactInfo' (NAME='AdventureWorks.dbo.getContact'),    BATCHES = DISABLED,    WSDL = DEFAULT,    DATABASE = 'AdventureWorks',    NAMESPACE = 'http://AdventureWorks/Contact');

After creating the endpoint, you can submit an HTTP request to the server to ensure that the endpoint is responding: http://localhost/Contact?wsdl.

To modify or to stop your endpoint, you can use the ALTER ENDPOINT command:

ALTER ENDPOINT SQLEP_GetContact    STATE = STOPPED;

You may want to proceed by checking out the following articles:

• Devx.com - Easy Web Services with SQL Server 2005 HTTP Endpoints
• MSDN - Overview of Native XML Web Services for Microsoft SQL Server 2005
• Developer.com - Native XML Web Services in SQL Server 2005

UPDATE: Following Ed Harper's comment below, please note that native XML web services have been deprecated in SQL Server 2008 (November 2009), and this feature will be removed in future version of SQL Server. Microsoft is suggesting using WCF web services instead. Source: MSDN - Native XML Web Services: Deprecated in SQL Server 2008

While to use WCF Data Services can be an option, just like Anton said, you should consider if it's a good idea to provide a direct path to your entire/partial database.

Another option is to build a data access layer, which will allow just a small operation set, like: "you can to add a customer, but you're not allowed to delete an invoice"

Scott Hanselman explains how to create an OData / Open Data / WCF Data Service from a database using Visual Studio 2010:

http://www.hanselman.com/blog/CreatingAnODataAPIForStackOverflowIncludingXMLAndJSONIn30Minutes.aspx