Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 23, 2003

XML and SQL 2000 (Part 3) - Page 2

By Marcin Policht

  • Data Source - determines the name of the SQL server and the target database, to which virtual directory corresponds.

  • Settings - options specified on this page control the level of access to SQL server (in addition to the ones specified on the Security tab) and include the following operations:

    • "Allow sql=... or template=... URL queries" - this option refers to including T-SQL statements as part of a URL string that is used to access the virtual directory (via HTTP GET method). Even though this is the simplest and most convenient option from the implementation perspective, it is rarely used in production systems due to its security implications. Once this option is enabled, you have no control over the type of statements that users will attempt to run on the target database (of course, these statements are still subject to restrictions resulting from access rights granted to users accounts, and these, in turn, are determined by the settings on the Security tab).

    • "Allow posted updategrams" - limits the type of URL queries to data modification statements in the form of XML updategrams. I will describe this option in more detail in the next article of this series.

    • "Allow template queries" - provides a very effective way of controlling T-SQL statements, (the only option enabled by default), that can be executed by users accessing the Web server via the virtual directory. This is accomplished by including appropriately formatted SQL statements that users will be allowed to run inside an XML template file stored in this virtual directory.

    • "Allow XPath" - uses XPath-based XML mapping schema, stored as a file in the virtual directory, to determine which SQL server data will be accessible to users. XPath determines the elements and attributes in the XML document, which correspond to (or, in other words are mapped to) tables, columns, and rows in the target database. I will elaborate on this topic in my next article, but for introduction to XPath, you can refer to the previous article of this series.

    • "Allow POST" - provides more flexibility in accessing the Web server by allowing HTTP POST method for sending updategrams and templates. POST method does not have the size limitations imposed on GET and HEAD HTTP methods (which can be enabled with the first option). Note, however, that allowing POST makes the server vulnerable to some common Denial of Service exploits (this can be mitigated by using another configuration option on this page, which limits size of the POST queries). Allowing POST has also the same security implications as allowing URL queries (the first option on the Security tab, described above).

  • Virtual Names - in addition to assigning an "alias" (virtual name) to the folder representing the SQL server database (using the previously mentioned General tab of the New Virtual Directory properties), you can also assign aliases to folders and files stored in the virtual directory on the web site, as well as to binary objects stored in the target database. This will affect the URL path that will be used to access these folders, files or database objects via a browser.

  • Advanced - consists of three sections. The top one, labeled "ISAPI Location," specifies the name of the folder where the SQLISAPI DLL file is stored. If you updated the SQLXML version, you can determine the version number by checking the file name (this would be SQLIS2.DLL and SQLIS3.DLL for SQLXML version 2.0 and 3.0 respectively). The middle section "Additional user settings" allows you to append additional options to the URL string, that will be translated into a T-SQL statement submitted to the SQL server. Finally, the bottom section contains caching options. Typically you would use the default values (which keeps caching of XML mapping schemas and templates enabled), which increases the speed of query processing. Caching is typically disabled in development environments, to force reloading modified schemas and templates.

Note that the options described above are based on the IIS Virtual Directory Management for SQL Server included in the Windows 2000 server. If you installed the newer, downloadable versions of the SQLXML component, you will have additional choices (which I will be also discussing).

In this article, we covered the initial steps necessary for configuring IIS Virtual Directory for access to SQL server. Some of the options described above might be a bit unclear, but examples I present next should clarify their meaning and purpose. In particular, we will look at rules governing forming URL queries, template queries and XPath mapping schemas.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date