Creating HTTP endpoint fails with 7850 error.
Creating a HTTP endpoint in SQL Server 2005 or SQL Server 2008 may fail with the following error messages:
Msg 7850, Level 16, State 1, Line 1
The user 'domainmyuser' does not have permission to register endpoint 'training_sql_endpoint' on the specified URL. Please ensure the URL refers to a namespace that is reserved for listening by SQL.
Msg 7807, Level 16, State 1, Line 1
An error ('0x80070005') occurred while attempting to register the endpoint 'training_sql_endpoint'.
The error message is actually incorrect in this context, it instead should report that it is the SQL Service account and also direct you to reserve the namespace explicitly, such as (this is what SQL Server 2008 now has):
The SQL Server Service account does not have permission to register the supplied URL on the endpoint '%.*ls'. Use sp_reserve_http_namespace to explicitly reserve the URL namespace before you try to register the URL again.
When you run CREATE ENDPOINT to create a HTTP endpoint, this is done under the context of the SQL Server service account. If the namespace reservation does not already exist, then SQL will implicitly create the reservation. However, this requires that the SQL Server service account have local administrator privileges on the computer. If the SQL Service account does not have local administrator, on SQL Server 2005 it will fail with the message noted earlier.
To resolve this you have two options:
1. Add the SQL Server service account to the local administrators group, restart and then run the CREATE ENDPOINT again.
2. Or, explicitly reserve the name space while logged on as a Windows Authentication user that has Local Administrator on the computer and sysadmin on SQL, *before* you run CREATE ENDPOINT. For example:
Then when you run the CREATE ENDPOINT, the SQL Service account will not have to reserve the namespace because it already exists, and proceed with creating the endpoint. Note that when you reserve a namespace explicitly, you need to be sure that the string of the namespace you reserve matches the parameters in the CREATE ENDPOINT statement. So for the namespace above, the CREATE ENDPOINT would need to look like the following for SQL to match it up correctly:
CREATE ENDPOINT [myendpoint]
AS HTTP (PATH=N'/sql/myfolder', PORTS = (CLEAR), AUTHENTICATION = (NTLM, KERBEROS, INTEGRATED), SITE=N'*', CLEAR_PORT = 2050, COMPRESSION=DISABLED)
The following link has more on this in “Identifying the Namespace for an Endpoint” http://msdn.microsoft.com/en-us/library/ms190614.aspx