Add a database as a data source for SHAREPOINT

With Microsoft Office SharePoint Designer 2007, you can connect to a variety of databases, including Microsoft SQL Server, Oracle, and other databases that are accessible by using OLE DB or ODBC protocols. By default, your Microsoft SharePoint site does not contain any database connections. To access a database, someone who has sufficient permissions must create the connection.

This article shows you how to connect to a database by using the Data Source Library. It also shows you how to create custom Structured Query Language (SQL) commands and how to modify the data source properties of an existing database connection in the Data Source Library.

Connect to a database by using Single Sign-On authentication

If your site is located on a server running Office SharePoint Server 2007, this is the preferred option for generating a connection string. The Use Single Sign-On authentication option instructs the server to supply the user name and password for accessing the database. This option can be used when your site is part of a portal site that was created by using Office SharePoint Server 2007 and the administrator has enabled and configured Single Sign-On. With Single Sign-On, you can connect to an external database, even if it doesn't reside on the same server as your SharePoint site.

1. If the Data Source Library task pane is not visible, on the Task Panesmenu, click Data Source Library.
2. In the Data Source Library task pane, under Database Connections, clickConnect to a database.
   
    NOTE   If the Database Connections heading is collapsed, click the plus sign (+) to expand it.
3. In the Data Source Properties dialog box, on the Source tab, clickConfigure Database Connection.
4. In the Configure Database Connection dialog box, under Server information, in the Server Name box, type the name of the server where your database resides.
5. In the Provider Name box, do one of the following:
   
   • If you are connecting to a database stored on a computer running Microsoft SQL Server, such as Microsoft SQL Server 2000 and Microsoft SQL Server 2005, click Microsoft .NET Framework Data Provider for SQL Server.
   • If you are connecting to a database that uses OLE DB, including versions of SQL other than Microsoft SQL such as MySQL, clickMicrosoft .NET Framework Data Provider for OLE DB.
6. Under Authentication, click Use Single Sign-On authentication, and then click Settings.
7. In the Single Sign-On Settings dialog box, do the following:
   
   • In the Application Name box, type the application name for your database.
   • In the Application field to use as the user name box, type the field in which your user name is stored.
   • In the Application field to use as the password box, type the field in which your password is stored.
    NOTE   If you do not have this information, ask your server administrator.
8. Click Next.
9. In the Database list, click the database that you want to use as a data source, and then do one of the following:
• Click Select a table or view, click the table or saved view that you want from the list, and then click Finish.
  
  By using this option, you create a default query that selects all records in the table or view. However, after you click Finish, you can modify the default query by clicking Fields, Filter, and Sort in the Data Source Propertiesdialog box. For more information, see the article Create a data source query.
• Click Specify custom Select, Update, Insert, and Delete commands using SQL or stored procedures, and then click Finish.
  
   NOTE   This option is available only if your administrator has turned on theEnable Update Query Support option.
  By using this option, you can create custom SQL commands. When you click Finish, the Edit Custom SQL Commands dialog box opens. In this dialog box, you can create commands and add parameters. After you create a custom SQL command, you can edit the query by clicking Edit Custom Query in the Data Source Properties dialog box. For more information, seeCreate custom SQL commands later in this article.
10. In the Data Source Properties dialog box, click the General tab, type a name for the data source, and then click OK.
    
    The new database connection now appears in the Data Source Library.

 NOTE   If you cannot use SQL Server authentication on the server that is running SQL Server, make sure that Single Sign-On authentication is enabled for the server where your SharePoint site resides.