Introduction

SQL Server 2005 introduces new features called Endpoints, which make other applications able to connect to SQL Server 2005 directly. SQL Server 2005 defines two types of endpoints, which are:

1. TCP Endpoint (which won’t be covered in this article)
2. HTTP Endpoint This article is going to focus on HTTP Endpoints.

Prerequisites

1. SQL Server 2005 Developer Edition
2. Microsoft Visual Studio.NET 2005
3. AdventureWorks Sample Database

HTTP endpoint overview

The word HTTP might suggest that external applications connect to SQL Server 2005 through IIS. But this is incorrect! SQL Server 2005 uses the http.sys driver file instead of IIS to provide communication between an external application and SQL Server 2005 using the HTTP protocol.

How to create an HTTP Endpoint in SQL Server 2005

In the following section I’m going to describe the steps needed to create a HTTP Endpoint in SQL Server 2005.

1. Open Microsoft SQL Server 2005 Management Studio.
2. Log onto the Server Type Database Engine using your Authentication type.
3. Click on Create New Query.
4. Write the SQL statements needed to create the stored procedures to be exposed through the HTTP Endpoint, as shown in listing 1.

Listing 1: Stored Procedures exposed through the HTTP Endpoint

use Adventureworks /* Create Stored Procedure that you want to Exposed to External application */ --This Procedure Return Products belong to Input category Create Proc udp_Get_Products @CategoryID int as SELECT Production.Product.Name, Production.Product.ProductNumber, Production.Product.Color, Production.Product.ListPrice, Production.ProductCategory.Name CategoryName FROM Production.Product INNER JOIN Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID INNER JOIN Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID WHERE (Production.ProductCategory.ProductCategoryID = @CategoryID) go /* Create Stored Procedure that you want to Exposed to External application*/ --This Procedure Return all categories Create Proc udp_Get_Categories as SELECT Production.ProductCategory.Name, Production.ProductCategory.ProductCategoryID from Production.ProductCategory go

5. Write the SQL that creates the HTTP Endpoint, as shown in listing 2.

Listing 2: SQL code for creating the HTTP Endpoint

/* Create New End Points named sql_Http_Endpoint */ create ENDPOINT sql_Http_Endpoint --Set Endpoint listen to port for any request from Clients STATE = STARTED --Speify that HTTP protocol that used by endpoint AS HTTP( --Specify the Virtual Path that used to get your method PATH = '/sql', /*Specify the authentication mode used to login through Http endpoint*/ AUTHENTICATION = (INTEGRATED) , /*specfy that port used http protocol if you want to use https change CLEAR to SSL*/ PORTS = ( CLEAR ), --Specify the port number used by endPoint CLEAR_PORT = 1024, /*Specify servername that host endpoint */ SITE = 'localhost' ) /*Use simple object access protocol SOAP to interoperability*/ FOR SOAP( /*specify the logical name used by client to call specific stored procedure */ WEBMETHOD 'GetProductsByCategoryId' ( --Specify the actual stored procedure name in database name='AdventureWorks.dbo.udp_Get_Products' ), WEBMETHOD 'GetCategories' ( --Specify the actual stored procedure name in database name='AdventureWorks.dbo.udp_Get_Categories' ), /* Make WSDL return to Client to know the Request/Response Mdoel Stored Procedure and used to Create Proxy Class in .NET application */ WSDL = DEFAULT, --include Schema in soap response to client SCHEMA = STANDARD, /* Specifies the database in the context of which the requested operation is executed */ DATABASE = 'adventureWorks', --Specifies the namespace for the endpoint NAMESPACE = 'http://tempUri.org/' ); GO

How to consume the exposed Stored Procedures through a .NET Application

In the following section I’m going to describe the steps needed to create a web application that consumes the stored procedures exposed through the HTTP Endpoint.

1. In Visual Studio 2005, create a new ASP.NET application.
2. In the Solution Explorer, right click on the project and select Add Web Reference, as shown in Figure 1.

Figure 1: Adding a web reference to an ASP.NET application

3. As shown in figure 2, type the following URL in the URL field of the “Add Web Reference” window: http://localhost/SQL?WSDL. The result returned is a web method that represents the stored procedure exposed through the HTTP Endpoint.

Figure 2: Specifying the URL in the Add Web Reference window

4. Design a web form as shown in figure 3.

Figure 3: Web Form layout

5. Write the code reported in listing 3 in the Page_Load event handler.

Listing 3: Code for the Page_Load method

protected void Page_Load(object sender, EventArgs e) { //this is first request to page if (!IsPostBack) { /*Create Object from Proxy class that expose * udp_Get_Products stored procedure by logical name * GetCategories Method return Array Of Objects * the first Object is Dataset that hold Table which * we want to bind to Dropdown control * named lst_Categories */ localhost.sql_Http_Endpoint sqlService = new localhost.sql_Http_Endpoint(); sqlService.Credentials= System.Net.CredentialCache.DefaultCredentials; DataSet ds= sqlService.GetCategories()[0] as DataSet; lst_Categories.DataSource = ds; lst_Categories.DataMember = ds.Tables[0].TableName; lst_Categories.DataTextField = ds.Tables[0].Columns[0].ColumnName; lst_Categories.DataValueField= ds.Tables[0].Columns[1].ColumnName; lst_Categories.DataBind(); } }

6. Write the code reported in listing 4 in the event handler for the button’s Click event.

Listing 4: Code under Button Click

protected void btn_Search_Click(object sender, EventArgs e) { /* Create Object from Proxy class that expose * udp_Get_Products stored procedure by logical name * GetProductsByCategoryId Method return * Array Of Objects the first Object is Dataset * that hold Table which we want * to bind to Dropdown control named lst_Categories */ localhost.sql_Http_Endpoint sqlService = new localhost.sql_Http_Endpoint(); sqlService.Credentials = System.Net.CredentialCache.DefaultCredentials; int id = Convert.ToInt32(lst_Categories.SelectedValue); DataSet ds = sqlService. GetProductsByCategoryId(id)[0] as DataSet; Gv_Products.DataSource = ds.Tables[0]; Gv_Products.DataBind(); }

7. Run the application by pressing CTRL + F5. In the page, select Accessories from the dropdown list control; then, click on the Search button.

8. The result of the search is shown in figure 4.

Figure 4: Result of the search

Considerations for using HTTP Endpoints

1. Interoperability. HTTP endpoints use the SOAP protocol. If your solution requires that clients using operating systems other than Microsoft Windows® be able to access SQL Server 2005, using HTTP endpoints is an obvious choice.

2. Performance. HTTP endpoints use the http.sys kernel mode driver, which provides better performance than Internet Information Services (IIS)–based solutions such as ASP.NET Web Services.

3. Security. You should not use HTTP endpoints to connect the database directly to an Internet application, because doing so exposes the database to the Internet. This increases the risk of an attack. If you must provide access to the database over the Internet, you should select more secure technologies, such as ASP.NET Web Services and SQLXML.

4. Scaling out. HTTP endpoints do not scale out because they use the Database Engine directly. If you need to scale out your system, you should select another technology, such as ASP.NET Web Services or SQLXML, which can run on multiple servers.

Scenarios suitable for using HTTP endpoints

The following list describes some scenarios in which you should consider using HTTP endpoints:

1. Generating reports for internal use. You can quickly create stored procedures that retrieve the required data and expose them through HTTP endpoints. You can provide internal users who need this data with the URL of the HTTP endpoint. These users can then use a web browser such as Microsoft Internet Explorer to connect to this URL and view the data returned by the stored procedure.

2. Using XML. You might have applications that generate and process data as XML instead of using the relational format used by SQL Server. You can use an HTTP endpoint to send and receive data as XML documents between an application and SQL Server 2005.

3. Implementing a Service-Oriented Architecture (SOA). The implementation of HTTP endpoints conforms to the SOA, providing programmers with a consistent means of exposing and consuming data services.

Scenarios where you should not use HTTP endpoints

The following list describes some scenarios in which it is not appropriate to use HTTP endpoints:

1. Using Windows 2000 or earlier operating systems. HTTP endpoints use the http.sys kernel driver. You can use this driver only with Microsoft Windows Server™ 2003 and Windows XP Service Pack 2.

2. Performing real-time transaction processing. HTTP endpoints cannot provide the same response time as other data access technologies, such as connecting directly to the database server by using Microsoft ADO.NET. Do not use HTTP endpoints when you require mission-critical response times.

3. Using large objects (LOBs). XML and HTTP endpoints are not the most efficient mechanism for transporting LOBs to and from a database server. The serialization mechanism required to convert LOB data to XML can require considerable processing power and consume significant network bandwidth.

Best practices for using HTTP endpoints

Keep in mind the following best practices when you use HTTP endpoints:

1. Use Kerberos authentication. You can define an endpoint with one or more of the following authentication types: Basic, Digest, NTLM, Kerberos, and Integrated (NTLM and Kerberos). Kerberos is the most secure because it uses stronger encryption algorithms than the other authentication types. It also identifies both the server and the client. In fact, to use Kerberos, you must associate a server principal name (SPN) with the account under which SQL Server is running.

2. Limit endpoint access by using the Connect permission. You should limit endpoint access only to those users or groups that need it. You can accomplish this by granting or denying the SQL Server 2005 Connect permission.

3. Use Secure Sockets Layer (SSL) encryption to exchange sensitive data. If you are planning to exchange sensitive data by using HTTP endpoints, use SSL to help ensure the confidentiality of data.

4. Place SQL Server behind a firewall. If you need to provide HTTP access for SQL Server to Internet users, don’t do it directly. Instead, configure SQL Server behind a firewall that protects your communications.

5. Disable the Windows Guest account on the server. The Guest account, when enabled, allows any user to log on to the local computer without having to provide a password.

6. Enable HTTP endpoints only as needed. You should not enable an endpoint if it is not required or currently in use.

Summary

HTTP endpoints are a new feature provided by Microsoft SQL Server 2005. They can be used to expose stored procedures directly to external applications.

About Ahmed Mosa

Sorry, no bio is available View complete profile here.

---

Discussion

Subject	Author	Date
Security issues	Lee Williams	7/8/2009 9:27 AM
Hi Ahmed I am getting alot of security issues trying to create a simple End Point on my SQL Server 8, i am also using Window XP SP 3. When i try and create an END Point i get:- Msg 7850, Level 16, State 1, Line 1 The SQL Server Service account does not have permission to register the supplied URL on the endpoint 'JobControlEndPoint'. Use sp_reserve_http_namespace to explicitly reserve the URL namespace before you try to register the URL again. Msg 7807, Level 16, State 1, Line 1 An error ('0x80070005') occurred while attempting to register the endpoint 'JobControlEndPoint'. When i try and register the namespace i get:- The user does not have permission to reserve and unreserve HTTP namespaces. Then i try and grant access to myself using:- GRANT ALTER ANY ENDPOINT TO [???] And stil the same. DO you have any help? Lee Reply | Permanent link

---

Please login to rate or to leave a comment.