Reminiscing: Native HTTP in SQL Server 2005

A long time ago (c. 2001), I had a stint on the SQL Server team (it lasted about 9 months). One of the things I did on the team was write the first specification for a Native HTTP stack in SQL Server. The idea was to make any stored procedure available as a SOAP procedure. 

Not too long ago, I got an email from an old colleague that reminded me of that work, so I searched around to see what happened to it, and found this: 

https://www.programmersheaven.com/2/SQL-server-2005-school-lesson-9 

SQL Server 2005 provides native HTTP support so that you can programmatically interact with a database across the Internet or through an internal firewall by using a Web service. This feature reduces the developer's effort in working with data across the Internet.

I opened up one of my old specs (I have a copy of every spec I've ever written) on this feature and it's not that different from the final feature -- at least on the surface. I'm assuming that a lot of things (possibly most things) changed in the actual implementation from my original specification. But, I'm still somewhat excited about the fact that I did have some impact on SQL Server. :)

As an interesting comparison, here is the syntax from my spec (dated August 2001, and referring to "SQL Server Yukon"):

CREATE HTTPENDPOINT endPointName AS [HTTPSITE “<WebSite>"] PATH "<url>"
PORTS {CLEAR [(<ClearPort>)] | SSL [(<SSLPort>)] [, REDIRECT [(<ClearPort>)]] | CLEAR [(<ClearPort>)], SSL [(<SSLPort>)]}
AUTH[ENTICATE] {ANON | BASIC | DIGEST | INTEGRATED }* [ANONACCT “<acct>” PASSWORD "password"]
[PERMIT {{SPEXEC, ADHOCBATCH}* | NONE}
[DEFAULT TO {DATABASE "<database>" | INSTANCE}]
[ALLOW {SESSIONS | NOSESSIONS}]
[NAMESPACE "<namespace>"]
[DEFAULTSCHEMA {NOSCHEMA | SCHEMA | NOANNOTATIONS | SCHEMAONLY}]
[RESTRICT IP {NONE|ALL} [EXCEPT ({SINGLE <ipaddress> | RANGE <ipaddress>:<mask>}*)
[STATE {STARTED | STOPPED}]

Here's the syntax from the actual shipping product. Looks like the feature was generalized from a HTTP stack to a SOAP stack (including general TCP support), but a lot of the core features are still there.

CREATE ENDPOINT endPointName [AUTHORIZATION login]
STATE = { STARTED | STOPPED | DISABLED }
AS  { TCP | HTTP | VIA | ...} 
 (
  PATH = 'url'
 , PORTS = ({CLEAR | SSL} [,... n])
  [ SITE = {'*' | '+' | 'webSite' },]
  [, CLEAR_PORT = clearPort ]
  [, SSL_PORT = SSLPort ]
   , AUTHENTICATION =({BASIC | DIGEST | INTEGRATED} [,...n])
  [, AUTH_REALM = { 'realm' | NONE } ]
  [, DEFAULT_LOGON_DOMAIN = {'domain' | NONE } ]
  [, RESTRICT_IP = { NONE | ALL } ]
  [, COMPRESSION = { ENABLED | DISABLED } ]
  [,EXCEPT_IP = ({ <4-part-ip> | <4-part-ip>: } [,...n])
)

One thing that made the episode of my life interesting was that I was forced to learn a lot about the internals of SQL Server and I had to learn a lot about how SOAP and WSDL work. The "fun" part was figuring out how to automatically generate a WSDL file that describes an arbitrary SQL stored procedure -- including mapping SQL datatypes into SOAP datatypes. For what it's worth, I still have a nervous twitch whenever someone mentions WSDL.

My team was also tasked with building a generalized HTTP pipeline within SQL server that could be used by any SQL Server feature to expose an HTTP-based pipeline. That was interesting because SQL Server's core protocol (called TDS) is connection- and transaction- oriented, while HTTP is, at least in theory, stateless.

PS. There's a punchline to this story, but I may have to wait until after I retire to tell that one. :)