Least privilege and HTTP endpoints

A few weeks ago, I was surprised by an error message when attempting to create an HTTP endpoint with CREATE ENDPOINT. The error was "You do not have permission to perform this operation". The reason I was surprised was that SQL Server 2005 was running in a domain environment and I was logged on as domain administrator at the time. This meant I had sa-level database privledges, and privileges on the OS as well. Hmmm…

The reason for this turned out to be pretty straightforward. I was running the SQL Server process as a relatively unprivileged account, principal of least privilege and all that. When you create an HTTP endpoint, SQL Server issues a "namespace reservation" for part of the HTTP namespace. The reservation is used when other applications (like IIS 6.0) use the HTTP.sys implementation at the same time. It attempts to issue the reservation *using the identity of the principal this is running the SQL Server process*, not as your currently logged on user.

The way to accomplish the reservation under these conditions is to use a system stored procedure, sp_reserve_http_namespace.
It looks like this:

sp_reserve_http_namespace N'http://mymachine.mydomain.com:80/mydir'

Note that in order for this procedure to work, you must be logged in to SQL Server as a Windows login that has OS admin privileges. And so I was, and it worked. So did CREATE ENDPOINT… FOR HTTP. However I noticed that, in my CREATE ENDPOINT DDL statement I had to use the exact machine domain name for the SITE operand, rather than the default ('*' which means "use all machine names not otherwise reserved"). Oh.

That had everything to do with my input to sp_reserve_http_namespace. Using:

sp_reserve_http_namespace N'http://*:80/mydir'

instead, set things up so that I could use '*' as a SITE operand. Whew.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.