CREATE ENDPOINT (Transact-SQL)

Artikkeli
12/30/2022

Applies to: SQL Server

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 (TCP) 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: Transact-SQL, service broker, database mirroring. In this part, you also include language-specific information.

Note

Native XML Web Services (SOAP/HTTP endpoints) was removed in SQL Server 2012 (11.x).

Transact-SQL syntax conventions

Syntax

CREATE ENDPOINT endPointName [ AUTHORIZATION login ]  
[ STATE = { STARTED | STOPPED | DISABLED } ]  
AS { TCP } (  
   <protocol_specific_arguments>  
        )  
FOR { TSQL | SERVICE_BROKER | DATABASE_MIRRORING } (  
   <language_specific_arguments>  
        )  
  
<AS TCP_protocol_specific_arguments> ::=  
AS TCP (  
  LISTENER_PORT = listenerPort  
  [ [ , ] LISTENER_IP = ALL | ( xx.xx.xx.xx IPv4 address ) | ( '__:__1' IPv6 address ) ]  
  
)  
  
<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 { AES | RC4 | 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 { AES | RC4 | 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.

The AUTHORIZATION option is not available in ALTER ENDPOINT. Ownership can only be assigned when the endpoint is created. To reassign ownership, see DROP ENDPOINT (Transact-SQL) and then return to this article to recreate the endpoint.

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 listens to port requests but returns errors to clients.

STOPPED
Endpoint is stopped. In this state, the server does not listen to the endpoint port or respond to any attempted requests to use the endpoint.

To change the state, use ALTER ENDPOINT (Transact-SQL).

AS { TCP }
Specifies the transport protocol to use.

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

Currently, there are no Transact-SQL language-specific arguments to pass in the <language_specific_arguments> parameter.

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, 5022 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.

If you configure database mirroring with an IP address instead of a fully-qualified domain name (ALTER DATABASE SET PARTNER = partner_IP_address or ALTER DATABASE SET WITNESS = witness_IP_address), you have to specify LISTENER_IP =IP_address instead of LISTENER_IP=ALL when you create mirroring endpoints.

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 value is WINDOWS.

The supported authentication methods include NTLM and or Kerberos or both.

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 { AES | RC4 | 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:

AES
Specifies that the endpoint must use the AES algorithm. This is the default in SQL Server 2016 (13.x) and later.

RC4
Specifies that the endpoint must use the RC4 algorithm. This is the default through SQL Server 2014 (12.x).

Note

The RC4 algorithm is only supported for backward compatibility. New material can only be encrypted using RC4 or RC4_128 when the database is in compatibility level 90 or 100. (Not recommended.) Use a newer algorithm such as one of the AES algorithms instead. In SQL Server 2012 (11.x) and later versions, material encrypted using RC4 or RC4_128 can be decrypted in any compatibility level.

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

The RC4 algorithm is deprecated. This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that 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_FORWARD_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 Database Mirroring (SQL Server).

Note

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

Example

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

Create a new endpoint pointing to a specific IPv4 address and port

CREATE ENDPOINT ipv4_endpoint_special
STATE = STARTED
AS TCP (
    LISTENER_PORT = 55555, LISTENER_IP = (10.0.75.1)
)
FOR TSQL ();

GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO public; -- Keep existing public permission on default endpoint for demo purpose
GRANT CONNECT ON ENDPOINT::ipv4_endpoint_special
TO login_name;

Create a new endpoint pointing to a specific IPv6 address and port

CREATE ENDPOINT ipv6_endpoint_special STATE = STARTED AS TCP (
    LISTENER_PORT = 55555,
    LISTENER_IP = ('::1')
)
FOR TSQL();

GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO PUBLIC;
GRANT CONNECT ON ENDPOINT::ipv6_endpoint_special TO PUBLIC;

Jaa

CREATE ENDPOINT (Transact-SQL)

Syntax

Arguments

Remarks

Permissions

Example

Creating a database mirroring endpoint

Create a new endpoint pointing to a specific IPv4 address and port

Create a new endpoint pointing to a specific IPv6 address and port

See also

Palaute

Lisäresursseja