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 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
 Note |
|---|
Native XML Web Services (SOAP/HTTP endpoints) is deprecated. For more information, see Native XML Web Services: Deprecated in SQL Server 2008. |
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 }
)
HTTP Protocol Option
The following apply only to the HTTP protocol option.
TCP Protocol Option
The following arguments apply only to the TCP protocol option.
SOAP Arguments
The following arguments are specific to the SOAP option.
| Note |
|---|
Native XML Web Services (SOAP/HTTP endpoints) is deprecated. For more information, see Native XML Web Services: Deprecated in SQL Server 2008. |
- [ 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.
ImportantWhen 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.
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.
- 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.
- 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, a default 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.
NoteFor 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.
- 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.
ImportantAll 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:
NoteThe 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.
NoteFor 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 Overview.
| Note |
|---|
For DATABASE_MIRRORING, only TCP can be used as the transport protocol, not HTTP. 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).
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 (http://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
C. Specifying a port
When creating a SOAP endpoint on a Windows Server beginning with Windows Server 2003, if the path (URL) and port combination is in use by IIS, CREATE ENDPOINT will fail.
On Windows XP, you might need to stop IIS for the SOAP endpoints to work.
CREATE ENDPOINT sql_endpoint STATE = STARTED AS HTTP ( PATH = '/sql', AUTHENTICATION = (INTEGRATED ), PORTS = ( CLEAR ), SITE = 'SERVER', CLEAR_PORT = 2000 ) 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/' );
