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.
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.
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 'Gloria@SelfPacedSQL.msft' , @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:
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.
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.
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.
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.
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.
Accessing mail settings for messaging profiles.
Changing mail settings for messaging profiles.
Selecting a messaging profile for SQL Mail.
Click the Test button to verify whether a mail session can be established. See Figure 12.28.
Testing the messaging profile.
You can also set up SQL Mail using the xp_startmail system stored procedure.
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.
Selecting the messaging profile for the SQLAgentMail.
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.
Basics of a linked server configuration.
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).
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.
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.
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.
Configuring advanced connection parameters for a linked server.
You can also set up a linked server configuration using the sp_addlinkedserver system stored procedure.
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.
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.
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
The Linked Server Properties - New Linked Server dialog box appears, with the General tab selected.
SQL Query Analyzer appears.
Notice that the contents of the Customer table are displayed in the results pane.
SQL Query Analyzer appears.
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.
The SQL Server Properties (Configure) - SelfPacedCPU\MyNamedInstance dialog box appears.
The SQL Server Login Properties - New Login dialog box appears.
The Confirm Password dialog box appears.
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.
The Linked Server Properties - SelfPacedCPU\MyNamedInstance dialog box appears.
Only Joe and Ana will be permitted to use this linked server configuration.
Notice that the contents of the Customer table are displayed in the results pane.
Notice that your administrator account can no longer use the linked server connection because no mapping exists.
SQL Query Analyzer appears.
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.
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.
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.
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.
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.
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.
Changing regional, language, and logging settings for a DSN.
Reviewing and testing a DSN.
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:
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.
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.
Specifying a name and path for the virtual directory.
Selecting the authentication method for connecting to the virtual directory.
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.
Choosing settings for access to the SQL Server database.
Creating a virtual name for the published data.
Changing default advanced settings for the 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
The IIS Virtual Directory Management For SQL Server console appears displaying a connection to your server.
The New Virtual Directory Properties dialog box appears with the General tab selected.
A Confirm Password dialog box appears.
The Virtual Name Configuration dialog box appears.
The contact name and phone number from the Customers table in the Northwind database is displayed.
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.