How to Register/Unregister a SQL 2005 Endpoint

If you're like me you want to create services based on Service Oriented Architecture (SOA) so that you can decouple your applications from the underlying repository. The beauty of doing this is the ability to move the application to a new server environment or relocate an existing server (either physically or virtually – based on IP) to any place in the public or private cloud. The below picture depicts the benefits of SOA.

Before: In traditional IT architectures, business process activities, applications, and data are locked in independent, often-incompatible "silos." Users have to navigate separate networks, applications, and databases to conduct the chain of activities that complete a business process. Independent silos absorb an inordinate amount of IT budget and staff time to maintain.

After: A Services-Oriented Architecture (SOA) delivers the data needed for business process activities as an integrated service. Users no longer have to log into multiple systems, search for relevant data, and integrate the results manually. The information appears as a single application, delivered on a single screen, all with a single login.

With the above in mind, it is natural to desire to decouple access to the underlying repository from your business application. SQL Endpoints in SQL Server 2005 provides SOA enthusiasts the capability to expose database stored procedures and functions as a web service with the below code snippet.

use [master]

GO

IF EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'my_sql_endpoint')

DROP ENDPOINT [my_sql_endpoint]

GO

CREATE ENDPOINT [my_sql_endpoint]

  AUTHORIZATION [sa]

  STATE=STARTED

  AS HTTP (PATH=N'/sql/training', PORTS = (CLEAR), AUTHENTICATION = (NTLM, KERBEROS, INTEGRATED), SITE=N'*', CLEAR_PORT = 5487, COMPRESSION=DISABLED)

FOR SOAP (

   WEBMETHOD 'GetSqlInfo'

    (name='master.dbo.xp_msver',

SCHEMA=STANDARD),

   WEBMETHOD 'DayAsNumber'

    (name='master.sys.fn_MSdayasnumber'),

   WSDL = DEFAULT,

   SCHEMA = STANDARD,

   DATABASE = 'master',

   NAMESPACE = 'http://tempUri.org/'

   );

GO

This is great so far, but I just found an ugly error that I think can be of some help. If you followed any blog post about reserving the SQL namespace prior to the creation of a SQL Endpoint you probably ran into an issue based on the following scenario. You reserved the SQL Endpoint namespace, and then decided to change it to something else and for some reason weren't able to create a new SQL Endpoint.

You now get weird errors like the user does not have permissions to register an endpoint. The best undocumented thing you can do is completely remove the reserved namespace. But how you ask, it is below. You will need to ensure the HTTP service is running using "net start HTTP" or "net start HTTP SSL" in a command line window on the Windows Server Operating System if it is not already started. Then run the below highlighted keyword with the appropriate SQL Endpoint web address in order to successfully add or remove your endpoint. FYI, if you want to remove an existing reserved namespace you must specify the exact address of the previous namespace.

• Register: sp_reserve_http_namespace N' [HTTP Web Address of the SQL Endpoint]'
  • sp_reserve_http_namespace N'http://[my machine name]: [port]/[sql endpoint relative path] '
    • sp_reserve_http_namespace N'http://*: 80/sql/datamart'
    • sp_reserve_http_namespace N'http://127.0.0.1: 80/sql/datamart'
    • sp_reserve_http_namespace N'http://*: 5487/sql/datamart'
    • sp_reserve_http_namespace N'http://127.0.0.1: 5487/sql/datamart'
• Unregister: sp_delete_http_namespace_reservation N' [HTTP Web Address of the SQL Endpoint]'
  • sp_delete_http_namespace_reservation N'http://[my machine name]: [port]/[sql endpoint relative path] '
    • sp_delete_http_namespace_reservation N'http://*: 80/sql/datamart'
    • sp_delete_http_namespace_reservation N'http://127.0.0.1: 80/sql/datamart'
    • sp_delete_http_namespace_reservation N'http://*: 5487/sql/datamart'
    • sp_delete_http_namespace_reservation N'http://127.0.0.1: 5487/sql/datamart'

Also, if you want to check on the status of your reserved namespace in HTTP.sys you have to use the HttpCfg.exe utility in a command prompt. The HttpCfg.exe is part of the Windows Server support tools. So to Query HTTP.sys go to a command prompt and type in "httpcfg query urlacl".

Here are some other things you shoul know about SQL 2005 Endpoints

To manage HTTP endpoints, you use CREATE ENDPOINT, ALTER ENDPOINT and DROP ENDPOINT, but you must have the required permissions to create, modify, or drop an endpoint.

When you execute CREATE ENDPOINT to create an endpoint, SQL Server 2005 runs the statement and registers the endpoint with the HTTP.SYS. Depending on the context in which the endpoint statement is specified, SQL Server 2005 impersonates the caller as follows:

If you execute the statement in the context of a Windows account, SQL Server 2005 impersonates the caller to register the endpoint with HTTP.SYS.

If you execute the statement in the context of a SQL Server account, for example, sa or some other SQL Server login, SQL Server 2005 impersonates the caller by using the SQL Service account, specified when SQL Server is installed, to register the endpoint with HTTP.SYS.