.png)
CREATE ENDPOINT (Transact-SQL)
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. |
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 | ( 4-part-ip ) | ( "ip_address_v6" ) ]
)
<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 }
)
TCP Protocol Option
The following arguments apply only to the TCP protocol option.
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.
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> ::=
- ENCRYPTION = { DISABLED | SUPPORTED | REQUIRED } [ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ]
-
Specifies whether encryption is used in the process. The default is 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.
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 material encrypted using RC4 or RC4_128 can be decrypted in any compatibility level.
- 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
The RC4 algorithm is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. 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.
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. |
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
Requires CREATE ENDPOINT permission, or membership in the sysadmin fixed server role. For more information, see GRANT Endpoint Permissions (Transact-SQL).
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
