SQL WebBench is a stand alone browser based application that demonstrates Native SQL Web Services in Microsoft® SQL Server™ "Yukon" Beta 1. SQL Web Bench allows for HTTP SOAP endpoint manipulation and the ability to invoke remote SQL web services right from your browser via a WSDL XSL transform. SQL Web Bench demonstrates and exposes the raw SOAP that is sent and received from the server.
Note: SQL Web Bench can be run straight from the command line, IIS is not required.
Quick start
__________
1. Run default.htm
2. Enter SQL Server name
3. Say Yes to create default endpoint
4. Accept default endpoint name
Note: If the user executing this application is not a valid SQL user,
then they will be unable to access the application. In this case, execute the
following to add the user.
|
|
sp_adduser @loginame=[DOMAIN_NAME\USERNAME] or [MACHINE_NAME\USERNAME]
|
___________________________________________________________________________________________________________________________
Table of contents
|
Section
|
Content
|
|
1.
|
What is Sql Web Bench?
|
|
2.
|
Checklist
|
|
3.
|
Installation
|
|
4.
|
Walkthrough
|
|
5.
|
Troubleshooting
|
___________________________________________________________________________________________________________________________
Section 1: What is Sql Web Bench?
Sql Web Bench is a web based application that demonstrates HTTP SOAP endpoint
manipulation and the ability to invoke remote webmethods. Sql Web Bench enables
users to create, alter and drop endpoints without having to know the syntax to
perform this task. The user only needs to enter the values of the parameters
they need to manipulate the endpoint Data Definition Language (DDL) and click on the
appropriate buttons to execute the DDL. The same is true for webmethods
associated with the endpoints. The DDL interface for the webmethods is similar
to that of the endpoints.
This application allows the user to invoke the webmethods exposed at an
endpoint. It also allows a user to write sql queries.
Execution of all the statements in this application are performed using SOAP
requests. The responses are received in the form of SOAP responses.
Section 2: Checklist
If the user using this application is not a valid SQL user, then they will be
unable to access the application. In this case, execute the following to add
the user.
|
|
sp_adduser @loginame=[DOMAIN_NAME\USERNAME] or [MACHINE_NAME\USERNAME]
|
Section 3: Installation tips
Extract the contents of sqlWebApp.zip folder to a directory. Double click on
the default.htm file to start the application.
Section 4: Walkthrough
On loading the application, a window prompts you to enter a server name that you
wish to connect to. This is the machine on which SQL Server is; the machine on
which you want to do endpoint manipulation, webmethod manipulation and/or expose
these webmethods. Enter the server name and click the "OK" button.
There are 5 tabs displayed in the application "Config", "Web methods", "Query",
"Request" and "Response".
Tab 1: "Config"
This tab displays the endpoint and webmethods information of the server at that
instant. The top of this tab displays the server that you are connected to. This
server value is the value that you entered on loading the application. This is
displayed as "Connected to server <SERVERNAME>". You can change this value
by clicking on the "change" link in parentheses. You can also view this readMe file
at anytime during the application by clicking "Help" at the top right corner of the
"Config" tab.
The information in this tab is divided into 2 main parts, the endpoint
manipulation using DDL and the webmethods manipulation using DDL.
Part 1: Endpoint DDL
- Endpoint Properties: This displays the endpoints available at the server in a
drop down list box called "Server Endpoints" and the state that the endpoints are in
(Started/Stopped) in a drop down list box called "Endpoint State"
- HTTP Properties: This displays the http properties associated with the
endpoint. These include:
|
Attribute
|
Value
|
|
Web Site
|
The website that the endpoint is desired to be at. In case of existing
endpoints, this displays the site at which the endpoint was created.For
creating or altering the location for an endpoint, this value should be set to
the desired path. If this is not done, the default for this attribute will be
set to *.
|
|
URL Path
|
The path under the website. The wsdl for the endpoint will be located at
http://Website/URLPath?wsdl. For existing endpoints, this value
gives the path for the existing endpoint. For creating or altering an endpoint
path, this value needs to be specified. The default value for this attribute is
set to /SqlWebBench.
|
|
Anonymous Authentication
|
If checked, enables anonymous authentication. For existing endpoints, this box
is checked if the user enabled anonymous authentication at the time of endpoint
creation.
|
|
Basic Authentication
|
If checked, enables basic authentication. For existing endpoints, this box is
checked if the user enabled basic authentication at the time of endpoint
creation.
|
|
Digest Authentication
|
If checked, enables digest authentication. For existing endpoints, this box is
checked if the user enabled digest authentication at the time of endpoint
creation.
|
|
Windows Authentication
|
If checked, enables windows authentication. For existing endpoints, this box is
checked if the user enabled windows authentication at the time of endpoint
creation.
|
|
Clear Port
|
This is checked if the user enabled clear port during endpoint creation. If it
is checked then it displays the port value in the text box adjacent to the
"Clear Port" checkbox. This value can be changed for altering the endpoint or a
value can be entered while creating an endpoint. The default value for this is
80. If the "Clear Port" box is checked and the user does not enter a value for
the clear port, the user gets an error "You need to specify a clear port value
if 'Clear Port' is checked".
|
|
SSL Port
|
This is checked if the user enabled SSL port during endpoint creation. If it is
checked then it displays the port value in the text box adjacent to the "SSL
Port" checkbox. This value can be changed for altering the endpoint or a value
can be entered while creating an endpoint. If the "SSL Port" box is
checked and the user does not enter a value for the SSL port, an error message
is displayed ""You need to specify an ssl port value if 'SSL Port' is checked"
Note: Either "Clear Port" or "SSL Port" value must be checked for any endpoint
otherwise the user will be displayed with an error "Either Clear or SSL ports must
be enabled".
|
|
Restrict IP
|
This is set to "NONE" if no IPs are restricted, and to "ALL" if all of them are
restricted. This value can be changed at the time of endpoint creation or when
altering an existing endpoint.
|
|
Except IP
|
This is the value of IP that is allowed, when the "Restrict IP" is set to
"ALL". This value can be changed at the time of endpoint creation or when
altering an existing endpoint.
|
- SOAP Properties: This displays all the SOAP related properties of the
endpoint. These include:
|
Attribute
|
Value
|
|
Enable SOAP
|
If checked it enables SOAP in the endpoint.
|
|
SQL Batch
|
If checked, sql batch support is enabled at the endpoint. So, users can execute
queries using this.
|
|
WSDL Language
|
If set to "DISABLED", disables the user's ability to view the wsdl using the
"Website" and "URLPath" values described in the HTTP Properties section. To be
able to see the WSDL, this must be set to "ENABLED". The value can be
changed while altering an endpoint and during endpoint creation.
|
|
XML Charset enforced
|
This enforces XML charset if "XML" is set and SQL if "SQL" is checked. The
value can be changed while altering an endpoint and during endpoint creation.
|
|
Default Result schema
|
This displays if the endpoint has a "Standard" result schema or no result
schema. This value can be changed at endpoint creation or while altering the
endpoint.
|
|
Default Database
|
This displays the default database name that is used by that endpoint. You can
specify a value for this on endpoint creation or change the existing value
while altering the endpoint.
|
|
Default namespace
|
This displays the namespace reserved for the endpoint. The value can be changed
at endpoint creation time or while altering the endpoint.
|
There are three buttons used for executing Endpoint DDL statements. If you need any help on
endpoints, click on the "DDL for Endpoint" hyperlink. You will be redirected to the
corresponding help available on Books Online. You need to have Books Online installed to be
able to view this.
|
Button
|
Action
|
|
Create
|
This button should be clicked if you wish to create an endpoint. Enter all the
values in the appropriate "Endpoint properties", "HTTP Properties" and "SOAP
Properties" for the endpoint to be created. Then click the "Create" button. You
will be prompted for the new endpoint's name. Enter the name and click "OK". If
there were no errors with your input, you will be alerted with "Endpoint
<endpointName> has been created successfully". Click the "OK" button.
You will now see the new endpoint added to the drop down list of available
endpoints.
|
|
Alter
|
This button should be clicked if you wish to alter an existing endpoint. Select
the endpoint that you wish to alter from the "Endpoints" drop down list box.
Change the values of attributes that you wish to modify. Click the "Alter"
button. You will be displayed with an alert "Please confirm you would like to
ALTER ENDPOINT <endpointName>". Click "OK". If there were no
errors with the changes that you made, you will be alerted with "ENDPOINT
<endpointName> has been ALTERED successfully". Click the "OK" button.
|
|
Drop
|
This button should be clicked if you wish to drop an existing endpoint. Select
the endpoint that you wish to drop for the "Endpoints" drop down list box.
Click "Drop". You will be alerted with "Are you sure you would like to drop
<endpointName> endpoint?" Click "OK". You will be alerted with
"Endpoint <endpointName> has been dropped successfully". Click the
"OK" button. You will not see the dropped endpoint in the drop down list of
available endpoints.
|
Part 2: Webmethods DDL
- "Web methods on <ENDPOINT>": This displays information about the existing
webmethods for a particular endpoint. The information includes:
|
Attribute
|
Value
|
|
Web method alias
|
This drop down list box displays the names of the existing webmethods for a
particular endpoint.
|
|
Webmethod Namespace
|
This text box displays the namespace associated with the webmethod. This value
can be changed at the time of webmethod creation or while altering an existing
webmethod.
|
|
Current Object name
|
This text box displays the current webmethod's object.
|
|
Result schema
|
This drop down list displays the result schema type of the webmethod. A
webmethod may have either no schema, in which case this value is set to "NONE",
or standard, in which case this value is set to "STANDARD". This value can be
changed at the time of webmethod creation or while altering an existing
webmethod.
|
|
Result type
|
This drop down list displays the result type of the webmethods. There are two
types possible: "ALL_RESULTS" or "ROWSETS_ONLY"
|
There are three buttons used for executing Webmethod DDL statements. If you need any help on
webmethod manipulation, click on the "DDL for Endpoint" hyperlink. You will be redirected to
the corresponding help available on Books Online. You need to have Books Online installed to
be able to view this.
|
Button
|
Action
|
|
Create
|
This button should be clicked if you wish to create a webmethod. Enter all the
attributes for the webmethod to be created. Then click the "Create" button. You
will be prompted for the new webmethod's name. Enter the name and click "OK".
If there were no errors with your input, you will be alerted with "Webmethod
<webmethodName> has been created successfully". Click the "OK"
button.
|
|
Alter
|
This button should be clicked if you wish to alter an existing webmethod.
Select the webmethod that you wish to alter from the "Existing webmethods" drop
down list box. Change the values of attributes that you wish to modify. Click
the "Alter" button. You will be displayed with an alert "Please confirm you
would like to ALTER WEBMETHOD <webmethodName>". Click "OK". If
there were no errors with the changes that you made, you will be alerted with
"WEBMETHOD <webmethodName> has been ALTERED successfully". Click
the "OK" button.
|
|
Drop
|
This button should be clicked if you wish to drop an existing webmethod. Select
the webmethod that you wish to drop for the "Existing Webmethods" drop down
list box. Click "Drop". You will be alerted with "Are you sure you would like
to drop <webmethodName> webmethod?" Click "OK". You will be
alerted with "Webmethod <webmethodName> has been dropped
successfully". Click the "OK" button.
|
Tab 2: "Web methods"
Note: The webmethods are visible only if the "WSDL Language" is set to
"Default" during endpoint creation.
This tab exposes the webmethods available at a given endpoint. It allows
remote invokation of these webmethods. It displays the webmethod name, its
parameters and textboxes for inputing the values for these parameters and an
"Invoke" button. On entering appropriate values for the parameters for an
individual webmethod, and clicking the "Invoke" button, a SOAP request is
generated and sent to the server. The server then processes the request and
sends back an appropriate SOAP response.
A special type of webmethod "sqlbatch" is displayed here, if the "Enable SQL Batch"
box is checked during endpoint creation. This webmethod allows querying. Like all
other webmethods, on entering a well-formed sql query string, and clicking the
"Invoke" button, a SOAP request is generated and sent to the server. The server
then processes the request and sends back an appropriate SOAP response.
Executing a sqlbatch query without a parameter:
select name from sys.objects
When the user hits Invoke after entering the above string in the BatchCommands
input box, the response is displayed in the "Response" tab.
Executing a sqlbatch query with a parameter:
Batchcommands: print @Id
Parameters: <sqlparameter:sqlParameter><value>1</value></sqlparameter:sqlParameter>
Tab 3: "Query"
This tab is used to execute queries in SQL. You can write a query, or a set of
SQL statements and select them and execute them by pressing the "F5" key on the
keyboard. Alternatively, you can execute the queries using the "Query" button
to the top right corner. The results of the queries are displayed in the
"Response" tab. The tabular form of the results can also be viewed in the
respective result's tab.
On selecting a section of text from this tab, and hitting F1, the user is
redirected to the equivalent search for that string on MSDN.
Tab 4: "Request"
The SOAP request generated for the webmethod or the query can be seen in the
"Request" tab.
Tab 5: "Response"
The SOAP response received for the webmethod or the query can be seen in the
"Response" tab.
This tab also has the "Update URL" button. This button is displayed when the
user executes a query from the "Query" tab. The "Update URL" allows the user
to be able to pass around queries for "repro" purposes.
Section 5: Troubleshooting
- If you are running off of a webserver, to use this application, this script
must be added to your "trusted sites". This can be done as follows:
|
Step
|
Action
|
|
1.
|
In Internet Explorer, click on the "Tools" menu
|
|
2.
|
In the "Tools" menu, click on "Internet Options"
|
|
3.
|
In the "Internet Options" window, click on the "Security" tab
|
|
4.
|
In the "Security" tab, click on the "Trusted sites" icon
|
|
5.
|
This will display a "Sites..." button below the icon
|
|
6.
|
Click on the "Sites..." button
|
|
7.
|
This will display the "Trusted sites" window
|
|
8.
|
In the "Add this website to the zone", enter http://sqlwb
|
|
9.
|
Uncheck the "Require server verification (https:) for all sites in this zone"
box
|
|
10.
|
Click the "Add" button located on the right of the field where you entered
http://sqlwb
|
|
11.
|
Click the "Close" button
|
|
12.
|
This will get you back to the "Security" tab window. Click the "OK" button
|
You have now, successfully added the application to your trusted sites.
- If the service account is a domain user account, the object for this account
in the active directory must have an attribute named 'servicePrincipalName'.
The values for this attribute are the following:
|
|
HOST/<SERVERNAME>
|
|
|
HOST/<SERVERNAME>.<FULLY_QUALIFIED_DOMAIN_NAME>
|
The fully qualified name must be in the form of domain.company.com. To add and
remove these values to the service's domain user account in the active
directory, SQL server has two stored procedures:
|
|
sp_register_kerberos_spn_for_http
|
|
|
sp_delete_kerberos_spn_for_http
|
To call these procedures, one must be part of the sysadmin role. For these
procedures to be effective, the caller also must have read and write rights on
the domain user account in the active directory. For more details on this
subject, please refer to "Registering Kerberos SPNs (Managing HTTP Endpoints)"
in Books Online.
Alternatively, SQL Server could be logged on as local system or a network
service.
The sample code is available here.
Copyright and Disclaimer
This is a preliminary document and may be changed substantially prior to final commercial release of the software described herein.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
Copyright 2003 Microsoft Corporation. All rights reserved.
Microsoft is a registered trademark of Microsoft Corporation in the United States and/or other countries.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.