Estimated lesson time: 60 minutes
Expanding on the data access technologies reviewed in the last lesson, this lesson will cover designing connections between SQL Server and heterogeneous clients. It will also cover what needs to be done to connect to XML Web services and another instance of a SQL Server service.
Not all computers that connect to your SQL Server service will be using a Windows operating system. It is not uncommon to have some users in your enterprise using other platforms, such as UNIX or Linux. However, non-Windows users can still connect to your SQL Server service through HTTP endpoints.
Important | SQL Server Enterprise Edition Required for HTTP Endpoints You cannot create an HTTP endpoint using SQL Server Express Edition. You must have the standard edition or higher to create endpoints and complete the lab in this lesson. |
HTTP endpoints allow you to expose data directly from your SQL Server without having to create an intermediary interface. This also enables non-Windows consumers to access the same data that Windows consumers can. You can create a stored procedure or user-defined function that exposes data both to internal company applications as well as external partners or customers that might be running on other platforms, such as UNIX.
HTTP endpoints can be created as TCP or HTTP. This indicates the protocol that will be used to send the data across the network. TCP is applicable when there is a need to create endpoints for database mirroring or service brokering. HTTP is the protocol to use when providing native XML Web services through SQL Server.
You must be a member of the sysadmin role to create endpoints. Endpoints are created using the CREATE ENDPOINT statement in a SQL query window. The following is an example of an HTTP endpoint named GetProducts, which is used to retrieve product information from the AdventureWorks database using a stored procedure named GetProductListing:
CREATE ENDPOINT GetProducts STATE = STARTED AS HTTP ( SITE = 'localhost', PATH = '/sql/products', AUTHENTICATION = (INTEGRATED), PORTS=(CLEAR) ) FOR SOAP ( WEBMETHOD 'GetProductListing' ( name='AdventureWorks.dbo.GetProductListing', schema=STANDARD ) , WSDL = DEFAULT, DATABASE = 'AdventureWorks', BATCHES=ENABLED )
The first thing to notice about this statement is that the STATE is set to a value of STARTED. This indicates that the endpoint listener will be active shortly after it is created. If we did not specify this, the statement would execute, but the endpoint would not be available for requests. Alternative values for this option are STOPPED and DISABLED.
In the CREATE ENDPOINT statement, specify that this will be an HTTP endpoint and that it will use INTEGRATED authentication. By selecting integrated authentication, either NTLM or Kerberos can be used as part of the authentication challenge. Endpoints can be set with one or more of the following as authentication options:
Basic Uses a base64-encoding strategy, and is not considered secure unless you also use a Secure Socket Layer (SSL) port, which represents a secure connection to encrypt the data. An SSL certificate will contain the IP address and port information.
Digest Uses a username and password that is then encoded with a one-way hashing algorithm. This will map to a valid Windows domain account.
Integrated Indicates that authentication will occur using either NTLM or Kerberos authentication methods.
NTLM Uses a challenge-response protocol; this is the authentication mechanism used by Windows 95, Windows 98, and Windows NT 4.0.
Kerberos Uses a standard Internet authentication method and is supported by Windows 2000, Windows XP, or Windows 2003.
Selecting CLEAR as the port means you will be going through the default port 80, which is not a secure channel. To use a secure channel, you would change the ports option to SSL, but you would also need to register a secure certificate on the server hosting SQL Server 2005.
The GetProducts endpoint will retrieve data using the GetProductListing stored procedure. The stored procedure represents the Web method for the native XML Web service. The GetProductListing stored procedure executes a query against the AdventureWorks database and appears as follows:
CREATE PROCEDURE dbo.GetProductListing AS SELECT name, productnumber, listprice FROM production.product GO
Data will be returned from the endpoint in the Simple Object Access Protocol (SOAP) format. SOAP is a transport protocol that provides a standard way to send messages between applications. Allowing the endpoint to use SOAP as the transport protocol is specified in the FOR portion of the CREATE statement and is the best choice when returning data across the Internet. The FOR clause is used to specify the payload for the endpoint. Alternatively, the FOR clause could have been set as Transact-SQL (TSQL), service broker (SERVICE_BROKER), or database mirroring (DATABASE_MIRRORING).
By specifying the WSDL = DEFAULT switch, you are able to expose the Web Services Description Language (WSDL) to potential consumers. At this point, the PATH option is used to indicate where to locate the WSDL for the endpoint. You can get information about what the endpoint returned by querying the WSDL file at http://localhost/sql/products?WSDL.
By enabling batches for the GetProducts endpoint, you are allowing ad hoc SQL to be executed against the endpoint. The SOAP request will need to contain the <sql:sqlbatch> element. Within this element is another element named <BatchCommands> that contains one or more SQL statements. There can also be a <Parameters> element to specify any optional parameters needed for the queries. The following is an example of a SOAP request that contains an ad hoc query:
<?xml version="1.0" encoding="utf-8" ?> <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <soap:Body> <sqlbatch xmlns="http://schemas.microsoft.com/sqlserver/2004/SOAP"> <BatchCommands> SELECT EmployeeID, FirstName, LastName FROM Employee WHERE EmployeeID=@x FOR XML AUTO; </BatchCommands> <Parameters> <SqlParameter Name="x" SqlDbType="Int" MaxLength="20" xmlns="http://schemas.microsoft.com/SQLServer/2001/12/SOAP/types/SqlParameter"> <Value xsi:type="xsd:string">1</Value> </SqlParameter> </Parameters> </sqlbatch> </soap:Body> </soap:Envelope>
The SOAP request includes a query that returns employee data as an XML stream for a particular employee. Because the employee ID needs to be passed in as a parameter for the query, the <Parameters> element is included.
Enabling batches for an endpoint enables users to execute any query against the database. This enables them to get data beyond what is returned from the Web method assigned to the endpoint. Essentially, they are able to execute any query allowed by the SQL Server permissions. If you decide to enable batches for HTTP endpoints, ensure that the user accounts accessing those endpoints have proper permissions assigned.
In the previous section, you saw how HTTP endpoints are created to return data directly from an instance of SQL Server. This is all part of a new capability offered with SQL Server 2005, known as native XML Web services. Essentially, you are able to use HTTP and SOAP to deliver Web services from SQL Server 2005 directly. This enables you to bypass IIS and the need to create Web services using Visual Studio 2005.
Important | IIS Not Required for Endpoints on Windows Server 2003 If you are creating endpoints on a Windows 2003 Server server, you do not need to have IIS installed. SQL Server 2005 will utilize the http.sys module in the Windows Server 2003 kernel. |
Using Web services to host your SQL Server data is great when you have a large amount of data that needs to be retrieved quickly and efficiently. Examples of this include online product catalogs, staff listings, stock results, and music listings.
The use of Web services can also be helpful in the generation of reports. You can easily create a stored procedure to generate the data needed and then expose it using Web services in SQL Server 2005. The data can then be consumed by another application or just embedded into an Excel document. By using Excel as the display mechanism, you do not have to worry about creating a special application that acts as an interface to your data.
Only authenticated users are allowed to access your native XML Web services. There is no support for anonymous users, so all incoming SOAP requests must be authenticated. You can choose to handle security using the Windows user logon or the SQL Server logon. In the sample code created earlier, the endpoint was directed to use Windows authentication.
If Windows authentication is used, no additional SOAP authentication headers are required. You only need to worry about additional SOAP authentication if you use SQL Server to authorize your users. If you do use SQL authorization, you will need to utilize Web services security using Web Services Enhancements (WSE). You will also need to modify the Transact-SQL statement used to create your endpoint. If you decide to use SQL authentication, you will need to set an option in the FOR clause that specifies that the logon type will be mixed. The following is an example of a CREATE ENDPOINT statement that uses SQL authorization.
CREATE ENDPOINT GetProducts STATE = STARTED AS HTTP ( SITE = 'localhost', PATH = '/sql/products', AUTHENTICATION = (BASIC), PORTS=(SSL) ) FOR SOAP ( WEBMETHOD 'GetProductListing' ( name='AdventureWorks.dbo.GetProductListing', schema=STANDARD ) , LOGIN_TYPE = MIXED, WSDL = DEFAULT, DATABASE = 'AdventureWorks', BATCHES=ENABLED )
When you create an endpoint using the CREATE ENDPOINT statement, you automatically have access to it, but for other SQL Server users to see and access it, you must specifically grant them access. To do this, you will use the GRANT statement. In the following example, a user named TestUser in the MyDomain domain has been granted access to the endpoint named GetProductListing:
GRANT CONNECT ON ENDPOINT::GetProductListing TO [MyDomain\TestUser]
If the endpoint was created using the default WSDL option, clients can use the WSDL document to generate proxy code. The proxy code contains the instructions for how the Web service should be called and what interface it is using. This will be used by the client application to access the Web service method. The WSDL document contains information about the Web service, such as the method name, what parameters are used, and what return values are provided.
Using Microsoft Visual Studio 2005, you can add a Web reference to the endpoint using a URL. If the client application is an ASP.NET project, the reference is added by selecting Add Web Reference from the Website menu. You will then see a dialog box similar to the one shown in Figure 1-6. If the client application is a Windows application project, the reference is added by selecting Add Web Reference from the Project menu. You would then select the link for Web services on the local machine. In the URL text box, you would type the path to the WSDL file for your SQL Web service. (For the previous example, this was http://localhost/sql/products?WSDL.) This will generate the necessary proxy code for your client application.
Figure 1-6: Add Web Reference dialog box for an ASP.NET project created with Visual Studio 2005
Once the Web reference has been added, you can access the Web method exposed by the Web service. You will need to pass in the necessary authentication credentials because this is a SQL Server requirement. The following code could be used to access the GetProducts endpoint created earlier:
//C# SQL.GetProducts ws = new SQL.GetProducts(); ws.Credentials = new System.Net.NetworkCredential("Administrator", "Pass@word", "MyDomain"); object[] objList = ws.GetProductListing(); DataSet ds = new DataSet(); //Loop through the array of objects returned until //you get to the dataset foreach (object o in objList) { if (o is DataSet) { ds = (DataSet)o; } } 'VB Dim ws As New SQL.GetProducts ws.Credentials = System.Net.CredentialCache.DefaultCredentials Dim ds As New DataSet Dim objList As Object() = ws.GetProductListing 'Loop through the array of objects returned until 'you get to the dataset Dim o As Object For Each o In objList If o.GetType.ToString = "System.Data.DataSet" Then ds = o End If Next DataGridView1.DataSource = ds.Tables(0) ws = Nothing
SQL Server 2005 enables you to install multiple instances of the SQL Server service. The additional instances are known as named instances, and each will be assigned a unique name, which is used to identify it. You will need this name when creating the connection string used to connect to the additional instance. If the instance was installed on a port number other than the default, you will also need to specify the port number with the instance name.
For example, the following connection strings can be used to connect to a named instance of the SQL Server service that has been assigned to port number 1431:
//C# string connString = @" server=.\SERVER02,1431;" & _ "Integrated Security=SSPI;" & _ "Database=AdventureWorks" 'VB Dim connString As String = "server=.\SERVER02,1431;" & _ "Integrated Security=SSPI;" & _ "Database=AdventureWorks"
With SQL Server 2005, you can create linked servers, which allow you to execute commands against an OLE DB source. The data source could be a heterogeneous data source, such as Oracle, or just another instance of the SQL Server service.
You can create a linked server using SQL Server Management Studio or a SQL script. To create a linked server using SQL Server Management Studio, expand the Server Objects node from the Object Explorer pane. From here, you should see a node called Linked Servers. You create a new linked server by right-clicking the Linked Servers node and selecting New Linked Server.
The New Linked Server dialog box shown in Figure 1-7 enables you to specify the server type as SQL Server or Other data source. If the server type is SQL Server, you need to type the instance name in the Linked Server text box.
Figure 1-7: Add New Linked Server dialog box in SQL Server Management Studio
After the linked server has been created, you can execute queries and commands against the linked server using a four-part name. The name appears as linked_server_name.catalog.schema.object_name.
Its four parts are:
linked_server_name Name of the linked server as it was specified in the New Linked Server dialog box. For a SQL Server linked server, this is the instance name.
Catalog For a SQL Server linked server, this is the name of the database.
Schema For a SQL Server 2005 instance, this refers to the user-schema, which is a collection of database entities that forms a single namespace. For earlier versions of SQL Server, this portion of the four-part name is the database owner, such as dbo.
Object_name This is the object being accessed, which is typically a table name. It could also be another object, such as a view or a user-defined function.
In this lab, you will create an HTTP endpoint that is used to get information about engineering employees from the AdventureWorks database. In the first exercise, you will create the HTTP endpoint on SQL Server 2005. In the second exercise, you will create a Windows forms project that references the endpoint and displays the data returned in a dataGridView control.
The completed code examples, in both Visual Basic and C#, are available in the \Labs\Chapter 01 folder on the companion CD.
Exercise 1: Create an HTTP Endpoint
In this exercise, you will create a stored procedure to retrieve engineering employee information. The stored procedure will be referenced by a newly-created HTTP endpoint.
Open Microsoft SQL Server Management Studio, and connect to the local instance of SQL Server.
Click New Query, and type the following SQL statement to create a stored procedure named GetEngineeringEmployees. Ensure that you receive a message stating the commands completed successfully.
CREATE PROCEDURE dbo.GetEngineeringEmployees AS SELECT e.employeeid, e.title, c.firstname, c.middlename, c.lastname FROM humanresources.employee e LEFT JOIN person.contact c ON e.contactid = c.contactid LEFT JOIN humanresources.employeedepartmenthistory ed ON e.employeeid = ed.employeeid WHERE ed.departmentid = 1 AND (startdate < getdate() AND enddate is null) GO
Select the AdventureWorks database from the Available Databases drop-down list, and then click Execute.
In the same query window, type the following SQL statement to create an HTTP endpoint named GetEngineeringList. Ensure that you receive a message stating the commands completed successfully.
Important | Stop IIS to Complete Exercise If the machine you are using for this exercise has IIS installed on it, you will have to stop the IIS Admin Service to be able to create your endpoint. Otherwise, you will receive an error stating that the port is already bound to another process. Go to Services in Control Panel to stop the IIS Admin Service. When you restart the IIS Admin Service, be sure to also restart the World Wide Web Publishing Service. |
CREATE ENDPOINT GetEngineeringList STATE = STARTED AS HTTP ( SITE = 'localhost', PATH = '/sql/employees', AUTHENTICATION = (INTEGRATED), PORTS=(CLEAR) ) FOR SOAP ( WEBMETHOD 'GetEngineeringEmployees' ( name='AdventureWorks.dbo.GetEngineeringEmployees', schema=STANDARD ) , WSDL = DEFAULT, DATABASE = 'AdventureWorks', BATCHES=ENABLED )
Select the AdventureWorks database from the Available Databases drop-down list, and then click Execute.
Open Internet Explorer, and type the URL http://localhost/sql/employees?WSDL.
Important | Possible delay There could be a delay in the creation of the WSDL document for your endpoint. If you get an error, wait a few seconds and try typing the URL again. |
Inspect the WSDL document displayed in the browser window. Select Find, and do a search for GetEngineeringEmployees. Note that the XML associated with the stored procedure only occupies a small portion of the document.
Exercise 2: Consume the HTTP Endpoint
In this exercise, you will create a Windows forms application using Visual Studio 2005, and add a reference to the HTTP endpoint created in Exercise 1.
Open Microsoft Visual Studio 2005.
If you completed the lab from Lesson 1, you can skip to step 4.
On the File menu, select New, Project.
In the New Project dialog box, expand the Other Project Types node, and select Visual Studio Solutions. Type TK442Chapter1 for the name of your blank solution, and place it in a directory of your choosing. A new solution file will be created, and you can now add multiple projects to this solution. You will add one project for each lab included in this chapter.
On the File menu, select Add, New Project. Select Windows Application as the template, and type Lab2 as the project name. Set the language by selecting Visual Basic, Visual C#, or Visual J# from the language drop-down list box. By default, Visual Studio will select the language specified when it was first configured.
From the Project menu, select Add Web Reference.
From the Web Reference dialog box, type the URL http://localhost/sql/employees?WSDL and then click Go.
You should see the GetEngineeringList method displayed in the methods pane. Note that you also have access to a method named sqlbatch. This method is available because the endpoint was created using the BATCHES=ENABLED option.
Type SQL as the Web reference name, and click the Add Reference button to add the reference.
From the Toolbox, drag a button control onto the Form1 design surface. Use the following properties for this control:
Name = btnWebService
Text = "Connect to Web service"
From the Toolbox, drag a dataViewGrid control onto the Form1 design surface. Leave the properties with the default values.
Right-click the Form1 file from Solution Explorer, and select View Code. Paste the following code into the code window:
//C# private void btnWebService_Click(object sender, EventArgs e) { try { SQL.GetProducts ws = new SQL.GetEngineeringList(); //Use the credentials of the user that is logged in ws.Credentials = System.Net.CredentialCache.DefaultCredentials; object[] objList = ws.GetEngineeringEmployees(); DataSet ds = new DataSet(); //Loop through the array of objects returned until //you get to the dataset foreach (object o in objList) { if (o is DataSet) { ds = (DataSet)o; } } dataGridView1.DataSource = ds.Tables[0]; } catch (Exception ex) { MessageBox.Show(ex.Message); } } 'VB Private Sub btnWebService_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnWebService.Click Try Dim ws As New SQL.GetEngineeringList 'Use the credentials of the user that is logged in ws.Credentials = System.Net.CredentialCache.DefaultCredentials Dim ds As New DataSet Dim objList As Object() = ws.GetEngineeringEmployees 'Loop through the array of objects returned until 'you get to the dataset Dim o As Object For Each o In objList If o.GetType.ToString = "System.Data.DataSet" Then ds = o End If Next DataGridView1.DataSource = ds.Tables(0) ws = Nothing Catch ex As Exception MessageBox.Show(ex.Message) End Try End Sub
Save the Lab2 project by going to the File menu and selecting Save All.
Right-click the Lab2 project from Solution Explorer, and select Set As Startup Project.
Press Ctrl+F5 to build the project without debugging. Ensure that the project builds successfully. Form1 should appear after the project compiles and executes. You should see an Open Web Service button; click this button. The data results are displayed in the dataViewGrid control, as shown in Figure 1-8.
Figure 1-8: Form1 as it displays a list of all engineering employees from the AdventureWorks database