SQL SERVER AS A PLATFORM FOR WEB SERVICES

SQL SERVER AS A PLATFORM FOR WEB SERVICES general, authentication is providing information, be it a user ID and password or a token containing a Kerberos ticket, to identify yourself to the application and prove that you are who you claim to be. Once you have been authenticated, SQL Server knows who you are, and your SQL Server roles and permissions authorize you to access various resources. The parameters AUTHENTICATION, AUTH_REALM, and DEFAULT_LOGIN_ DOMAIN determine what mechanism a user uses to identity herself to SQL Server. There are four AUTHENTICATION choices, analogous to the choices in IIS. ANON This allows anonymous access to the endpoint. The user does not have to identify herself to SQL Server at all. Anonymous access will not be permitted on CLEAR ports in other words, unless SSL is also used. When a user contacts an endpoint using anonymous access, she actually connects to SQLServer through SQLServer s Windows integrated security option using the Windows guest account on the machine. BASIC This choice uses HTTP basic authentication as defined by RFC 2617. Basic authentication requires a user ID and password, which will be transmitted over the network, and therefore is not permitted on CLEAR ports. When basic authentication is used, a user can specify either SQL Server credentials or Windows credentials (user ID and password), and if these credentials have logon access (that is, a record in syslogins), these will be used to log on to SQL Server. DIGEST Using digest authentication consists of hashing the user name and password, using a one-way hashing algorithm and sending the hash output to the server. It is defined in RFC 2617. In Windows operating systems, this requires that the machine be a Windows Active Directory domain controller and is not used frequently. In digest authentication, the user logs in to SQL Server using Windows security (a native SQL Server login is not possible). INTEGRATED Integrated security in Windows

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

SECURITY CHOICES AND XML WEB SERVICES SQLXML 3.0

SECURITY CHOICES AND XML WEB SERVICES SQLXML 3.0 Functionality In SQLXML 3.0, the equivalent functionality would be defined by using the IIS Virtual Directory Management for SQL Server GUI tool and adding a new virtual directory. Because we are using native HTTP support rather than an ISAPI application, when we use CREATE ENDPOINT in SQL Server 2005, we are not adding a virtual directory to IIS. Security Choices and XML Web Services The same reasons that HTTP is conveniently usable as a transport also make it a security risk. Firewall administrators routinely leave port 80 open for HTTP traffic. Web spiders and other search engines scour arbitrary servers looking for content to index (or break into). Tools exist that make it easy to execute a denial of service attack on an arbitrary Web server. A server listens to TCP port 80 at its own risk. It is not the case that the HTTP protocol itself is less secure than, say, the TDS protocol; it is just more of a known quantity. The fact that the headers and verbs are text based (a feature shared by SOAP and XML) makes any message readable by default. Arbitrary TDS messages may be run through a binary decoding filter, but when you are using a text-based protocol, the filter is your eyes. Making security explicit and denying access the default behavior is crucial when using HTTP to talk directly to your corporate database. As we mentioned before, SQL Server s HTTP support is turned off by default. Enabling HTTP is required. Endpoints are not started by default, and no endpoints are predefined. This is a big improvement over software that comes with Web servers preinstalled, autostarted, with security turned off. Because SQL Server endpoints are their own Web servers, you use traditional HTTP security protocols for authentication. In addition to authentication, SQL Server s HTTP endpoints allow IP address filtering by using the RESTRICT_IP and EXCEPT_IP parameters on CREATE ENDPOINT. This is similar to the equivalent functionality found in most Web servers. You can permit access to SQL Server endpoints using either SQL Server authentication or Windows integrated security logins on SQL Server. A variety of authentication protocols are supported, including WS-Security (the Web Service standard security authentication protocol), which will be added before SQL Server 2005 ships. Once authenticated, access to SQL Server resources (authorization) is handled by SQL Server permissions. In

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

SQL SERVER AS A PLATFORM FOR WEB SERVICES

SQL SERVER AS A PLATFORM FOR WEB SERVICES If you specify + (plus sign), it means that you want to listen on all possible host names for the machine. * is the default. PATH The path on the Web server that users connect to. You must specify this parameter, and there are special security requirements to be able to use any path that is not a subpath of /sql. PORTS, CLEAR_PORT, and SSL_PORT These define the TCP ports to use and whether you can use unencrypted (CLEAR_PORT) or encrypted (SSL_PORT) communication or both. By default, unencrypted HTTP uses port 80, and SSL encryption uses port 443. Note that in the beta release of SQL Server 2005, if you want to use SSL, you must have an IIS server running on the same machine with a server certificate installed on it. COMPRESSION This defines whether the endpoint uses HTTP compression. Because SOAP messages can be rather verbose but, being XML- based, are prone to size improvements when compression algorithms are used, that is usually a performance improvement. You must ensure that your clients can deal with the compressed message format, however. As an example of the parameters we ve defined so far, the following CREATEstatement: CREATE ENDPOINT myendpoint STATE = STARTED AS HTTP ( SITE = * , PATH = /sql/mydatabase , PORTS = (CLEAR), COMPRESSION = ENABLED ) GO would partially define an endpoint with the symbolic name myendpoint that listens for requests at http://myservername/sql/mydatabase on port 80. Because security information is missing, the CREATE statement would not succeed; it s only for illustration. This endpoint is available at SQL Server startup. Note that myendpoint is only a symbolic name that identifies the endpoint in the SQL Server metadata and has no bearing on the physical HTTP endpoint. In this example, myservername is the DNS name of our machine that is running SQL Server. We specified this by using the SITE= * parameter, or since SITE= * is the default, we could have left it out all together. As nice a definition as this is, we can reach SQL Server but have no permission to do anything yet. We need to address security and add the FOR SOAPportion of the definition for that.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

PARAMETERS THAT RELATE TO SERVING HTTP Defining

PARAMETERS THAT RELATE TO SERVING HTTP Defining whether you can invoke specific procedures, arbitrary batches, or both Defining the exact format of the SOAP message Endpoint State First, we d like to point out that no HTTP endpoints are defined by default in SQL Server. When you install a fresh version of SQL Server on a .NET Server machine, you have no HTTP connectivity. Someone with administrative privileges has to define and enable HTTP endpoints before they are available; this behavior is for the sake of added security. All endpoints can be defined with state parameters. STATE When SQL Server comes up, it tries to establish an HTTP listener on the sites, paths, and ports that you specify, if STARTED is selected. If STOPPED is selected, the endpoint does not automatically service requests at startup time, but an administrator can enable it by using ALTER ENDPOINT… STATE=STARTED. Note that STOPPED is the default. If you specify DISABLED, SQL Server must be stopped and restarted for the endpoint to be enabled. Note that you can also enable or disable HTTP for the entire SQL Server instance by using the system stored procedure sp_configure. The entire T-SQL statement would look like this. option 0 turns it off option 1 turns it on sp_configure enable http , {0 | 1} Parameters That Relate to Serving HTTP Let s talk about Web server information, deferring the security information until a later section. There are a few parameters to CREATE ENDPOINT that are usually specified in the IIS metabase if you are using the IIS Web server. Because SQL Server is acting as the Web server in this case, these parameters must be defined in the DDL statement. These were not needed in SQLXML 3.0 because you were using IIS as a Web server. The relevant parameters are as follows. SITE This is the name of the Web site ( Web server ) that will be used by the client when connecting. If you specify * (asterisk), it means that you want to listen on all possible host names for the machines that are not otherwise explicitly reserved by other programs that serve HTTP (like IIS).

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

SQL SERVER AS A PLATFORM FOR WEB SERVICES

SQL SERVER AS A PLATFORM FOR WEB SERVICES time correlate these DDL statements with the COM object model that you would be using if you use SQLXML 3.0. The complete syntax for cataloging an HTTP endpoint definition in Transact-SQL follows. CREATE ENDPOINT endPointName [AUTHORIZATION ] [ STATE = { STARTED | STOPPED | DISABLED } ] AS HTTP ( [ SITE = { * | + | webSite } ,] PATH = url , PORTS = ({CLEAR | SSL} [,... n]) [, CLEAR_PORT = clearPort ] [, SSL_PORT = SSLPort ] , AUTHENTICATION =({ANON | BASIC | DIGEST | INTEGRATED} [,...n]) [, AUTH_REALM = { realm | NONE } ] [, DEFAULT_LOGON_DOMAIN = { domain | NONE } ] [, COMPRESSION = { ENABLED | DISABLED } ] [, RESTRICT_IP = { NONE | ALL } [, EXCEPT_IP = ({ <4-part-ip> | <4-part-ip>: } [,...n]) ] ) [ FOR SOAP ( [ { WEBMETHOD [ namespace .] methodalias ( NAME = three.part.name [, SCHEMA = { NONE | STANDARD | DEFAULT }] [, FORMAT = { ALL_RESULTS | ROWSETS_ONLY }]) } [,...n] ] [ BATCHES = { ENABLED | DISABLED } ] [ , WSDL = { NONE | DEFAULT | sp_name } ] [ , SESSIONS = { ENABLED | DISABLED } ] [ , SESSION_TIMEOUT = {int | NEVER}] [ , DATABASE = { database_name | DEFAULT } ] [ , NAMESPACE = { namespace | DEFAULT } ] [ , SCHEMA = { NONE | STANDARD } ] [ , CHARACTER_SET = { SQL | XML }] ) This syntax, seen in its entirety, may seem imposing at first. So to start with, let s break it down into its component pieces. Note that endpoints can be owned by a specific user by specifying the AUTHORIZATION keyword, just as with other SQL Server database objects. The parameters in CREATEENDPOINT that are used by HTTP endpoints are divided into these groups of functionality: Endpoint state Serving HTTP Authentication

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

HTTP ENDPOINT DECLARATION program that could consume WSDL

HTTP ENDPOINT DECLARATION program that could consume WSDL to know how to communicate with SQL Server. All the SQLXML 3.0 capabilities are available to SQL Server 2005 and SQL Server 2000. But if you have installed SQL Server 2005 on the Windows 2003 Server operating system, additional SOAP functionality can also be exposed directly from the SQL Server engine itself. The reason you need Windows 2003 Server is that in Windows 2003 Server the HTTP stack has been moved into the operating system kernel (this implementation is called HTTP.SYS). SQL Server 2005 Web Services use HTTP.SYS and do not require IIS. This not only allows faster execution of HTTP requests, but allows HTTP to be served from multiple applications running under the operating system, including SQL Server and IIS. You can service HTTP requests from both of them at the same time. Communication with SQL Server through SOAP makes the SOAP protocol an alternative to the TDS protocol. You can define which endpoints will be exposed through SOAP and what protocol these endpoints will use for SQL Server authorization, and use SSL to encrypt the data stream. In addition, you can configure the endpoint with the capability to accept batches of SQL directly. This makes SQL Server truly available to non- Windows clients and available directly over HTTP. No client network libraries are needed. The rest of this chapter will cover SQL Server 2005 s internal SOAP network libraries, although you might notice that most of the SOAP functionality is similar to that exposed in SQLXML 3.0 s ISAPI DLL. The biggest enhancement is that you can produce XML output by running a stored procedure or user-defined function that produces an instance or instances of the XML type as output. HTTP Endpoint Declaration The way that we defined an HTTP endpoint with SQLXML 3.0 was to use either a COM object model that wrote to the IIS metabase and the Windows registry, or to use a graphic user interface that encapsulated this object model. The new functionality is built directly into SQL Server. The information is stored in SQL Server metadata, and the way to define it is to use Transact-SQL. The relevant DDL statements are CREATE ENDPOINT, ALTER ENDPOINT, and DROP ENDPOINT. You can use these DDL statements to define endpoints for protocols other than HTTP (for example, SQL Server Service Broker endpoints), but in this chapter we ll only cover using them to define HTTP endpoints. We ll discuss them here and in the same

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

SQL SERVER AS A PLATFORM FOR WEB SERVICES

SQL SERVER AS A PLATFORM FOR WEB SERVICES required for full functionality. Not only is TDS a proprietary protocol, but it needs some special firewall configuration to work over the Internet. A specific network port (port 1433 in the default case) needs to be open on any firewall in order to communicate through TDS over TCP/IP. In addition, later versions of SQL Server use integrated security using NTLM or Kerberos security systems. NTLM will not pass through firewalls, and Kerberos will only with great difficulty. Most firewall administrators, with good reason, won t open the ports needed for users to connect directly to SQL Server over the Internet. Web Services expose a standard mechanism for communication that uses standard protocols and a common message format. The network protocol most often used is HTTP. The message format is known as SOAP. Web Services can be produced and consumed by any platform with an HTTP stack and an XML stack. It has become a popular means of communication among unlike systems and may displace proprietary protocols over time. SQL Server 2000 allowed communication via HTTP by using Internet Information Server and an ISAPI DLL. This DLL allowed users to issue HTTP requests (subject to security, of course) to well-known endpoints exposed with XML-based files known as templates. The ISAPI application parses the template and uses TDS to talk to SQL Server. These templates could use SQL or XPath queries, embedded in SQL. The result of these queries was XML in a well-known format, and this XML could also be postprocessed with XSLT inside the ISAPI DLL. In addition, with the proper configuration of the ISAPI application, users could enter endpoints that corresponded to SQL or XPath queries via a URL parameter. Through a number of post SQL Server 2000 Web releases, known as SQLXML, the functionality of the ISAPI application was expanded to support direct posting of updates in XML formats (known as DiffGrams and Update- Grams) and producing the XML output on the client side, allowing additional postprocessing capabilities. SQLXML 3.0 expanded the capability of the ISAPI DLL to include the production of SOAP packets, therefore exposing SQL Server through IIS as a Web Service. Any stored procedure, user-defined function, or template query can be exposed as a SOAP endpoint. Output is available in a variety of formats, some optimized for the .NET consumer, but all using the SOAP protocol. The ISAPI application was also expanded to produce Web Service Description Language (WSDL), a standardized dialect of XML that describes the format and location of a Web Service. This allowed any

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

10 SQL Server as a Platform for Web

10 SQL Server as a Platform for Web Services SQLXML 3.0 INTRODUCED the concept of exposing SQL Server as a Web Service, through the use of a special Internet Services API (ISAPI) DLL running under Internet Information Server. SQL Server 2005 moves this capability into the server itself, removes the need for Internet Information Server, and expands dramatically on the functionality provided by SQLXML 3.0. Mixing Databases and Web Services Communication with SQL Server (or any database management system, for that matter), has always required using a special proprietary protocol. In the case of SQL Server, this protocol is called TDS (tabular data stream) and uses a special set of client network libraries. These libraries are the SQL Server network libraries and are only available on Windows operating systems. Originally, the TDS protocol was shared with the Sybase database since they shared a mostly common codebase. Since then, each database has improved the protocol in different ways. Although SQL Server supports a backward-compatibility mode for using old versions of the TDS protocol, and therefore supports using Sybase network libraries on other operating systems, today these libraries support only a subset of Microsoft TDS functionality. A Windows client and operating system is

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

WHERE ARE WE? well as other, physical optimizations

WHERE ARE WE? well as other, physical optimizations such as types of indexes. With an XML data type, the possibility of strong typing through XML Schemas, and a query language that allows optimizations based on strong typing, XQuery users will most likely experience the same improvements in performance as the data type and query language matures. Programmers (and especially data center managers) like the idea of the same code running faster as vendors improve their parser engines, with minimal changes to the query code itself. Where Are We? SQL Server 2005 not only introduces XML as a new scalar data type, it introduces a query language and a data manipulation language to operate on it. The query language selected for operation inside SQL Server is XQuery, a new query language that is still in standardization. (At the time of this writing, XQuery was a W3C Working Draft). The XQuery implementation inside the database makes some simplifications and optimizations when compared with the entire specification. The subsetting is done to allow the queries to be optimizable and fast. This is a goal of XQuery itself, although the specification does not define implementable subsets. Because XQuery does not specify a data manipulation language, SQL Server provides a proprietary language that uses XQuery expressions to produce sequences to mutate, known as XML DML. The standardization of XML DML is being considered, because every implementation by relational or XML database vendors is different. This is reminiscent of the early days of SQL. In addition to the SQL Server XML engine, Microsoft provides an abstraction of query languages in the client XML stack. This can consume a query in any XML-based query language and produce a standard representation of the query. This abstraction will be used to expose XML Views over SQL data using the query language of the programmer s choice. Finally, because SQL Server 2005 can run .NET code, and SQL Server XML Views are based on .NET, it is feasible to run a normally client-side XML stack from within a stored procedure or user-defined function. This variation of XQuery, XPath, and XSLT is more likely to correspond more closely to the complete specification for these languages, but because it operates on documents in memory rather than directly accessing the database, it should be used sparingly in the server, based on document size. We ll explore XML Views and the client-side XML stack in Chapter 13.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

XML QUERY LANGUAGES: XQUERY AND XPATH Even though

XML QUERY LANGUAGES: XQUERY AND XPATH Even though the XML instance that is input to an XML function can be typed or untyped, remember that the result of an xml.query function is always an untyped XML instance. Typed XML should always be used, if possible, to give the XQuery engine a chance to use its knowledge of the data type involved. This also makes the resulting query run faster as well as producing more accurate results and fewer runtime errors. Optimization Decisions in the XQuery Engine The XQuery engine s implementation provides some optimizations over the the XQuery standard. It does this by restricting the functionality defined by the spec. There are four main restrictions: query over constructed sequences, usage of filter expressions other than at the end of the path, usage of order by with multiple or out-of-scope iterators and heterogeneous sequences. Here are some short examples. The following is a query over a constructed sequence. for $i in (for $j in //a return { $j }) return $i Next is a filter expression with a filter in the middle of the path. /a/b/(some-expression(.))/d Here is an example of using order bywith multiple iterators. for $x in //a for $y in //b order by $x > $y return $x, $y We ll discuss heterogeneous sequences in more detail here. In XQuery, sequences are defined to be able to contain all nodes, or all scalar values, or a combination of nodes and scalar values. SQL Server 2005 s XQuery engine restricts sequences to either all nodes or all scalar values; defining a heterogeneous sequence will result in an error. Permitting only homogeneous sequences allows the engine to optimize queries over sequences, because it will not try to determine the data type of each member of the sequence. Early SQL parsers were unoptimized; this was one of the reasons that early relational databases ran slowly. The performance improvement in relational databases since their inception is due, in no small way, to the optimization of SQL query processors, including static type analysis as

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services