Troubleshooting the SQL Endpoint Configuration
     Q1.   I cannot create an endpoint when I log as a Windows administrator, why?
     Q2.   I created an endpoint but I cannot connect to it. How do I check if the configuration is set properly?
     Q3.   How do I know which permissions have been granted to an endpoint?
     Q4.   Can I connect to a SQL server HTTP endpoint with Internet Explorer?
     Q5.   I can connect to my endpoint but I cannot get the WSDL information, what should I check?
     Q6.   How can I get the WSDL document from a .NET application?
     Q7.   What trace flags can I use to get more information from SQL Web Services?
     Q8.   Why would a request get close in the middle of a server response?
Troubleshooting the Configuration with the Windows HTTP API driver
     Q9.   What is the Windows HTTP API driver?
     Q10. Are there any service dependencies on the Windows HTTP API to start or stop SQL server?
     Q11. What are HTTP namespace reservation and URL registration?
     Q12. How do I query for existing reservations?
     Q13. How does SQL reserve namespaces and register URLs?
     Q14. Are the reserved namespaces removed from the HTTP API driver when SQL server is uninstalled?
     Q15. I removed a namespace reservation that had been assigned to another service account and I still cannot register my URL under the new namespace, why?
     Q16. Why do I get an error: “SA does not have permission to register URL” when trying to create an endpoint?
     Q17. How are URLs being constructed when I call the DDL endpoint statements?
Troubleshooting Web Services Authentication
     Q18. Which authentication type is supported with integrated authentication?
     Q19.  I have chosen integrated authentication, but my .NET client is getting access denied. How do I verify my configuration?
     Q20. How do I configure my server to authenticate with Kerberos when the SQL service account is a domain user account?
     Q21. How can I verify the values for the servicePrincipalName attribute on the domain user account?
     Q22. Why is SQL authentication and basic only supported over SSL channels?
     Q23. How do I convert Win32 errors to text?

Q1.   I cannot create an endpoint when I log on as a Windows administrator, why?

When creating an endpoint, the server maps it to an existing object in the syslogins table. The object searched in the syslogins table is either the corresponding login of the user who executed the statement, or the user in the [AUTHORIZATION=<user>] clause of the DDL statement. In Yukon Beta1, users that belong to a Windows groups such as Builtin\administrators, do not have an object implicitly created in the syslogins table, thus one must be entered before creating an endpoint. Execute the following command to create a login that can then be used as the owner of the endpoint:

To verify the entry in the syslogins table execute the following query:

Once the entry is in the syslogins table, then executing the DDL to create an endpoint should succeed.

Q2.   I created an endpoint but I cannot connect to it. How do I check if the configuration is set properly?

When creating an HTTP endpoint, its state is configured as STOPPED by default unless it is specifically changed with the clause STATE=STARTED in the DDL statement.

To check this setting just execute the following query:

If value is 1, then the endpoint is stopped.  To change this state, one can drop and re-create the endpoint or just simply alter it to set the STATE=STARTED. Some other useful endpoint views for checking endpoint configurations are:

This view will list all webmethods mapped to the endpoints.

This view provides more details than sys.http_endpoints such as whether XSD schema and WSDL is enabled.

The next place to verify is in the connection permissions for the endpoint. After creating a new endpoint, only members of the sysadmin role and the endpoint owner can connect to it, unless a specific GRANT permission is set. The syntax for this permission is as follows:

More information on HTTP endpoint permissions is located in books online under the following title: “Granting HTTP Endpoint Permissions”.

Q3.   How do I know which permissions have been granted to an endpoint?

To view granted permissions on a given endpoint (or other server objects), administrators can query the following system tables:

A simple join of these tables would print a clear result set of the permissions granted on the server http endpoints:

Results:

 The symbol value ‘CO’ represents the ‘connect’ permission.

The following is a table of all possible permissions that can be set on a specific endpoint

Q4.   Can I connect to a SQL server HTTP endpoint with Internet Explorer?

Because of the intrinsic support of SOAP over the HTTP endpoint, Internet Explorer is not the ideal client for SQL Web Services. Only the WSDL documents can be retrieved using IE since WSDL those must be issued as GET request. The syntax to get a WSDL request using IE is the following: 

Q5.   I can connect to my endpoint but I cannot get the WSDL information, what should I check?

WSDL support is not enabled by default on an HTTP endpoint. To enable WSDL generation, the owner of the endpoint must use the WSDL = DEFAULT statement when creating or altering the endpoint as in the following example:

To verify that is value is set in the endpoint, execute the following query:

If the value is set to 1, then the WSDL generation is enabled on that endpoint. To retrieve the WSDL document, issue an HTTP GET request against the endpoint PATH with the ?wsdl query syntax as in the following example:

http://server/sql/mypath?wsdl 

Note that the SITE and PATH in the URL must match the endpoint configuration.

Q6.   How can I get the WSDL document from a .NET application?

This is a quick application that will do an HTTP GET, and print the response. Having installed the .NET Framework, one can build it by saving it as HttpGet.cs, and then running the following program from the command prompt:

The WSDL information can be retrieved by executing the following line:

where URL is the full HTTP URL of the location. For example,

It will output all of the details of the HTTP response — the status line, the headers, and the body. If it fails on something like server not found, it will likely throw an exception and print a call stack. (There is little error handling in this code and caution should be used when running.)

Q7.   What trace flags can I use to get more information from SQL Web Services?

The following list of trace flags can help you get more details from SQL server:

When SQL server is running as service (normal case), the console output is logged in the ERRORLOG file located at c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG. You can view this in a notepad, or by typing the following at the command prompt:

Q8.   Why would a request get closed in the middle of a server response?

An error can sometimes occur in the middle of the execution phase when SQ L server is sending data back to the client over HTTP. If an error occurs, SQL server will close the HTTP connection to avoid sending inconsistent data. The following possible errors can cause this server behavior:

• Severe errors in the network interface connection layer
• Out of memory conditions
• Invalid XML characters that cannot simply be entitized
• Errors executing a UDF
• Errors executing an SP that is in dataset mode

One solution to avoid the connection closure for Invalid XML characters is to alter the endpoint to get CHARSET = SQL (see books online title “Accessing SQL Server Using Native HTTP SOAP Support” for details on how to create an endpoint). Here is an example:

The default option for CHARACTER_SET is XML. Any response with this option will fail if an invalid XML character is present in the returned data. If the option SQL is selected, then any invalid XML character that exists will be entitized and send a response to the user. Entitization is the process in which XML special characters are converted to XML non-special characters in order to escape them from XML parsing. Here is an example:

Other special characters are escaped in the form of #x(four-digit-hex) e.g. #x00B7 where the four digits represent a equivalent character in Unicode. (See XML 1.0 recommendation for more details here http://www.w3.org/TR/REC-xml#NT-BaseChar .) Because the SQL character set is larger that the one for XML, the XML parser will not be able to convert some characters. When this happens, the SQL engine will return an error. If the error happened in the middle of sending a response, then the connection gets closed. To avoid this problem, one can set the CHARACTER_SET option to be SQL. One downside of this option is that because of the special MS typical escape sequence, it is not warranteed to work with all apps.

The Windows HTTP API driver is a kernel level application that controls two services: The HTTP service for all HTTP protocol requests and the HTTPFILTER service for encrypted HTTP requests over the SSL channel. Administrators can query the status of these services using the following utility from Windows Server 2003 from the command prompt:

To start and stop these services use:

These services are automatically initiated when configuring an endpoint from SQL server.

Also refer to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/http/http/http_api_overview.asp for more information on the Windows HTTP programming interfaces.

Q10.  Are there any service dependencies on the Windows HTTP API to start or stop SQL server?

SQL server does not have any dependencies on the HTTP service to start or stop. However when the SQL service starts and initializes an HTTP endpoint, a handle is taken from the HTTP service. This handle will keep the HTTP service from shutting down while SQL server is running. Unfortunately, the message presented to the user is not very clear. See below:

To stop the HTTP service then, one must stop SQL Server first (or drop all the endpoints). The SQL service does not set any start and stop service dependencies on the HTTP for the following reasons:

1. The HTTP service is supported only on Windows 2003 servers and not all SQL installations will be on this version of Windows. For example, Windows XP and Windows 2000
2. Not every SQL installation will be using the HTTP and SOAP services
3. Stopping the HTTP service should not force to stop the SQL service

Q11.  What are HTTP namespace reservation and URL registration?

Reservation is the process in which the HTTP API allows access to the URL namespace on a machine. Applications such SQL server can reserve the first portion of the URL (e.g. http://server:80/sql). The HTTP driver will then route to the application any requests that match that portion of the URL. Application can then register subsequent URLs under the root of the reserved namespace. Windows system administrators must reserve a namespace on behalf of the service account before the application can register subsequent URLs under that namespace.

See to the following link for MSDN details: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/http/http/namespace_reservations_registrations_and_routing.asp

Q12.  How do I query for existing reservations?

To query existing namespace reservations run the following application from the command prompt:

This application can be found in the Windows support tools from the Windows Server 2003 CD. The following instructions explain how to install the tools.

Q13.  How does SQL reserve namespaces and register URLs?

There are two stored procedures to reserve and delete namespaces in the HTTP API driver.  The procedures are the following:

Reserve:

Delete:

 Where <scheme> is http or https

      <site> maps to the SITE value in the endpoint DDL

      <port> maps to the PORT value in the endpoint DDL

To register subsequent URLs under the reserved namespace, one must execute the CREATE/ALTER endpoint statements.

Q14.  Are the reserved namespaces removed from the HTTP API driver when SQL server is uninstalled?

Uninstalling SQL Server does not remove the namespaces from the HTTP API repository. Therefore, be aware that when reinstalling SQL server under a different service account, the previously reserved namespaces will not match to the new service account. This assumes that those namespaces were reserved explicitly by calling the stored procedure sp_reserve_http_namespace. If SQL server has been uninstalled, then Windows administrators need to remove any reserved namespaces using the HTTPCFG.EXE utility.

Q15.  I removed a namespace reservation that had been assigned to another service account and I still cannot register my URL under the new namespace, why?

The HTTP API driver will cache all the namespaces in memory. That prevents the driver from immediately updating in memory any changed made in the HTTP API repository. Therefore, you need to restart the HTTP service. Before you stop HTTP you might need to stop MSSQLSERVER as well.

 Q16.  Why do I get an error: "SA does not have permission to register URL" when trying to create an endpoint?

This is a common error when the SQL service account does not have Windows administrator rights. Members of the sysadmin role, such as SA, must use the SQL service account to access any Windows resources, including namespace reservations. The problem can be solved by taking one of the following steps:

• Reserving the namespace and URL before calling into the DDL endpoint statements
• If you must run this statement as SA, then change the SQL service account to run as Local System. (Running SQL server as Local System should always be the last option for security reasons)

Q17.  How are URLs being constructed when I call the DDL endpoint statements?

The URL and namespaces are formed from the DDL statement before calling into the HTTP API. For example, if the statement is the following:

The SQL server DDL statement then calls into the HTTP API function HttpAddUrl (see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/http/http/httpaddurl.asp) and passes the following parameter as the URL:

Also in this case, there is a second call to HttpAddURL() with the following parameter for the SSL scheme:  

Q18.  Which authentication type is supported with integrated authentication?

Integrated authentication provides support for either NTLM or Negotiate authentication. The authentication type for the request depends on the client’s authorization header. The client request will send either an HTTP authorization header with NTLM type or with Negotiate type. If the authorization request header calls for negotiate, then the server will choose Kerberos as the authentication type. Most Windows HTTP client libraries use the Kerberos authentication scheme by default if no other authentication type is configured. If the negotiate header is chosen, the server will only try to authenticate using Kerberos.

For more details on SQL Web Services authentication, refer to books online under the title “Authentication Types Overview (Managing HTTP Endpoints)”

Q19.  I have chosen integrated authentication, but my .NET client is getting access denied. How do I verify my configuration?

First, verify that all the permissions and state of the endpoint are configured properly. Refer to the previous question in this document for more details on the subject. Then review the client’s code (if using a .NET application) or the IE configuration settings to assure that the authentication type is in fact set to Windows integrated mode. One step for troubleshooting is to configure the endpoint to only one authentication type that is not integrated, e.g., digest authentication. If the application can authenticate with digest and not with integrated, then you can continue troubleshooting the endpoint configuration. If it cannot authenticate with other types, then the issue must not be with integrated authentication.

The .NET and Windows HTTP libraries (including IE’s implementation) will send Kerberos authentication requests usually by default. To authenticate with Kerberos, one of the following conditions must be met on the server configuration:

1. The SQL Server service account is either LocalSystem or NetworkService

Or,

2. The SQL server service account is a *domain* user account and not a *local* user account.

Q20.  How do I configure my server to authenticate with Kerberos when the SQL service account is a domain user account?

If the service account is a domain user account, the object for this account in the active directory must have an attribute named ‘servicePrincipalName’. The values for this attribute are the following:

The fully qualified name must be in the form of servername.domain.company.com. To configure this in SQL server, there are two stored procedures that can be called.

To call these procedures, one must be part of the sysadmin role. However, for these procedures to be effective, the caller also must also have read-write permissions on the domain user account in the active directory. For more details on this subject, please refer to “Registering Kerberos SPNs (Managing HTTP Endpoints)” in books online.

Q21.  How can I verify the values for the servicePrincipalName attribute on the domain user account?

An LDAP search to the active directory can retrieve any existing values for this attribute. It is important to know if existing domain user accounts are already configured with the necessary attributes for Kerberos authentication: HOST/<machinename> and HOST/<fully-qualified-name>. If these values are duplicated across the domain controller on other domain accounts, the Kerberos authentication for this server will always fail. This is because the domain controller cannot reliably validate the authenticity of the service (SQL server).

Windows Support tools provide a utility called: LDP.EXE. You only need read-rights on the domain controller to verify this.

1. Launch the ldp.exe application.
2. Select Connection | Connect (use the proper server).

3. Select Connection | Bind. Enter your user credentials.
4. Select View | Tree. Leave the BaseDN empty. Click OK  (this takes you to the root of the Active Directory).
5. Select Browse | Search. Add a filter to match your <SERVER NAME>.
6. Filter: (servicePrincipalName=HOST/<SERVERNAME>*)



7. Select Options

8. Enter in the Attributes box: servicePrincipalName;
9. Select OK, then select RUN.

The results from this query will look like this:

Make sure that there are not duplicate entries (DN) with the same servicePrincipalName string.

Q22.  Why is SQL authentication and basic only supported over SSL channels?

Security is the main reason. When sending an authenticated request using basic or SQL authentication schemes, the headers can easily be found over a clear port and can also be easily decoded.

Q23.  How do I convert Win32 errors to text? 

If the first four digits of the error code are 8007, this indicates a Win32 or network error. Windows error handling maps the numeric value to a full error description. These numeric values are usually returned to the user in hexadecimal. When a Win32 error message is displayed, take the last four digits of the error code and convert them from hexadecimal to decimal. Then use the following command to get the full description:

For example, if the error returned is 0x8007054B, convert the 054B to decimal (1355). Then type the following:

The net command returns the error description:

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document.  Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

© 2003 Microsoft Corporation. All rights reserved.

Microsoft is a registered trademark of Microsoft Corporation in the United States and/or other countries. The names of actual companies and products mentioned herein may be the trademarks of their respective owners.