SQL Server 2008: SQL Server web service using Service Broker

Important: SQL Server HTTP web service has been removed from SQL Server 2012. Existing implementation will need to recreate web service using other tools like ASP.Net.

Besides using ASP.Net to create a web service and pull out data, we can also take advantage of SQL Server Service Broker to create a web service within SQL Server and expose a stored procedure.

Let’s begin with a test database:

–Setup Test base

create database MyTestDB


use MyTestDB


create table Test

(Number int)


insert into Test

values (3)


select * from Test

–Enable Service Broker

use MytestDB

create master key encryption by password = ‘Password1!’




–Change database owner since it was created under my windows ID

sp_changedbowner ‘sa’


OK. Let’s go ahead and create our stored procedure which returns expected data:

create proc NumberService



select Number from Test


Next step is to create end point and expose stored procedure:


create endpoint WebServiceEndPoint

state = started

as http


    path = ‘/NumberService’,

    authentication = (integrated),

    ports = (clear),

    site = ‘*’


for soap


    webmethod ‘GetNumberService’


        name = ‘MyTestDb.dbo.NumberService’


    wsdl = default,

    schema = standard,

    database = ‘MyTestDB’,

    namespace = ‘http://www.test.com’



Server side work is done, let do a quick test: Open Internet Explorer and enter ‘http://<computer_NetBIOS name>/NumberService?WSDL’, you should be able to see:

Great, we got web service up and running. Let’s create a quick client to consume this service.

In VS2008, create a new console project, add a web reference to the new created web service. Then enter following code:

using System;

using System.Data;

namespace SQLWebService


    class Program


        static void Main(string[] args)


            ServiceReference1.WebServiceEndPointSoapClient client = new SQLWebService.ServiceReference1.WebServiceEndPointSoapClient();


            object[] numbers = client.GetNumberService();

            foreach (DataRow num in ((DataSet)numbers[0]).Tables[0].Rows)







Before running this client, go to app.config and update security setting. We had setup Windows authentication on server side and also need to do the same on client side:

Open App.config, locate Security section, update security mode to TransportationCredentialOnly and clientCredentialType to Windows:

                    <security mode=”TransportCredentialOnly”>

                        <transport clientCredentialType=”Windows” proxyCredentialType=”None”

                            realm=”” />

                        <message clientCredentialType=”UserName” algorithmSuite=”Default” />


Run this app and we get the web service consumed:


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s