Native XML Web Services provides two types
of Web services access to data: exposing specific stored procedures or
user-defined functions (UDFs) as Web methods, and batch mode access.
1. Stored Procedures and User-Defined Functions
Native XML Web Services allows virtually any
stored procedure or UDF to be exposed as a Web method. There are a few
minor limitations, such as restrictions
on stored procedure parameter names and lack of support for UDFs that
return tables. However, in general you can choose to expose any existing
stored procedure as a Web service, allowing it to do double duty as a
normal stored procedure in your applications and as a Web service.
2. SQL Batch
In addition to exposing individual stored
procedures and UDFs, Native XML Web Services also supports exposing
T-SQL batch statements. Because of the wide-ranging power of this level
of access, SQL batch is disabled by default.
3. Reserving URLs with Http.sys
It is not strictly necessary to register a URL with Http.sys in order to expose an endpoint. The CREATE ENDPOINT statement implicitly registers the URL defined by its PORTS, SITE, and PATH
arguments. However, this registration takes place only when the
endpoint is running, so it is possible for another application to
register the same URL before your service starts up, causing a conflict. Therefore, it is usually a good idea to reserve the specific URL for your endpoints.
To register a URL to be handled by SQL Server, you call the sp_reserve_http_namespace stored procedure:
EXEC sp_reserve_http_namespace N'http://localhost:80/sql'
or
EXEC sp_reserve_http_namespace N'https://localhost:443/sql'
The registration of namespaces with Http.sys is hierarchical, which means any sub URLs, such as http://localhost:80/sql/AdventureSales, are also handled by SQL Server.
Note
You must be logged on to
SQL Server with an account that has Windows Administrator privileges on
the computer where the instance of SQL Server 2005 is installed in order
to reserve an HTTP namespace with the kernel-mode HTTP driver
(Http.sys). |
4. Creating and Managing Endpoints
For obvious security reasons, Native XML Web
Services does not expose any Web services by default—you must explicitly
expose them by creating endpoints. Only system administrators can
create endpoints or grant rights to other users to create them. However,
if you have the correct permissions, creating endpoints is easy—you
simply execute the CREATE ENDPOINT statement.
CREATE ENDPOINT
The CREATE ENDPOINT
statement has a lot of options, but here we’ll simplify it and look
only at the most commonly used options for creating Web service
endpoints.
CREATE ENDPOINT endPointName
STATE = { STARTED | STOPPED | DISABLED }
AS HTTP (
PORTS = ( { CLEAR | SSL} [ ,... n ] ),
SITE = {'*' | '+' | 'webSite' },
PATH = 'url',
AUTHENTICATION =({ BASIC | DIGEST | INTEGRATED | NTLM | KERBEROS } [ ,...n ] )
)
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 },
DATABASE = { 'database_name' | DEFAULT },
NAMESPACE = { 'namespace' | DEFAULT },
SCHEMA = { NONE | STANDARD }
)
STATE
The STATE argument specifies the state of the endpoint after it is created. By default, an endpoint is created in the STOPPED state and you must then use the ALTER ENDPOINT statement to activate it. If you want the endpoint to be activated immediately upon creation, you specify STARTED for this argument. When an endpoint is in the STOPPED
state, the server listens for and handles requests to the port but
returns an error to clients indicating that the port is not active. When
an endpoint is in the DISABLED state, the server does not listen on the port and will not respond to any client requests.
PORTS, SITE, and PATH
You specify the full URL address for the endpoint in three parts: [protocol]://[host name] + [path], defined by the PORTS, SITE, and PATH arguments, respectively. The PORTS argument determines whether the URL begins with http:// (for CLEAR) or https:// (for SSL). Note that it is possible to listen on both ports. The SITE argument defines the host name used. The PATH
argument defines the rest of the URL after the server name. For
example, you would use the following options to define the URL as http://localhost/sql/AdventureSales.
PORTS = ( CLEAR ),
SITE = 'localhost'
PATH = '/sql/AdventureSales'
The following options result in the URL https://myserver/NXWSEndpoints/MyEndpoint.
PORTS = ( SSL ),
SITE = 'MyServer'
PATH = '/NXWSEndpoints/MyEndpoint'
Note
Although the use of sql as the base of the PATH
argument seems to be common usage, this is by no means required. You
can put any value (that results in a valid URL string) in the PATH argument to define the URL. |
AUTHENTICATION
The AUTHENTICATION
argument defines the types of authentication supported by the endpoint.
These options should be familiar to you because they are the common
options available under IIS for authenticating Web sites and Web
services. However, conspicuously missing from the list is an option for
anonymous access. This is by design; the caller must be authenticated as
a Windows account or an SQL Server account to invoke a Web service on
an endpoint.
You should pay attention to a few details
regarding the authentication options. Because Basic authentication
passes the username and password in clear text, you are required to
choose the SSL value for PORTS when you specify Basic authentication. If the INTEGRATED
value is specified, the server will use Kerberos or NTLM
authentication, depending on how the client initiates authentication.
Before you can use Kerberos authentication, the SQL Server must
associate a Service Principal Name (SPN) with the account it will be
running on. You can do this using the Windows SetSPN.exe support tool in
Windows 2003.
WEBMETHOD
You must enter a WEBMETHOD clause for each stored procedure or UDF that you want to expose. The method_alias
argument allows you to assign the public name for the Web method, which
can differ from the actual name of the stored procedure or function it
maps to. The optional namespace argument allows you to override, for this particular Web method, the namespace defined at the endpoint level. You set NAME to the actual database name for the stored procedure or function, fully qualified as [database].[owner].[name].
The SCHEMA argument controls whether an XML Schema Definition (XSD) is included inline with the SOAP response. There is also a SCHEMA option at the endpoint level, so at the WEBMETHOD level we can specify DEFAULT to use the value set at the endpoint level or override it for a particular Web method. If this option is set to STANDARD, the XSD schema is included in the response; if it is set to NONE, it is not.
Note
If you want to receive the result set as a DataSet on the client side, you must set SCHEMA to STANDARD because the schema is required by the client proxy to create the DataSet. |
The final option for the WEBMETHOD clause is FORMAT, which is used to control whether row count, error messages, and warnings are returned with the result set. The default, ALL_RESULTS,
causes all of these results to be passed back to the client. In
addition to controlling what is passed back from the server, the FORMAT argument also affects the interface of the generated proxy when you add a Web reference in Visual Studio .NET. The ALL_RESULTS value causes the proxy to return an object
array to the client application. Although it is useful to have access
to the row count, error messages, and warnings, receiving an object array requires the client code to do a little more work to get at the desired DataSet. Setting FORMAT to ROWSETS_ONLY changes the Web method signature on the proxy to return a single DataSet, which is simpler for the client code to consume.
BATCHES
The BATCHES option, if set to ENABLED, creates an extra Web method called sqlbatch
that allows you to pass a string containing T-SQL commands. Because
this gives you broader access to data—for example, enabling you to
execute ad hoc SQL queries—the default is DISABLED. Keep in mind that even with BATCHES set to ENABLED,
the authenticated user must have rights to the underlying database
objects. So if you protect access to all tables in a database and only
give rights to stored procedures, turning on BATCHES
will allow the user only to invoke multiple stored procedures in a
single call—not to execute SQL statements directly against the tables.
DATABASE
The DATABASE argument defines the database the endpoint will be run against. If this argument is not set, it defaults to the DEFAULT database for the login.
NAMESPACE
You can define the default SOAP namespace for all of the exposed Web methods in an endpoint by including the NAMESPACE argument. Individual Web method entries can override this default by specifying the namespace in the WEBMETHOD clause.
SCHEMA
The SCHEMA argument at the endpoint level controls the default value for the SCHEMA argument in contained WEBMETHOD clauses. It defines whether an XSD schema is included inline with the SOAP response. If this option is set to STANDARD, the XSD schema is included in the response; if it is set to NONE, it is not.
Note
The SCHEMA setting is not related to the XMLSCHEMA option in a SELECT query that uses the FOR XML option. If you want the XML returned by a stored procedure to contain a schema, you must specify the XMLSCHEMA option in the T-SQL statement, regardless of the SCHEMA setting here. |
WSDL
The WSDL argument allows you to control how the endpoint generates WSDL to support client applications. If you set this value to NONE, there will be no support for WSDL. If you need to support a custom WSDL format, you can set the WSDL
argument to the name of a stored procedure that generates the WSDL and
returns it. Due to the complexity of WSDL, implementing the code to
return custom WSDL is not a trivial task. You will rarely need to
generate custom WSDL, but it’s nice to know the option is there if you
need it.
The default value, and most common choice, for the WSDL argument is DEFAULT,
which instructs SQL Server to generate WSDL for the endpoint. The WSDL
generation actually supports two versions of WSDL for the endpoint. The
first is called default WSDL and is returned when you put ?wsdl at the end of the URL (for example, http://MyServer/sql/MyEndpoint?wsdl).
This default version of WSDL provides mappings from SQL Server data
types to corresponding data types in the .NET Framework 2.0. For
example, a number of types in the System.Data.SqlTypes namespace are better suited than the normal .NET primitive types to holding data from SQL Server.
The data type mappings provided in the default
WSDL format are useful for client applications built with Visual Studio
2005, but clients written with other technologies might not be able to
parse this form of the WSDL. For example, clients written with the SOAP
Toolkit cannot understand all of the XSD types in the default WSDL. To
support these clients, Native XML Web Services provides the simple WSDL
format, which you can obtain by adding the word simple to the end of the Web service URL, as shown here: http://MyServer/sql/MyEndpoint?simple.
The simple WSDL format uses only simple XSD types, so it has better
backward compatibility with older Web services clients and with
non-Microsoft Web services client technologies.
Let’s look at a simple example:
CREATE ENDPOINT my_sql_endpoint
STATE = STOPPED
AS HTTP(
PATH = '/sql/HelloWorld',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = 'localhost'
)
FOR SOAP (
BATCHES = ENABLED,
SCHEMA = STANDARD,
WSDL = DEFAULT,
DATABASE = 'AdventureWorks',
NAMESPACE = 'http://HelloWorld/'
)
Here we create the endpoint named my_sql_endpoint. It runs in clear HTTP, and the URL is http://localhost/sql/HelloWorld. It starts out in the STOPPED state. The only Web method exposed is the sqlbatch Web method because BATCHES is enabled. It runs against the AdventureWorks database.
ALTER ENDPOINT
The syntax for ALTER ENDPOINT is pretty much the same as for CREATE ENDPOINT, except you only need to specify the parts that should be changed. You can use ALTER ENDPOINT
to add, modify, or drop an existing Web method in the endpoint and to
change the properties of the endpoint. For example, you might want to
add a new Web method to the endpoint:
ALTER ENDPOINT my_sql_endpoint
FOR SOAP ( ADD WEBMETHOD 'SayHello' (name='AdventureWorks.dbo.SayHello') )
If you created the endpoint with STATE set to STOPPED, you can start the endpoint like this:
ALTER ENDPOINT my_sql_endpoint
STATE = STARTED
Later, you might decide to remove that Web method from the endpoint:
ALTER ENDPOINT my_sql_endpoint
FOR SOAP ( DROP WEBMETHOD 'SayHello')
DROP ENDPOINT
DROP ENDPOINT is used to remove an endpoint definition. The syntax is simple, as you would expect:
DROP ENDPOINT my_sql_endpoint
