CREATE ENDPOINT (Transact-SQL)

Artikkeli
12/03/2008

Creates endpoints and defines their properties, including the methods available to client applications. For related permissions information, see GRANT Endpoint Permissions (Transact-SQL).

The syntax for CREATE ENDPOINT can logically be broken into two parts:

The first part starts with AS and ends before the FOR clause.
In this part, you provide information specific to the transport protocol as either TCP or HTTP and set a listening port number for the endpoint, as well as the method of endpoint authentication and/or a list of IP addresses (if any) that you want to restrict from accessing the endpoint.
The second part starts with the FOR clause.
In this part, you define the payload that is supported on the endpoint. The payload can be one of several supported types: SOAP, Transact-SQL, service broker, database mirroring. In this part, you also include language-specific information. For example, in using HTTP SOAP, you identify the procedures you want to expose in the endpoint as Web methods. For each of Web methods, you identify a corresponding SQL Server stored procedure or user-defined function to which it maps. A client application can then send HTTP SOAP requests that call these methods in the endpoint. You can also provide additional SOAP configuration information, such as the following:
- Whether to allow ad hoc query requests
- Whether to return the XSD schema for the result set
- The database context in which the methods requested are executed
- The namespace for the endpoint
- How to handle characters in the result that are not valid in XML

Topic link icon Transact-SQL Syntax Conventions

Syntax

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>
        )

<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 } ]
  [ [ , ] COMPRESSION = { ENABLED | DISABLED } ]
  )

<AS TCP_protocol_specific_arguments> ::=
AS TCP (
  LISTENER_PORT = listenerPort
  [ [ , ] LISTENER_IP = ALL | ( 4-part-ip ) | ( "ip_address_v6" ) ]

)

<FOR SOAP_language_specific_arguments> ::=
FOR SOAP(
  [ { WEBMETHOD [ 'namespace' .] 'method_alias' 
    (   NAME = 'database.schema.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 } ]
  [ [ , ] HEADER_LIMIT = int ]

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

<FOR DATABASE_MIRRORING_language_specific_arguments> ::=
FOR DATABASE_MIRRORING (
   [ AUTHENTICATION = { 
            WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] 
      | CERTIFICATE certificate_name 
      | WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] CERTIFICATE certificate_name 
      | CERTIFICATE certificate_name WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] 
   [ [ [ , ] ] ENCRYPTION = { DISABLED | { { SUPPORTED | REQUIRED } 
       [ ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ] } 

    ] 
   [ , ] ROLE = { WITNESS | PARTNER | ALL }
)

Arguments

endPointName
Is the assigned name for the endpoint you are creating. Use when updating or deleting the endpoint.

AUTHORIZATION login
Specifies a valid SQL Server or Windows login that is assigned ownership of the newly created endpoint object. If AUTHORIZATION is not specified, by default, the caller becomes owner of the newly created object.

To assign ownership by specifying AUTHORIZATION, the caller must have IMPERSONATE permission on the specified login.

To reassign ownership, see ALTER ENDPOINT (Transact-SQL).

STATE = { STARTED | STOPPED | DISABLED }
Is the state of the endpoint when it is created. If the state is not specified when the endpoint is created, STOPPED is the default.
- STARTED
  Endpoint is started and is actively listening for connections.
- DISABLED
  Endpoint is disabled. In this state, the server does not listen to the endpoint port or respond to any attempted requests to use the endpoint.
- STOPPED
  Endpoint is stopped. In this state, the server listens to port requests but returns errors to clients.
To change the state, use ALTER ENDPOINT (Transact-SQL).

AS { HTTP | TCP }
Specifies the transport protocol to use.

FOR { SOAP | TSQL | SERVICE_BROKER | DATABASE_MIRRORING }
Specifies the payload type.

Currently, there are no TSQL <language_specific_arguments>.

HTTP Protocol Option

The following apply only to the HTTP protocol option.

PATH = 'url'
Specifies the URL path that identifies the location of the endpoint on the host computer specified in the SITE argument. PATH is a logical partitioning of the URL namespace that is used by the listener to route requests appropriately. PATH must be part of the URL that the client uses to send HTTP SOAP requests to an instance of SQL Server. For example, in the URL https://ServerName/Sql, ServerName is the host computer specified by SITE and /Sql is the URL specified by PATH.

AUTHENTICATION = ( { BASIC | DIGEST | NTLM | KERBEROS | INTEGRATED } [ ,...n ] )
Specifies the authentication type that used to authenticate users that log on to an instance of SQL Server. You can specify BASIC, DIGEST, NTLM, KERBEROS or INTEGRATED, or a combination of these values separated by commas (,). For more information, see Endpoint Authentication Types.

PORTS**=(** { CLEAR | SSL} [ ,... n ] )
Specifies one or more listening port types that are associated with the endpoint. CLEAR and SSL can be specified at the same time. If only CLEAR is specified, the incoming requests must use HTTP. If SSL is specified, the incoming requests must be Secure HTTP (https://) requests.

[ SITE = { ' * ' | ' + ' | 'webSite' } ]
Specifies the name of the host computer. If SITE is omitted, the asterisk is the default. If sp_reserve_http_namespace was executed, pass <hostpart> to the SITE keyword. For example, if sp_reserve_http_namespace N'http://MyServer:80/sql' was executed, specify SITE='MyServer' in the CREATE ENDPOINT statement.
- * (asterisk)
  Implies that a listening operation applies to all possible host names for the computer that are not otherwise explicitly reserved.
- + (plus sign)
  Implies that a listening operation applies to all possible host names for the computer.
- webSite
  Is the specific host name for the computer.

[ CLEAR_PORT **=**clearPort ]
Specifies the clear port number. If PORTS = (CLEAR), this clearPort specifies the clear port number. The default port number is 80.

[ SSL_PORT**=**SSLPort ]
Specifies the SSL port number. If PORTS = (SSL), SSLPort specifies the SSL port number. The default SSL port number is 443.

[ AUTH_REALM = { 'realm' | NONE } ]
If AUTHENTICATION = DIGEST, specifies the hint that returns to the client, which sent the SOAP request to the endpoint, as part of HTTP authentication challenge. The default is NONE.

[ DEFAULT_LOGON_DOMAIN = { 'domain' | NONE } ]
If AUTHENTICATION = BASIC, specifies, the default login domain. The default is NONE.

[ COMPRESSION = { ENABLED | DISABLED } ]
If set to ENABLED, SQL Server will honor requests where gzip encoding is accepted, and return compressed responses. That is, if a request comes in with an HTTP header specifying GZIP as a valid "accept-encoding", the server returns the response gzip-encoded. Default is DISABLED.

TCP Protocol Option

The following arguments apply only to the TCP protocol option.

LISTENER_PORT **=**listenerPort
Specifies the port number listened to for connections by the service broker TCP/IP protocol. By convention, 4022 is used but any number between 1024 and 32767 is valid.

LISTENER_IP = ALL | **(**4-part-ip ) | ( "ip_address_v6" )
Specifies the IP address that the endpoint will listen on. The default is ALL. This means that the listener will accept a connection on any valid IP address.

SOAP Arguments

The following arguments are specific to the SOAP option.

[ WEBMETHOD [ 'namespace' .] 'method_alias' ]
Specifies a method for which you can send HTTP SOAP requests to an endpoint. Each WEBMETHOD clause describes one method; however multiple methods can be exposed for the endpoint. If namespace is not specified, the namespace of the endpoint is assumed.

You can create an endpoint without any methods, and then use ALTER ENDPOINT to add methods to it.

Important

When you use WEBMETHOD to expose methods, you must ensure that there are no duplicate names when more than one SQL Server database is serviced by the same HTTP endpoint. To prevent this, consider adding your registered domain name URL as part of the namespace path.

NAME ='database.schema.name'
Specifies the name of a stored procedure or user-defined function that corresponds to the SOAP method specified in WEBMETHOD. database**.schema.name must be a three-part name that complies with the rules for identifiers. If database.schema.**name does not exist, a warning is returned but the DDL operation succeeds.

[ SCHEMA = { NONE | STANDARD | DEFAULT } ], (This option is for the WEBMETHOD clause.)
Determines whether inline XSD schema will be returned for the current Web method in SOAP responses.
- NONE
  XSD schema is not returned for SELECT statement results sent through SOAP.
- STANDARD
  XSD schema is returned for SELECT statement results sent through SOAP.
- DEFAULT
  Defaults to the endpoint SCHEMA option setting.
If a schema is not specified or this option is set to DEFAULT, the SCHEMA option specified for the endpoint determines whether the SCHEMA for the method result is returned.

To get a schema for the result of a SELECT query that uses the FOR XML option, you must specify the XMLSCHEMA option in the query, regardless of the setting of the SCHEMA option here.

[ FORMAT = { ALL_RESULTS | ROWSETS_ONLY | NONE } ]
Specifies whether a row count, error messages and warnings are returned with the result set. The default is ALL_RESULTS.
- ALL_RESULTS
  Returns a result set, a row count and error messages and warnings in the SOAP response.
- ROWSETS_ONLY
  Returns only the result sets.
  
  Use this option with client applications that use the Visual Studio 2005 Web service proxy class generator, if you want the results returned as a single dataset (System.Data.Dataset object) and not as an object array.
- NONE
  Suppresses the return of SOAP-specific markup in the server response.
  
  This option can be used as a mechanism to support applications that have a stored procedure in which the response will be returned as is, in raw mode, by the server. When this option is in effect, the application is responsible for returning well-formed XML. This feature can be used to control the response for a number of reasons, For example, it could be used to create a stored procedure that would return a WS-Policy. The FORMAT=NONE option has the following conditions for use:
  - The method must be implemented by using a stored procedure without output parameters. User-defined functions are not allowed with this response format.
    Either the query must be a single-statement FOR XML query (Multiple FOR XML statements are not allowed with this response format.); or the output must consist of a single column that has the name XML_F52E2B61-18A1-11d1-B105-00805F49916B and of the type nvarchar.

BATCHES = { ENABLED | DISABLED }
Specifies whether ad hoc SQL requests are supported on the endpoint. The default is DISABLED.

ENABLED allows SOAP requests for ad hoc queries that use the sqlbatch method to be sent to this endpoint.

If batches are enabled, ad hoc SQL requests can be executed on the endpoint by calling the sql:sqlbatch method. This method also exposes parameterized query functionality; therefore, it can take an optional list of SqlParameter elements that will describe metadata and values for the parameters of the specified query.

Within the query, parameter names starting with the at sign (@) can be embedded. Matching parameter names must be supplied; otherwise, an error is returned for the request.

LOGIN_TYPE = { MIXED | WINDOWS }
Specifies the SQL Server authentication mode for the endpoint. If LOGIN_TYPE is not specified, the default is WINDOWS.

LOGIN_TYPE can only be used to further restrict the authentication mode for endpoints that are based upon the server global authentication mode that was selected when the instance of SQL Server was installed.
- MIXED
  Allows either SQL Server authentication or Windows authentication to be used to authenticate endpoint users.
  
  If MIXED is specified and the server is installed in Windows authentication mode, an error is returned.
  
  When set to MIXED, SQL Server-based authentication is supported on the endpoint, and the endpoint must be configured to use a Secure Sockets Layer (SSL) port.
- WINDOWS
  Allows only Windows authentication to be used to authenticate endpoint users.

WSDL = { NONE | DEFAULT | 'sp_name' }
Indicates whether WSDL document generation is supported for this endpoint. If NONE, no WSDL response is generated or returned for WSDL queries submitted to the endpoint. If DEFAULT, adefault WSDL response is generated and returned for WSDL queries submitted to the endpoint. In exceptional cases, where you are implementing custom WSDL support for the endpoint, you can also specify stored procedure by name that will return a modified WSDL document.

[ SESSIONS = { ENABLED | DISABLED } ]
Specifies whether the instance of SQL Server allows sessions support. If set to ENABLED, SQL Server allows sessions support, whereby multiple SOAP request/response message pairs can be identified as part of a single SOAP session. The default is DISABLED.

SESSION_TIMEOUT **=**timeoutInterval
Specifies time in seconds (as an integer) that is available before a SOAP session expires at the server when no further requests are received. The default is 60 seconds. This overrides the time-out value specified in the SOAP session header.

The time-out time that is initialized when the server finishes sending a SOAP response message to the client. If the time-out elapses before another SOAP request with the same session ID specified in its header, the session is terminated at the server.

[ DATABASE = { 'database_name' | DEFAULT } ]
Specifies the database in the context of which the requested operation is executed. If database_name is not specified or if DEFAULT is specified, the default database for the login is used.

[ NAMESPACE = { 'namespace' | DEFAULT } ]
Specifies the namespace for the endpoint. If namespace is not specified or if DEFAULT is specified, the assumed namespace is http://tempuri.org. You can overwrite the default namespace when you specify a specific method by using WEBMETHOD **'namespace'**namespace.

[ SCHEMA = { NONE | STANDARD } ],
Specifies whether an XSD schema is returned by the endpoint when SOAP results are sent.
- NONE
  Omits inline schema from SOAP responses.
- STANDARD
  Includes inline schema in endpoint responses.
Note

For loading SOAP results into System.Data.DataSet objects when you are using Visual Studio 2005, inline schemas are required; therefore, STANDARD must be specified.

[CHARACTER_SET = { SQL | XML } ]
Defines the behavior when the result of an operation includes characters that are not valid in XML. The default is XML.
- SQL
  Encodes the characters that are not valid as character references, and then returns them in the result. In this case, an XML parser may reject the returned XML as not valid.
- XML
  Encodes characters according to the XML specification. Any characters that are not allowed in the XML character set will cause SQL Server to send an invalid XML error back to the client.

HEADER_LIMIT
Specifies the maximum size, in bytes, of the header section in the SOAP envelope. If the SOAP headers don't fit in this size, the server generates a parsing error. If not specified, a default value of 8 KB (8192 bytes) is assumed.

SERVICE_BROKER and DATABASE_MIRRORING Options

The following AUTHENTICATION and ENCRYPTION arguments are common to the SERVICE_BROKER and DATABASE_MIRRORING options.

Note

For options that are specific to SERVICE_BROKER, see "SERVICE_BROKER Options," later in this section. For options that are specific to DATABASE_MIRRORING, see "DATABASE_MIRRORING Options," later in this section.

AUTHENTICATION = <authentication_options>
Specifies the TCP/IP authentication requirements for connections for this endpoint. The default is WINDOWS.

The supported authentication methods include NTLM and or Kerberos or both. For more information about these methods, see Endpoint Authentication Types.

Important

All mirroring connections on a server instance use a single database mirroring endpoint. Any attempt to create an additional database mirroring endpoint will fail.

<authentication_options> ::=
- WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
  Specifies that the endpoint is to connect using Windows Authentication protocol to authenticate the endpoints. This is the default.
  
  If you specify an authorization method (NTLM or KERBEROS), that method is always used as the authentication protocol. The default value, NEGOTIATE, causes the endpoint to use the Windows negotiation protocol to choose either NTLM or Kerberos.
- CERTIFICATE certificate_name
  Specifies that the endpoint is to authenticate the connection using the certificate specified by certificate_name to establish identity for authorization. The far endpoint must have a certificate with the public key matching the private key of the specified certificate.
- WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] CERTIFICATE certificate_name
  Specifies that endpoint is to try to connect by using Windows Authentication and, if that attempt fails, to then try using the specified certificate.
- CERTIFICATE certificate_name WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
  Specifies that endpoint is to try to connect by using the specified certificate and, if that attempt fails, to then try using Windows Authentication.

ENCRYPTION = { DISABLED | SUPPORTED | REQUIRED } [ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ]
Specifies whether encryption is used in the process. The default is REQUIRED.
- DISABLED
  Specifies that data sent over a connection is not encrypted.
- SUPPORTED
  Specifies that the data is encrypted only if the opposite endpoint specifies either SUPPORTED or REQUIRED.
- REQUIRED
  Specifies that connections to this endpoint must use encryption. Therefore, to connect to this endpoint, another endpoint must have ENCRYPTION set to either SUPPORTED or REQUIRED.
Optionally, you can use the ALGORITHM argument to specify the form of encryption used by the endpoint, as follows:
- RC4
  Specifies that the endpoint must use the RC4 algorithm. This is the default.
- AES
  Specifies that the endpoint must use the AES algorithm.
- AES RC4
  Specifies that the two endpoints will negotiate for an encryption algorithm with this endpoint giving preference to the AES algorithm.
- RC4 AES
  Specifies that the two endpoints will negotiate for an encryption algorithm with this endpoint giving preference to the RC4 algorithm.
Note

RC4 is a relatively weak algorithm, and AES is a relatively strong algorithm. But AES is considerably slower than RC4. If security is a higher priority for you than speed, we recommend you use AES.

If both endpoints specify both algorithms but in different orders, the endpoint accepting the connection wins.

SERVICE_BROKER Options

The following arguments are specific to the SERVICE_BROKER option.

MESSAGE_FORWARDING = { ENABLED | DISABLED }
Determines whether messages received by this endpoint that are for services located elsewhere will be forwarded.
- ENABLED
  Forwards messages if a forwarding address is available.
- DISABLED
  Discards messages for services located elsewhere. This is the default.

MESSAGE_FORWARDING_SIZE **=**forward_size
Specifies the maximum amount of storage in megabytes to allocate for the endpoint to use when storing messages that are to be forwarded.

DATABASE_MIRRORING Options

The following argument is specific to the DATABASE_MIRRORING option.

ROLE = { WITNESS | PARTNER | ALL }
Specifies the database mirroring role or roles that the endpoint supports.
- WITNESS
  Enables the endpoint to perform in the role of a witness in the mirroring process.
  
  Note
  
  For SQL Server 2005 Express Edition, WITNESS is the only option available.
- PARTNER
  Enables the endpoint to perform in the role of a partner in the mirroring process.
- ALL
  Enables the endpoint to perform in the role of both a witness and a partner in the mirroring process.
For more information about these roles, see Overview of Database Mirroring.

Note

For DATABASE_MIRRORING, only TCP can be used as the transport protocol, not HTTP. There is no default port for DATABASE_MIRRORING.

Remarks

ENDPOINT DDL statements cannot be executed inside a user transaction. ENDPOINT DDL statements do not fail even if an active snapshot isolation level transaction is using the endpoint being altered.

Requests can be executed against an ENDPOINT by the following:

Members of sysadmin fixed server role
The owner of the endpoint
Users or groups that have been granted CONNECT permission on the endpoint

Permissions

Requires CREATE ENDPOINT permission, or membership in the sysadmin fixed server role. For more information, see GRANT Endpoint Permissions (Transact-SQL).

Examples

A. Creating an endpoint to use for SOAP requests

The following example creates an endpoint called sql_endpoint, with two methods: GetSqlInfo and DayAsNumber. These are the methods for which a client can send SOAP requests to the endpoint.

For each method, the default for the FORMAT option is ALL_RESULTS. Therefore, the SOAP response for method request will include error messages, warnings, and rowcount information.

Note the following SOAP-specific settings:

The SCHEMA option is set to STANDARD for the endpoint. Therefore, by default, inline schemas are returned in SOAP responses.
The WSDL option is set to DEFAULT. Therefore, if a client requests a WSDL response from this endpoint (https://Server/sql?wsdl), the server generates and returns a WSDL response to the client.

DROP ENDPOINT sql_endpoint;
GO

CREATE ENDPOINT sql_endpoint 
STATE = STARTED
AS HTTP(
   PATH = '/sql', 
   AUTHENTICATION = (INTEGRATED ), 
   PORTS = ( CLEAR ), 
   SITE = 'SERVER'
   )
FOR SOAP (
   WEBMETHOD 'GetSqlInfo' 
            (name='master.dbo.xp_msver', 
             SCHEMA=STANDARD ),
   WEBMETHOD 'DayAsNumber' 
            (name='master.sys.fn_MSdayasnumber'),
   WSDL = DEFAULT,
   SCHEMA = STANDARD,
   DATABASE = 'master',
   NAMESPACE = 'http://tempUri.org/'
   ); 
GO

You can query the catalog view to find the information about the endpoint that you created, as shown in the following examples. You can also do a join of these catalog views to get the data you want.

Query sys.endpoints to retrieve all of endpoint information in the system. This includes, name, ID of the endpoint, ID of the server principal who owns the endpoint, and other properties of the endpoint.

SELECT *
FROM sys.endpoints;

You can query sys.http_endpoints to retrieve detailed endpoint information related to HTTP, such as SITE, URL, AUTHENTICATION mechanism and other HTTP specific information.

SELECT *
FROM sys.http_endpoints;

To retrieve SOAP-specific information about the endpoint, query sys.soap_endpoints.

SELECT * 
FROM sys.soap_endpoints;

To retrieve the SOAP methods that are defined on the endpoint, query sys.endpoint_webmethods.

SELECT *
FROM sys.endpoint_webmethods;

B. Creating a database mirroring endpoint

The following example creates a database mirroring endpoint. The endpoint uses port number 7022, although any available port number would work. The endpoint is configured to use Windows Authentication using only Kerberos. The ENCRYPTION option is configured to the nondefault value of SUPPORTED to support encrypted or unencrypted data. The endpoint is being configured to support both the partner and witness roles.

CREATE ENDPOINT endpoint_mirroring
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7022 )
    FOR DATABASE_MIRRORING (
       AUTHENTICATION = WINDOWS KERBEROS,
       ENCRYPTION = SUPPORTED,
       ROLE=ALL);
GO

Jaa