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

go

use MyTestDB

go

create table Test

(Number int)

go

insert into Test

values (3)

go

select * from Test

–Enable Service Broker

use MytestDB

create master key encryption by password = ‘Password1!’

go

alter database MyTestDB set ENABLE_BROKER with ROLLBACK IMMEDIATE

go

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

sp_changedbowner ‘sa’

go

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

create proc NumberService

as

(

select Number from Test

)

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

–WebService

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’

)

go

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();

            //client.Endpoint.b

            object[] numbers = client.GetNumberService();

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

            {

                Console.WriteLine(num[0].ToString());

            }

        }

    }

}

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” />

                    </security>

Run this app and we get the web service consumed:


Advertisements

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