Lesson 2: Setting Up Additional Features

3 4

SQL Server 2000 can send and receive e-mail, notify pagers, connect with linked servers for distributed queries, and integrate with IIS for XML support. To take advantage of each of these features, you must set up these features. In this lesson, you learn to set up SQLAgentMail and SQL Mail. Next, you learn to set up linked servers for distributed queries and remote stored procedures. You also learn to create ODBC data sources for ODBC clients. Finally, you learn to configure IIS to permit HTTP access to SQL Server 2000.

---

After this lesson, you will be able to

• Set up SQL Mail and SQLAgentMail
• Set up linked servers
• Create ODBC SQL Server data sources
• Set up a virtual directory in IIS to enable XML access to SQL Server 2000

Estimated lesson time: 45 minutes

---

Setting Up SQL Mail and SQLAgentMail

SQL Server 2000 can connect with Microsoft Exchange Server, Microsoft Windows Mail, or a Post Office Protocol 3 (POP3) server to send and receive messages using two separate services, SQL Mail and SQLAgentMail. Both services require a MAPI client application (such as Microsoft Outlook) on the local SQL Server 2000 computer and a MAPI messaging profile. A MAPI messaging profile requires the use of a domain user account. Generally, SQL Mail and SQLAgentMail use the same domain user account for administrative convenience.

Using SQL Mail

SQL Mail is the mail service of the SQL Server service. The SQL Server service uses the xp_sendmail extended stored procedure to send e-mail from Transact-SQL batches, scripts, stored procedures, and triggers. It establishes a mail session as needed.

 EXEC xp_sendmail '[e-mail]' , @subject = 'Performance Information' , @query = 'SELECT * FROM master.dbo.sysperfinfo' 

The preceding example uses the xp_sendmail extended stored procedure to send an e-mail message to Gloria regarding the current SQL Server 2000 performance. This query could be scheduled to run periodically.

The content of an e-mail message can be any of the following:

• A result set from a query
• A message string
• A Transact-SQL statement or batch for execution
• A page for an electronic pager

The SQL Server service uses the sp_processmail system stored procedure, or the xp_findnextmsg, xp_readmail, and xp_deletemail extended stored procedures to process e-mail sent to the domain user account used by the SQL Server service. This e-mail typically contains a Transact-SQL statement or batch for execution, with the result set being returned by reply e-mail, including an optional cc: list. For example, an administrator can execute a stored procedure by e-mail that obtains the current status of the server and returns the result set by e-mail to the administrator.

Tip

---

Create a regularly scheduled job to periodically process e-mail automatically.

Using SQLAgentMail

SQLAgentMail is the mail service of the SQL Server Agent service. The SQL Server Agent service starts a mail session upon startup (if a mail session is configured in SQL Server Enterprise Manager) and sends e-mail and electronic pager notifications to designated users in response to the triggering of an alert or the success or failure of a job. Jobs and alerts are covered in Chapter 13.

Configuring a Messaging (Mail) Profile

Both SQL Mail and SQLAgentMail require the installation of a MAPI client (such as Microsoft Outlook) on the local SQL Server 2000 computer. Next, you must create a messaging profile (also called a mail profile) for the domain user account used by the SQL Server and SQL Server Agent services. You can create the messaging profile by using the MAPI client or the Mail program in Control Panel. If different domain user accounts are used by each service, you must set up a messaging profile for each domain user account. In general, you must log on to Windows 2000 as the domain uer to configure the messaging profile for that domain user. If the domain user account is not a local administrator, you might need to give this account permission to log on interactively (locally) so that you can create a messaging profile for this user account. The messaging profile contains the connection information used by the MAPI client to connect to the Microsoft Exchange Server, Microsoft Windows Mail, and/or a POP3 server.

Note

---

To verify the installation of a valid messaging profile, log on to Windows 2000 with the domain user account used by the SQL Server and SQL Server Agent services. Start the MAPI client and verify that it can send and receive e-mail.

When you create a profile using either the MAPI client or the Mail program in Control Panel, the default messaging profile name in the Profile Name text box for a domain user is MS Exchange Settings. You can use the xp_get_mapi _default_profile extended stored procedure to determine the default profile name (if any) for the SQL Server service domain user account. You can use the Mail program in Control Panel to add, review, remove, or change the settings for each messaging profile configured for a domain user account. See Figures 12.25 and 12.26.

Setting Up SQL Mail

After configuring and testing the messaging profile for the SQL Server service domain user account, you are ready to set up SQL Mail. Using SQL Server Enterprise Manager, expand the Support Services container for the instance, right-click SQL Mail, and then click Properties. The SQL Mail Configuration dialog box appears as shown in Figure 12.27.

Select the messaging profile name from the Profile Name drop-down list.

Figure 12.25

Accessing mail settings for messaging profiles.

Figure 12.26

Changing mail settings for messaging profiles.

Figure 12.27

Selecting a messaging profile for SQL Mail.

Note

---

If the messaging profile does not appear in SQL Server Enterprise Manager, verify that the SQL Server service is using the domain user account for which the messaging profile was created and that the domain user account has full control permissions on the HKEY_LOCAL_MACHINE\SOFTWARE \Clients\Mail registry key.

Click the Test button to verify whether a mail session can be established. See Figure 12.28.

Figure 12.28

Testing the messaging profile.

You can also set up SQL Mail using the xp_startmail system stored procedure.

Setting Up SQLAgentMail

After configuring and testing the messaging profile for the SQL Server Agent service domain user account, you are ready to set up SQLAgentMail. Using SQL Server Enterprise Manager, expand the Management container for the instance, right-click SQL Server Agent, and then click Properties. The SQL Server Agent Properties dialog box appears with the General tab selected, as shown in Figure 12.29.

In the Mail Session group box, select the messaging profile from the Mail Profile drop-down list. Click the Test button to verify whether a mail session can be established. By default, messages sent by the SQL Server Agent service are not saved in the Sent Items folder of the MAPI client. Using SQLAgentMail for jobs and alerts is covered in Chapter 13.

Figure 12.29

Selecting the messaging profile for the SQLAgentMail.

Setting Up Linked Servers

SQL Server 2000 can connect to linked servers. The primary use of a linked server configuration is the execution of distributed queries, joining information from multiple databases on multiple servers (such as SQL Server 2000, SQL Server 7.0, Oracle, and Access). You can set up a linked server configuration to any OLE DB data source by using SQL Server Enterprise Manager. Although the OLE DB data source is generally another database, it can also be a text file, a spreadsheet, or the results of full-text content searches. See Figure 12.30.

The configuration information specifies the OLE DB data source and the OLE DB provider used to communicate with the remote data source. It also specifies the security context for the connection to the linked server. Although the SQL Server service on the local instance is making the connection, it is making it on behalf of a user with certain permissions. You must configure login mappings between linked servers specifying the security context between each SQL Server instance to ensure that the user can only access information he or she has permission to access.

Figure 12.30

Basics of a linked server configuration.

Note

---

You can also set up a remote server configuration to another SQL Server instance. However, remote server functionality is provided primarily for backward compatibility. Linked servers provide all the functionality of remote servers, plus additional functionality.

Setting Up Linked Servers Using SQL Server Enterprise Manager

Using SQL Server Enterprise Manager, expand the Security container for the instance, right-click Linked Servers, and then click New Linked Server to display the Linked Server Properties - New Linked Server dialog box. See Figure 12.31.

In the General tab, you enter the name of the linked server in the Linked Server text box. If, in the Server Type group box, you click the SQL Server option button to create a link to a named instance, the name you specify in the Linked Server text box must be the network and instance name of the SQL Server instance. You can select from a list of possible OLE DB providers in the Provider Name drop-down list. These providers manage the access to the specified linked server. Notice that this includes an OLE DB provider for Microsoft Directory Services for querying Active Directory. If you specify any data source other than a SQL Server instance by clicking the Other Data Source option button in the Server Type group box, you must provide additional information specific to that data source (such as product name, data source, and provider string).

Figure 12.31

Specifying a Provider Name when creating a new linked server.

Next, click the Security tab to map local logins to remote logins. See Figure 12.32.

Figure 12.32

Mapping local logins to remote logins for a linked server.

On this dialog box, define how local users will connect to the remote data source. You select logins from the local server and define how they will connect to the remote server under Local Server Login To Remote Server Login Mappings. You can choose to have the SQL Server service impersonate the logged-in user or map the logged-in user to a SQL Server login on the remote server. You must provide the password for the remote login account.

Note

---

For the SQL Server service to impersonate a Windows user between servers, security account delegation must be enabled. See Chapter 10.

Next, determine how the SQL Server service should handle connection attempts made by users with no specific security mapping. You can have the SQL Server service deny all such attempts. You can also have the connection attempt made without a user security context (using the SQL Server service security context), using the login's current security context or using a specified default SQL Server login account.

Finally, in the Server Options tab, you can configure advanced connection parameters (such as a different collation or a specific query timeout value). See Figure 12.33.

Figure 12.33

Configuring advanced connection parameters for a linked server.

Setting Up Linked Servers Using Transact-SQL

You can also set up a linked server configuration using the sp_addlinkedserver system stored procedure.

 sp_addlinkedserver 'SelfPacedCPU\MyNamedInstance' 

The preceding example creates a linked server configuration between the current SQL Server 2000 instance and MyNamedInstance on SelfPacedCPU. No security mappings are created. By default, this linked server configuration specifies that all connections use the security context of the logged-in user.

Note

---

SQL Server 2000 Books Online has excellent examples for connecting to non-SQL Server data sources using the sp_addlinkedserver system stored procedure.

Use the sp_addlinkedsrvlogin system stored procedure to create or update mappings between logins on the local SQL Server instance and remote logins on the linked server.

 sp_addlinkedsrvlogin 'SelfPacedCPU\MyNamedInstance' , 'FALSE' , 'SelfPacedSQL\Bill' , 'sa' , 'sa_password' 

The preceding example adds a mapping on the local instance between the Windows user Bill in the SelfPacedSQL domain and the sa login on the linked server.

Use the sp_linkedservers system stored procedure to obtain a list of linked servers defined for the current instance.

Practice: Setting Up and Testing a Linked Server Configuration

In this practice you use the SQL Server Enterprise Manager to set up a linked server configuration.

To set up and test a linked server configuration

1. Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
2. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
3. In the console tree, expand the Microsoft SQL Servers container, expand the SQL Server Group container, expand the default instance, and then expand Security.
4. Right-click Linked Servers and then click New Linked Server.

   The Linked Server Properties - New Linked Server dialog box appears, with the General tab selected.

5. In the Linked Server text box, type SelfPacedCPU\MyNamedInstance.
6. Under Server Type, click the SQL Server option button and then click the Security tab.
7. In the For A Login Not Defined In The List Above, Connections Will group box, click the Be Made Using The Login's Current Security Context option button and then click OK.
8. On the Tools menu, click SQL Query Analyzer.

   SQL Query Analyzer appears.

9. In the query pane, type SELECT * FROM [SelfPacedCPU \MyNamedInstance].Northwind.dbo.Customers and then click the Execute Query button from the toolbar.

   Notice that the contents of the Customer table are displayed in the results pane.

10. On the File menu, click Connect.
11. The Connect To SQL Server dialog box appears.
12. Click the SQL Server Authentication option button.
13. In the Login Name text box, type Joe.
14. In the Password text box, type password and then click OK.

    SQL Query Analyzer appears.

15. In the query pane, type SELECT * FROM [SelfPacedCPU \MyNamedInstance].Northwind.dbo.Customers and then click the Execute Query button from the toolbar.

    Notice that the contents of the Customer table are not displayed in the results pane. There are several reasons why this failed. First, MyNamedInstance does not permit SQL Server logins. Second, Joe does not have a SQL Server login within MyNamedInstance.

16. Switch to SQL Server Enterprise Manager.
17. In the console tree, right-click SelfPacedCPU\MyNamedInstance and then click Properties.

    The SQL Server Properties (Configure) - SelfPacedCPU\MyNamedInstance dialog box appears.

18. Click the Security tab, click the SQL Server And Windows option button, and then click OK.
19. Click the Yes button to restart the SQL Server service.
20. Switch to SQL Query Analyzer.
21. On the toolbar, click the Execute Query button to re-execute the query for the user Joe.
22. Notice that the error message has changed. The reason the distributed query failed is that the local login Joe is mapped to a login Joe on MyNamedInstance (which does not yet exist).
23. Switch to SQL Server Enterprise Manager.
24. Expand the SelfPacedCPU\MyNamedInstance container and then expand Security.
25. Right-click Logins and then click New Login.

    The SQL Server Login Properties - New Login dialog box appears.

26. In the Name text box, type Joe.
27. Click the SQL Server Authentication option button.
28. In the Password text box, type password and then click OK.

    The Confirm Password dialog box appears.

29. In the Confirm New Password text box, type password and then click OK.
30. Switch to SQL Query Analyzer.
31. On the toolbar, click the Execute Query button to re-execute the query for the user Joe.

    Notice that the contents of the Customer table are displayed in the results pane. Joe is able to connect to the Northwind database because the guest user account is present in the Northwind database and has SELECT permissions on the Customer table.

32. Switch to SQL Server Enterprise Manager.
33. Expand the SelfPacedCPU container, expand the Security container, and then click Linked Servers.
34. In the details pane, double-click SelfPacedCPU\MyNamedInstance.

    The Linked Server Properties - SelfPacedCPU\MyNamedInstance dialog box appears.

35. Click the Security tab.
36. In the first row under the Local Login column, click the cell's drop-down combo box and then select Ana.
37. In the Remote User cell, type Joe.
38. In the Remote Password cell, type password.
39. In the second row under the Local Login column, type Joe into the cell and then select the Impersonate check box.
40. Under For A Login Not Defined In The List Above, Connections Will, click the Not Be Made option button and then click OK.

    Only Joe and Ana will be permitted to use this linked server configuration.

41. Switch to SQL Query Analyzer.
42. On the toolbar, click the Execute Query button to re-execute the query for the user Joe.

    Notice that the contents of the Customer table are displayed in the results pane.

43. Switch to your administrator connection in SQL Query Analyzer.
44. On the toolbar, click the Execute Query button to re-execute the query.

    Notice that your administrator account can no longer use the linked server connection because no mapping exists.

45. On the File menu, click Connect.
46. The Connect To SQL Server dialog box appears.
47. Click the SQL Server Authentication option button.
48. In the Login Name text box, type Ana.
49. In the Password text box, type password and then click OK.

    SQL Query Analyzer appears.

50. In the query pane, type SELECT * FROM [SelfPacedCPU \MyNamedInstance].Northwind.dbo.Customers and then click the Execute Query button on the toolbar.

    Notice that the contents of the Customer table are displayed in the results pane. Ana is using the linked server configuration and accessing data within MyNamedInstance using the permissions granted to Joe.

51. Close SQL Server Enterprise Manager and SQL Query Analyzer. Do not save any changes to queries.

Creating an ODBC SQL Server Data Source

Traditionally, clients connect to a SQL Server 2000 instance using either OLE DB or ODBC. OLE DB clients provide the necessary connection information through the Microsoft OLE DB Provider for SQL Server. ODBC clients can provide the necessary connection information through the use of the Microsoft OLE DB Provider for ODBC, or they can connect to an ODBC SQL Server data source name (DSN) to make a connection.

A DSN is a stored definition recording the ODBC driver, connection information, and driver-specific information. The ODBC Data Source Administrator utility is used to create DSNs. To create a DSN with Windows 2000, open the Data Sources (ODBC) utility from the Administrative Tools folder in Control Panel. See Figure 12.34.

Figure 12.34

Creating a DSN.

Notice that you can create User DSNs, System DSNs, and File DSNs. User DSNs are specific to the user that created them and local to the computer on which the user created them. System DSNs are available to all login clients and local to the computer on which the user created them. File DSNs are stored in a file. File DSNs can be shared among many users on the network, and need not be a local file on the client computer. After you select the type of DSN (by clicking the appropriate tab) and driver for the new DSN (by clicking the Add button), the Data Source Wizard appears. Figure 12.35 illustrates creating a new ODBC data source using the SQL Server driver.

Figure 12.35

Creating an ODBC data source for a DSN.

In the first page of the wizard, enter a name for the DSN in the Name text box, a description for it in the Description text box, and select the SQL Server instance for which this DSN is storing connection information in the Server drop-down combo box. Next, in the second page, you specify connection information. See Figure 12.36.

Figure 12.36

Providing connection information for a DSN.

You can specify that the SQL Server ODBC driver request a trusted connection or use the SQL Server login and password supplied by the user. You can also specify custom client network library parameters for this connection by clicking the Client Configuration button. Next, you can connect to SQL Server to obtain initial settings for the following screens by selecting the Connect To SQL Server To Obtain Default Settings For The Additional Configuration Options check box. Standard defaults are used if you choose not to connect.

Next, in the third page, you can specify a default database by selecting the Change The Default Database To check box or attach a database when this DSN is used by selecting the Attach Database Filename check box. In addition, you can specify ANSI settings for connections using this DSN by selecting the corresponding check box. Finally, if a clustered environment is detected, you can specify use of the failover SQL Server 2000 instance by selecting the Use The Failover SQL Server If The Primary SQL Server Is Not Available check box. See Figure 12.37.

Figure 12.37

Specifying database settings for a DSN.

Finally, in the fourth page, you can change the language of SQL Server system messages in the corresponding drop-down list, enable encryption, specify character set translation, and choose regional settings by selecting the appropriate check boxes. You can also enable logging of long-running queries (defining what is considered long-running in milliseconds) by selecting the Save Long Running Queries To The Log File check box, and ODBC driver statistics by selecting the Log ODBC Driver Statistics To The Log File check box. See Figure 12.38.

After you have completed the information in the wizard, the ODBC Microsoft SQL Server Setup dialog box appears. You can review your configuration in the information box and test the data source by clicking the Test Data Source button before actually creating the DSN. Figure 12.39 illustrates a successful test for the ODBC data source.

Figure 12.38

Changing regional, language, and logging settings for a DSN.

Figure 12.39

Reviewing and testing a DSN.

Configuring SQL Server XML Support in IIS

One of the most exciting new features of SQL Server 2000 is XML support. You can make a SQL Server 2000 instance an XML-enabled database server. To accomplish this, you configure an IIS virtual directory linked to SQL Server 2000 support. This enables SQL Server 2000 to provide for:

• HTTP access
• XML-Data schemas and XPath queries
• Retrieval and writing of XML data
• The ability to set XML documents as command text and to return result sets as a stream

The details about implementing XML from the programming perspective are beyond the scope of this book. However, the IIS Virtual Directory Management For SQL Server utility makes the task of creating a virtual directory within IIS easy for database administrators. You select this utility by clicking Configure SQL XML Support In IIS in the Microsoft SQL Server program group. Figure 12.40 illustrates the IIS Virtual Directory Management For SQL Server console.

Figure 12.40

IIS Virtual Directory Management for SQL Server console.

In the console tree, expand your server, right-click Default Web Site, point to New, and then click Virtual Directory to begin. The New Virtual Directory Properties dialog box appears. In the General tab, specify a user-friendly name for the virtual directory in the Virtual Directory Name group box. In the Local Path group box, specify a path on the local computer to the files that will be made accessible through this virtual directory. Generally this will be a subfolder under C:\Inetpub and will contain XML queries, templates, and style sheets. See Figure 12.41.

Next, in the Security tab, define the authentication method users will use to obtain access to SQL Server 2000. You can choose to have all users authenticate using either a dedicated SQL Server login or the IIS local user account (for which you can configure permissions). Generally, use this method for guest access with limited permissions. You can also choose to require that each user provide individual authentication (either Windows or SQL Server authentication). See Figure 12.42.

Next, in the SQL Server group box in the Data Source tab, specify the SQL Server 2000 instance whose data is being published through this virtual directory. In addition, you define the default database by browsing the instance to retrieve the database from a list of databases in the Database group box (using the credentials provided on the previous property sheet). See Figure 12.43.

Figure 12.41

Specifying a name and path for the virtual directory.

Figure 12.42

Selecting the authentication method for connecting to the virtual directory.

Figure 12.43

Specifying the server and database published using the virtual directory.

Next, in the Settings tab, specify the type of access to the SQL Server 2000 instance you want to provide by selecting the appropriate check boxes. URL queries allow a user to submit any query, whereas template queries limit the queries that can be submitted through this virtual directory. XPath queries over SQL views allow more control over the structure and appearance of the document returned. XPath queries can be embedded in a URL query or a template. You can also choose to allow user input for values to be passed to a POST query and choose a limit for the size of the user input (in kilobytes). See Figure 12.44.

Next, in the Virtual Names tab, specify any virtual names you want to create by clicking the New button. For example, you might create a virtual name linked to a physical path that contains XML templates or views. See Figure 12.45.

Finally, in the Advanced tab, you can specify a different location for the Sqlisapi.dll in the ISAPI Location group box, provide additional user settings in the Additional User Settings group box, or disable caching in the Caching Options group box. Generally, leave the defaults in place. See Figure 12.46.

Figure 12.44

Choosing settings for access to the SQL Server database.

Figure 12.45

Creating a virtual name for the published data.

Figure 12.46

Changing default advanced settings for the virtual directory.

Practice: Creating an IIS Virtual Directory

In this practice you create an IIS virtual directory using the IIS Virtual Directory Management For SQL Server utility.

To create an IIS virtual directory

1. Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
2. Using Windows Explorer, create a new folder called Northwind under C:\Inetpub.
3. Click Start, point to Programs, point to Microsoft SQL Server, and then click Configure SQL XML Support In IIS.

   The IIS Virtual Directory Management For SQL Server console appears displaying a connection to your server.

4. In the console tree, expand your server, right-click Default Web Site, point to New, and then click Virtual Directory.

   The New Virtual Directory Properties dialog box appears with the General tab selected.

5. In the Virtual Directory Name text box, type Northwind.
6. In the Local Path text box, type C:\InetPub\Northwind.
7. Click the Security tab.
8. In the User Name text box, type sa.
9. Click the Data Source tab.

   A Confirm Password dialog box appears.

10. Click OK to confirm no password for the sa account.
11. In the SQL Server text box, select SelfPacedCPU by clicking the ellipsis button.
12. In the Database text box, select Northwind from the Database drop-down combo box.
13. Click the Settings tab.
14. Select the Allow URL Queries check box and confirm that the Allow Template Queries check box is selected.
15. Click the Virtual Names tab.
16. Click the New button.

    The Virtual Name Configuration dialog box appears.

17. In the Virtual Name text box, type Queries.
18. In the Type drop-down list, select Template.
19. In the Path text box, type C:\Inetpub\Northwind and then click the Save button.
20. Click OK to create the new virtual directory.
21. Using Notepad, open the XML Query.txt file in the C:\SelfPacedSQL\CH_12 folder.
22. Copy the HTTP query.
23. Open Internet Explorer.
24. Paste the query to the Address drop-down combo box and then click the Go button.

    The contact name and phone number from the Customers table in the Northwind database is displayed.

25. Close Windows Explorer, Notepad, Internet Explorer, and IIS Virtual Directory Management For SQL Server console.

Note

---

The SQL Server 2000 compact disc contains additional XML queries and exercises. They are located in the DevTools\Samples\Xml folder in a self-extracting ZIP file.

Lesson Summary

Setting up SQL Mail enables the SQL Server service to use e-mail to respond to queries and notify users of the results of Transact-SQL scripts. SQLAgentMail enables the SQL Server Agent service to provide notification regarding alerts and the success or failure of jobs. Setting up linked servers allows users to access data on remote databases without providing connection information each time they connect. ODBC applications may require DSNs, which a database administrator might have to configure. Finally, to create an XML-enabled database server, IIS must be configured with a virtual directory linked to a SQL Server 2000 database and specifying connection parameters and permitted certain XML access types.