Section 8.5. Managing HTTP Endpoints


8.5. Managing HTTP Endpoints

An HTTP endpoint is an interface through which HTTP-based clients send queries to the server. You must use the CREATE ENDPOINT T-SQL statement to configure the SQL Server instance to listen natively for HTTP requests before you can send a native HTTP/SOAP request to the server.

SQL Server HTTP endpoints can listen and receive requests on any valid port including TCP port 80the same port used by the IIS World Wide Web Publishing service. Each URLwhether used for an HTTP endpoint or by IISis registered with the Windows operating system through the system HTTP listener process (http.sys).

This section discusses how to create, change, and remove endpoints, and how to grant and change permissions on endpoint objects.

8.5.1. Creating an HTTP Endpoint

An endpoint is an interface on which the server listens for HTTP requests from clients. The CREATE ENDPOINT T-SQL statement creates and configures an endpoint and web methods exposed by the endpoint.

The syntax for the CREATE ENDPOINT statement is:

 CREATE ENDPOINT endPointName [ AUTHORIZATION login ] STATE = { STARTED | STOPPED | DISABLED } AS { HTTP | TCP } (    <protocol_specific_arguments>         ) FOR { SOAP | TSQL | SERVICE_BROKER | DATABASE_MIRRORING } (    <language_specific_arguments>         ) 

where:


endPointName

The name of the endpoint being created.


AUTHORIZATION login

The SQL Server or Windows account that is assigned ownership of the new endpoint. The caller must have IMPERSONATE permissions on the specified login to specify AUTHORIZATION. If this clause is omitted, the caller becomes the owner.

You can reassign ownership of an endpoint with the ALTER ENDPOINT statement.


STATE = { STARTED | STOPPED | DISABLED }

The state of the endpoint when created.


STARTED

The endpoint is started and listening for requests.


DISABLED

The endpoint is disabled and does not respond to requests.


STOPPED

The endpoint is stopped and returns an error in response to requests.

STOPPED is the default state.

Use the ALTER ENDPOINT statement to change the state of an endpoint.


AS { HTTP | TCP } ( <protocol_specific_arguments>)

Specifies the transport protocol to useHTTP or TCP. The AS clause specifies transport protocol-specific information for the endpoint, including the listening port, the authentication method, and a list of IP addresses that are restricted from accessing the endpoint.

The AS clause is described in the following subsection, "Protocol-specific arguments."


FOR { SOAP | TSQL | SERVICE_BROKER | DATABASE_MIRRORING } ( <language_specific_arguments>)

Specifies the payload typeSOAP, TSQL, SERVICE_BROKER, or DATABASE_MIRRORING. The FOR clause defines the payload and specifies additional SOAP configuration information, such as whether ad hoc queries are allowed, whether to return the XSD schema for the result set, and how to handle invalid characters in the XML.

The FOR clause is described in the upcoming subsection "Language-specific items."

8.5.1.1. Protocol-specific arguments

The syntax for HTTP protocol-specific items in the AS clause is:

     <
AS HTTP_protocol_specific_arguments
> ::=
AS HTTP ( PATH = 'url', AUTHENTICATION =( { BASIC | DIGEST | INTEGRATED | NTLM | KERBEROS } [ ,...n ] ), PORTS = ( { CLEAR | SSL} [ ,... n ] ) [ SITE = {'*' | '+' | 'webSite' },] [, CLEAR_PORT = clearPort ] [, SSL_PORT = SSLPort ] [, AUTH_REALM = { 'realm' | NONE } ] [, DEFAULT_LOGON_DOMAIN = { 'domain' | NONE } ] [, RESTRICT_IP = { NONE | ALL } ] [, COMPRESSION = { ENABLED | DISABLED } ] [, EXCEPT_IP = ( { <4-part-ip> | <4-part-ip>:<mask> } [ ,...n ] ) ] )

where:


PATH

The URL path for the endpoint on the host computer specified by the SITE argument. The PATH together with the SITE argument specifies the URL that the client uses to send HTTP SOAP requests to the server.

For example, in the URL http://<site>/<path>, site is the computer specified by the SITE argument and path is the URL specified by the PATH argument.


AUTHENTICATION

The authentication method for the endpointone or more of BASIC, DIGEST, INTEGRATED, NTLM, or KERBEROS. (If you specify more than one authentication method, separate the methods with commas).

Authentication is discussed in detail in the "Endpoint Authentication" section later in this chapter.


PORTS = ( { CLEAR | SSL} [ ,...n ] )

One or more listening ports for the endpoint. The listening ports can be CLEAR, SSL, or both. If CLEAR is specified, incoming requests must use standard HTTP requests (http://). If SSL is specified, incoming requests must use Secure HTTP requests (https://).


SITE = {'*' | '+' | ' webSite' }

The name of the host computer. Either a specific hostname webSite or one of two wildcards can be specified:


Plus sign (+)

Specifies listening on all possible hostnames for the computer


Asterisk (*)

Specifies listening on all possible hostnames for the computer that are not otherwise explicitly reserved

If the SITE argument is not specified, the default value is asterisk (*).


CLEAR_PORT

The listening clear port number, if the PORTS argument specifies clear port listening (PORTS = CLEAR). The default value is 80 if CLEAR_PORT is not explicitly specified.


SSL_PORT

The listening SSL port number, if the PORTS argument specifies SSL port listening (PORTS = SSL). The default value is 443 if SSL_PORT is not explicitly specified.


AUTH_REALM = { ' realm' | NONE }

The hint returned to the client as part of the HTTP challenge for digest authentication (AUTHENTICATION = DIGEST). The default value is NONE.


DEFAULT_LOGIN_DOMAIN{ ' domain' | NONE }

The default login domain for basic authentication (AUTHENTICATION = BASIC). The default value is NONE.


RESTRICT_IP = { NONE | ALL }

This parameter together with EXCEPT_IP specifies which IP addresses can send requests to an endpoint:


NONE

Indicates that all IP addresses except the list specified by the EXCEPT_IP parameter can send requests to the endpoint.


ALL

Indicates that only IP addresses specified in the EXCEPT_IP parameter can send requests to the endpoint (i.e., all IP addresses are restricted except for the list defined by the EXCEPT_IP parameter).

The default is NONE, allowing all IP addresses to send requests to the endpoint.


COMPRESSION = { ENABLED | DISABLED }

Specifies whether the endpoint honors gzip-encoded requests and returns compressed responses. The default value is DISABLED, indicating that gzip requests are not honored.


EXCEPT_IP = ( { < 4-part-ip> | < 4-part-ip>:<mask> } [ ,...n ] )

A list of IP addresses that together with the RESTRICT_IP argument specifies which IP addresses are either allowed to send or not allowed to send requests to the endpoint.

The syntax for TCP protocol-specific items in the AS clause is:

 <
AS TCP_protocol_specific_arguments
> ::=
AS TCP ( LISTENER_PORT = listenerPort [ , LISTENER_IP = ALL | (<4-part-ip> | <ip_address_v6> ) ] [ , RESTRICT_IP = ALL | NONE ] [ , EXCEPT_IP = ( { <4-part-ip> | <4-part-ip>:<mask> } [ ,...n ] ) ]

where:


LISTENER_PORT

The listening port number for the TCP/IP protocol. The default value is 4022.


LISTENER_IP = ALL | (< 4-part-ip> | < ip_address_v6> )

The IP address that the endpoint listens on. The default value is ALL, meaning the endpoint will accept a connection on any valid IP address.


RESTRICT_IP

The RESTRICT_IP clause is described in the preceding discussion about HTTP protocol-specific items.


EXCEPT_IP

The EXCEPT_IP clause is described in the preceding discussion about HTTP protocol-specific items.

8.5.1.2. Language-specific arguments

The syntax for SOAP language-specific arguments is:

 <
FOR SOAP_language_specific_arguments
> ::=
FOR SOAP( [ { WEBMETHOD [ 'namespace' .] 'method_alias' ( NAME = 'database.owner.name' [ , SCHEMA = { NONE | STANDARD | DEFAULT } ] [ , FORMAT = { ALL_RESULTS | ROWSETS_ONLY | NONE} ] ) } [ ,...n ] ] [ BATCHES = { ENABLED | DISABLED } ] [ , WSDL = { NONE | DEFAULT | 'sp_name' } ] [ , SESSIONS = { ENABLED | DISABLED } ] [ , LOGIN_TYPE = { MIXED | WINDOWS } ] [ , SESSION_TIMEOUT = timeoutInterval | NEVER ] [ , DATABASE = { 'database_name' | DEFAULT } [ , NAMESPACE = { 'namespace' | DEFAULT } ] [ , SCHEMA = { NONE | STANDARD } ] [ , CHARACTER_SET = { SQL | XML }] [ , MAX_SOAP_HEADERS_SIZE = { int | DEFAULT }] )

where:


WEBMETHOD [ 'namespace' .] ' method_alias'

A method for which you can send SOAP requests to the endpoint. Each WEBMETHOD clause describes a single method. However, an endpoint can expose multiple methods. The ALTER ENDPOINT statement can be used to add or remove web methods from an existing endpoint.

If the namespace is not specified, the namespace of the endpoint is used.


NAME = ' database.owner.name'

The name of the stored procedure or UDF corresponding to the web method. The NAME must be specified as a three-part name in the format database.owner.name.


SCHEMA = {NONE | STANDARD | DEFAULT}

Specifies whether an inline XSD schema is returned in the SOAP response from the web method.


NONE

The web method is omitted from the schema if one is sent in the SOAP response.


STANDARD

An XSD schema is not returned with the SOAP response.


DEFAULT

The value specified for the endpoint SCHEMA option determines whether an XSD schema is returned.


FORMAT= { ALL_RESULTS | ROWSETS_ONLY | NONE }

Specifies whether the row count, error messages, and warnings are returned with the result set.


ALL_RESULTS

The row count, error messages, and warnings are returned with the result set in the SOAP response.


ROWSETS_ONLY

Only the result set is returned in the SOAP response.


NONE

SOAP-specific markup is suppressed from the SOAP response.

The default value is ALL_RESULTS.


BATCHES = { ENABLED | DISABLED }

Specifies whether ad hoc SQL requests are supported on the endpoint through the sqlbatch( ) method. The default value is DISABLED.

Ad hoc SQL requests are detailed earlier in this chapter in the section "SQL Batches."


WSDL = { NONE | DEFAULT | ' sp_name' }

Specifies whether a WSDL document is generated and returned for WSDL queries submitted to the endpoint.


NONE

No WSDL file is generated or returned for WSDL queries to the endpoint.


DEFAULT

The default WSDL file is generated and returned for WSDL queries to the endpoint.


'sp_name'

A custom WSDL file generated by the stored procedure sp_name is returned for WSDL queries on the endpoint.


SESSIONS = { ENABLED | DISABLED }

Specifies whether SQL Server allows sessionsmultiple SOAP request/response pairs identified as part of a single SOAP session. The default value is DISABLED.


LOGIN_TYPE = { MIXED | WINDOWS }

The SQL Server authentication mode for the endpoint. LOGIN_TYPE is used only to further restrict the authentication mode for endpoints based on the authentication mode selected when the SQL Server instance was installed.


WINDOWS

Only Windows authentication is used.


MIXED

Either SQL or Windows authentication is used. Mixed authentication cannot be used if it was not selected as the global authentication mode when SQL Server was installed. An endpoint using SQL Server-based authentication must be configured to use an SSL port.

The default value is WINDOWS.


SESSION_TIMEOUT = int | NEVER

The time, in seconds, after which a SOAP session expires at the server if no requests (identified by a session ID in the SOAP header) are made. After the session times out, subsequent requests with the expired session ID return a SOAP fault. The SOAP session never expires if a value of NEVER is specified for SESSION_TIMEOUT.


DATABASE = { ' database_name' | DEFAULT }

The database in which the operation is executed. If not specified or if DEFAULT is specified, the default database for the login is used.


NAMESPACE = { 'namespace' | DEFAULT }

The namespace for the endpoint. If not specified or if specified as DEFAULT, http://tempuri.org is used.


SCHEMA = { NONE | STANDARD }

Specifies whether an XSD schema is returned by the endpoint with the SOAP result sent to the client.


STANDARD

An inline schema is included in the SOAP response.


NONE

The inline schema is not included in the SOAP response.

An inline schema is required to load SOAP results into a .NET DataSet object.

The default is STANDARD.


CHARACTER_SET = { SQL | XML }

Specifies the behavior if the results of an operation include characters that are not valid in XML.


XML

An error is returned if the result includes invalid XML characters.


SQL

Invalid characters are encoded as character references and returned in the resultsthis might result in invalid XML.

The default value is XML.


MAX_SOAP_HEADERS_SIZE = { int | DEFAULT }

Specifies the maximum size, in bytes, of the SOAP header within the SOAP envelope. The server throws a parsing error if the SOAP header is larger than the specified value. The maximum header size is 8KB if DEFAULT is specified or if a value is not specified.

The syntax for Service Broker-specific items is:

 <
FOR SERVICE_BROKER_language_specific_arguments
> ::=
FOR SERVICE_BROKER ( [ AUTHENTICATION = { WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] | CERTIFICATE certificateName | WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] CERTIFICATE certificateName | CERTIFICATE certificateName WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] } ] [ , ENCRYPTION = {DISABLED | SUPPORTED | REQUIRED } [ALGORITHM {RC4 | AES | AES RC4 | RC4 AES } ] ] } [ , MESSAGE_FORWARDING = { ENABLED | DISABLED } ] [ , MESSAGE_FORWARD_SIZE = forwardSize ] )

where:


AUTHENTICATION

The TCP/IP authentication requirements for connections for the endpointeither WINDOWS or CERTIFICATE. The default is WINDOWS. If both authentication methods are specified, they are attempted in the order specified until one succeeds or both fail.


WINDOWS

Windows Authentication is used to authenticate the endpoints users. You can optionally specify an authorization methodNTLM, KERBEROS, or NEGOTIATEto force Windows Authentication to use that authorization method. If you specify NEGOTIATE, the endpoint uses Windows negotiating protocol to select either NTLM or Kerberos. NEGOTIATE is the default authorization method.


CERTIFICATE

Endpoint connects using the specified certificate to establish identity for authorization. The endpoint needs a certificate with the public key for the specified certificate.


ENCRYPTION = {DISABLED | SUPPORTED | REQUIRED }

Specifies whether encryption is used in the process.


DISABLED

Data sent over the connection is not encrypted.


SUPPORTED

Data is encrypted if the opposite endpoint connection specifies SUPPORTED or REQUIRED.


REQUIRED

Data must be encrypted. The opposite endpoint must specify SUPPORTED or REQUIRED for the ENCRYPTION option.

The default is REQUIRED.


ALGORITHM

Specifies the encryption algorithm. If both endpoints specify different encryption algorithms, the accepting endpoint prevails.


MESSAGE_FORWARDING = { ENABLED | DISABLED }

Specifies whether messages received by the endpoint for services located elsewhere are forwarded.


ENABLED

Messages are forwarded if a forwarding address is available.


DISABLED

Messages for services located elsewhere are discarded.

The default value is DISABLED.


MESSAGE_FORWARD_SIZE

Specifies the maximum amount of storage, in megabytes, allocated for use by the endpoint for storing messages that will be forwarded.

The syntax for database mirroring-specific items is:

 <
FOR DATABASE_MIRRORING_language_specific_arguments
> ::=
FOR DATABASE_MIRRORING ( [ AUTHENTICATION = { WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] | CERTIFICATE certificateName } ] [ [ , ] ENCRYPTION = { DISABLED |SUPPORTED | REQUIRED } [ ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ] ] } [ , ] ROLE = { WITNESS | PARTNER | ALL } )

where:


AUTHENTICATION

The AUTHENTICATION clause is described in the preceding discussion about Service Broker-specific items.


ENCRYPTION = { DISABLED |SUPPORTED | REQUIRED }

The ENCRYPTION clause is described in the preceding discussion about Service Broker-specific items.


ROLE = { WITNESS | PARTNER | ALL }

Specifies the database mirroring role or roles that the endpoint supports in the SQL Server mirroring process.


WITNESS

The endpoint performs the role of witness in the mirroring process.


PARTNER

The endpoint performs the role of partner in the mirroring process.


ALL

The endpoint performs the role of both witness and partner in the mirroring process.

8.5.2. Altering an ENDPOINT

The ALTER ENDPOINT statement lets you change an existing endpointyou can add methods to the endpoint, change or drop existing endpoint methods, and change properties of the endpoint.

In the ALTER ENDPOINT statement, specify only properties that you want to updateunspecified properties will remain unchanged.

Endpoints can be altered by members of the sysadmin role, the owner of the endpoint, or by users granted ALTER ANY ENDPOINT permission or ALTER ON ENDPOINT permission for the specific endpoint. The ALTER AUTHORIZATION statement changes the owner of an existing endpointthis command is discussed in the "Endpoint Authentication" section later in this chapter.

Most of the clauses and arguments for the ALTER ENDPOINT statement are the same as those for the CREATE ENDPOINT statement, discussed in the previous section. Only different arguments and clauses are discussed in this section.

The syntax for the ALTER ENDPOINT statement is:

 ALTER ENDPOINT endPointName [ AFFINITY = { NONE | <64bit_integer> | ADMIN } ] [ STATE = { STARTED | STOPPED | DISABLED } ] AS { TCP | HTTP } (    <protocol_specific_items> ) FOR { SOAP | TSQL | SERVICE_BROKER | DATABASE_MIRRORING } (    <language_specific_items> ) 

where:


AFFINITY

Specifies the endpoint affinity.


STATE

The STATE argument is the same as described in the discussion for the CREATE ENDPOINT statement earlier in this chapter.

If the state is changed to DISABLED, the server needs to be restarted for the change to take effect.

8.5.2.1. Protocol-specific items

The syntax for HTTP protocol-specific items is:

 <AS 
HTTP_protocol_specific_arguments
> ::=
AS HTTP ( PATH = 'url', AUTHENTICATION = ( { BASIC | DIGEST | NTLM | KERBEROS | INTEGRATED } [ ,...n ] ), PORTS = ( { CLEAR | SSL } [ ,...n ] ) [ SITE = { '*' | '+' | 'webSite' } , ] [ , CLEAR_PORT = clearPort ] [ , SSL_PORT = SSLPort ] [ , AUTH_REALM = { 'realm' | NONE } ] [ , DEFAULT_LOGON_DOMAIN = { 'domain' | NONE } ] [ , RESTRICT_IP = { NONE | ALL } ] [ , COMPRESSION = { ENABLED | DISABLED } ] [ , ADD EXCEPT_IP = ( { <4-part-ip> | <4-part-ip>:<mask> } [ ,...n ] ) [ , DROP EXCEPT_IP = ( { <4-part-ip> | <4-part-ip>:<mask> } [ ,...n ] ) )

where:


ADD EXCEPT_IP

Adds IP addresses to the IP addresses in the EXCEPT_IP parameter for the endpoint.

The RESTRICT_IP parameter specified in the CREATE ENDPOINT statement controls whether requests from these IP addresses are allowed or denied access to the endpoint.


DROP EXCEPT_IP

Removes IP addresses from the IP addresses in the EXCEPT_IP parameter for the endpoint.

The RESTRICT_IP parameter specified in the CREATE ENDPOINT statement controls whether requests from these IP addresses are allowed or denied access to the endpoint.

The syntax for TCP protocol-specific items is:

 <AS TCP_protocol_specific_arguments> ::= AS TCP (   LISTENER_PORT = listenerPort   [ , LISTENER_IP = ALL | (<4-part-ip> | <ip_address_v6> ) ]   [ , RESTRICT_IP = ALL | NONE ]   [ , EXCEPT_IP = ( { <4-part-ip> | <4-part-ip>:<mask> } [ ,...n ] ) ] ) 

8.5.2.2. Language-specific items

The syntax for SOAP language-specific items is:

 <
FOR SOAP_language_specific_arguments
> ::=
( [ { ADD WEBMETHOD [ 'namespace' .] 'method_alias' ( NAME = 'database.owner.name' [ , SCHEMA = {NONE | STANDARD | DEFAULT } ] [ , FORMAT = { ALL_RESULTS | ROWSETS_ONLY } ] ) } [ ,...n ] ] [ { ALTER WEBMETHOD [ 'namespace' .] 'method_alias' ( NAME = 'database.owner.name' [ , SCHEMA = {NONE | STANDARD | DEFAULT} ] [ , FORMAT = { ALL_RESULTS | ROWSETS_ONLY } ] ) } [ ,...n] ] [ { DROP WEBMETHOD [ 'namespace' .] 'method_alias' } [ ,...n ] ] [ BATCHES = { ENABLED | DISABLED } ] [ , WSDL = { NONE | DEFAULT | 'sp_name' } ] [ , SESSIONS = { ENABLED | DISABLED } ] [ , SESSION_TIMEOUT = int ] [ , DATABASE = { 'database_name' | DEFAULT } [ , NAMESPACE = { 'namespace' | DEFAULT } ] [ , SCHEMA = { NONE | STANDARD } ] [ , CHARACTER_SET = { SQL | XML } ] )

where:


ADD WEBMETHOD

Adds a method to the endpoint.


ALTER WEBMETHOD

Changes the definition of an existing method on the endpoint.


DROP WEBMETHOD

Removes an existing method from the endpoint.

The syntax for Service Broker-specific items is:

 <
FOR SERVICE_BROKER_language_specific_arguments
> ::=
FOR SERVICE_BROKER ( [ AUTHENTICATION = ENABLED | REQUIRED* | NONE ] [ , MESSAGE_FORWARDING = ENABLED | DISABLED* ] [ , MESSAGE_FORWARD_SIZE = forwardSize )

The syntax for database mirroring-specific items is:

 <
FOR DATABASE_MIRRORING_language_specific_arguments
> ::=
FOR DATABASE_MIRRORING ( [ ENCRYPTION = ENABLED | DISABLED ] | ROLE = WITNESS | PARTNER | ALL )

8.5.3. Dropping an ENDPOINT

The DROP ENDPOINT statement removes an existing endpoint. Endpoints can be dropped by members of the sysadmin role, the owner of the endpoint, or users granted CONTROL permission on the endpoint.

The DROP ENDPOINT statement syntax is:

 DROP ENDPOINT endPointName 

where:


endPointName

The name of the endpoint to remove.

8.5.4. Endpoint Authentication

Each endpoint is configured for an authentication type and access permissions that allow users to connect to the endpoint.

A user must be an authenticated Windows usereither a trusted Windows user or a member account on the local computerto access an endpoint. Anonymous authentication on an endpoint is not supported.

The AUTHENTICATION clause in the CREATE ENDPOINT and ALTER ENDPOINT statements specifies how endpoint authentication is performed. The following authentication types are supported:


BASIC

An HTTP 1.1 authentication mechanism that uses an authentication header containing a Base64-encoded username and password separated by a colon (:). The credentials must map to a valid Windows account. Because Base64 encoding can easily be decoded, SQL Server requires that a Secure Sockets Layer (SSL) port be used for the HTTP connectionBASIC authentication is not allowed for endpoints where the PORTS value is CLEAR.


DIGEST

An HTTP 1.1 authentication mechanism consisting of a username and password hashed with the MD5 algorithm. The server has access to either the actual password or an MD5 hash of the password, allowing the client to prove the password without actually sending it to the server. The credentials must map to a valid Windows domain accountlocal users are not supported for digest authentication.


NTLM

The authentication mechanism supported by Windows 9x and Windows NT 4.0. NTLM is an encrypted challenge/response protocol that uses a domain name, username, and one-way password hash.

Although Microsoft Kerberos is the protocol of choice, NTLM is provided for backward compatibility.


KERBEROS

An Internet standard mutual-authentication mechanism between client and server designed for use over an open or unsecured network. Clients obtain an authentication ticket from a Kerberos Key Distribution Center (KDC) and present that ticket to servers when connections are established. Kerberos is supported in Windows 2000 or later by the Security Support Provider Interface (SSPI ).

To use Kerberos authentication, you must register a service principal name (SPN) with the SQL Server account that it is running on by using the SetSPN.exe tool. For more information, see Microsoft SQL Server 2005 Books Online.


INTEGRATED

The endpoint can respond to either Kerberos or NTLM authentication-type challenges. The server tries to authenticate the client using the authentication type that the client uses in the authentication request.

Native HTTP SOAP can user either Windows user and group accounts or SQL Server logins as security principals. You control this via the LOGIN_TYPE value in the SOAP language-specific arguments for the CREATE ENDPOINT or ALTER ENDPOINT statement.

If SQL logins are used, your application must use WS-Security headers to submit the SQL username and password login credentials to the server for SQL Server authentication .

WS-Security is an open-standard set of SOAP extensions used to build secure web services and to implement message content integrity and confidentiality.

For more information about WS-Security, get the latest draft from the Organization for the Advancement of Structured Information Standards (OASIS) web site at http://www.oasis-open.org.


To use SQL Server authentication, the following conditions must be met:

  • The SQL Server installation is mixed mode, supporting both Windows and SQL logins.

  • The endpoint is configured with LOGIN_TYPE = MIXED and PORTS = (SSL).

  • Endpoint permissions have been granted to the appropriate SQL Server logins.

A generic WS-Security header that can be used in a SOAP request header to transport SQL Server authentication credentials is:

 <SOAP-ENV:Header>   <wsse:Security     xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/       oasis-200401-wss-wssecurity-secext-1.0.xsd">     <wsse:UsernameToken>       <wsse:Username>sqlLoginUserName</wsse:Username>       <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/         oasis-200401-wss-username-token-profile-1.0#PasswordText">         sqlLoginPassword       </wsse:Password>     </wsse:UsernameToken>   </wsse:Security> </SOAP-ENV:Header> 

The <wsse:UserName> element contains the SQL Server user login name, and the <wsse:Password> element contains the password for the login.

8.5.5. Managing Permissions on Endpoints

Endpoint permissions specify whether a login can create, alter, connect, or transfer ownership of an endpoint. Requests against endpoints can be executed by members of the sysadmin role, the owner of the endpoint, or users granted CONNECT permission on the endpoint.

All T-SQL statements governing permissions must be executed on the master database. This section discusses the statements used for managing endpoint permissions.

The prototypes in the subsections use the following arguments:


server_principal

The principal to which the permission is granted, denied, or revoked. server_principal is one of the following:

  • A SQL Server login

  • A SQL Server login created from a Windows login

  • A SQL Server login mapped to a certificate

  • A SQL Server login mapped to an asymmetric key


endPointName

The name of the endpoint for which the permission is granted.

8.5.5.1. Create permission

A server-scoped permission controls whether a login can create an endpoint object. The syntax is:

 { GRANT | DENY | REVOKE } CREATE ENDPOINT TO server_principal 

8.5.5.2. Alter permission

You can assign permissions to a user to alter an endpoint in three different ways:

  • At the server scope. Granting this permission lets the login alter or drop any endpoint on the server:

     { GRANT | DENY | REVOKE } ALTER ANY ENDPOINT TO server_principal 

  • For a specific endpoint. Granting this permission lets the login alter or drop a specific endpoint on the server:

     { GRANT | DENY | REVOKE } ALTER ON ENDPOINT::endPointName TO server_principal 

8.5.5.3. Control permission

This permission lets the principal alter or drop a specific endpoint on the server and transfer its ownership:

 { GRANT | DENY | REVOKE } CONTROL ON ENDPOINT::endPointName TO server_principal 

8.5.5.4. Connect permission

This permission controls whether a login can execute requests against a specific endpoint:

 { GRANT | DENY | REVOKE } CONNECT ON ENDPOINT::endPointName TO server_principal 

8.5.5.5. Take Ownership permission

This permission controls whether a user specified in the AUTHORIZATION clause of a CREATE ENDPOINT or ALTER ENDPOINT statement can take ownership of an endpoint:

 { GRANT | DENY | REVOKE } TAKE OWNERSHIP ON ENDPOINT::endPointName TO     server_principal> 

8.5.6. Endpoint Metadata

You can query the catalog views described in Table 8-1 for information about all endpoints defined in the system.

Table 8-1. Catalog views for endpoint information

Catalog view

Description

sys.database_mirroring_endpoints

General endpoint information and detailed database mirroring-specific information for each database mirroring endpoint in the system

sys.endpoints

General information about each HTTP endpoint in the system, including the name, ID, principal ID, protocol, type, and state information

sys.endpoint_webmethods

Detailed information for each SOAP method defined on a SOAP-enabled HTTP endpoint

sys.http_endpoints

General endpoint information and HTTP-specific information, including site information and authentication type for endpoints in the system that use HTTP

sys.tcp_endpoints

General endpoint information and TCP-specific information for endpoints in the system that use TCP

sys.service_broker_endpoints

General endpoint information and Service Broker-specific information for each Service Broker endpoint in the system

sys.soap_endpoints

General endpoint information and SOAP-specific information for each endpoint in the system that carries a SOAP payload

sys.via_endpoints

General endpoint information and Virtual Interface Adapter (VIA)-specific information for each VIA endpoint in the system




Programming SQL Server 2005
Programming SQL Server 2005
ISBN: 0596004796
EAN: 2147483647
Year: 2007
Pages: 147
Authors: Bill Hamilton

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net