Connect to a database by saving the user name and password

Connect to a database by saving the user name and password

When you use Connect to a database by saving the user name and password, Office SharePoint Designer 2007 generates a SQL-based connection string that stores the user name and password in the data source connection (a process known as SQL authentication). If your site is located on a server running Windows SharePoint Services 3.0, this is the preferred option for generating a connection string. You can also create a database connection by using a custom connection string. For more information, see the later section Connect to a database by using a custom connection string.

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 an external Microsoft SQL Server 2000 database or an external Microsoft SQL Server 2005 database, clickMicrosoft .NET Framework Data Provider for SQL Server.
   • If you are connecting to an external database that is OLE DB compatible, including versions of SQL other than Microsoft SQL such as MySQL, click Microsoft .NET Framework Data Provider for OLE DB.
6. Under Authentication, click Save this username and password in the data connection.
7. In the User name box, type your user name.
8. In the Password box, type your password.
9. Click Next.
   
    IMPORTANT   Because SQL authentication saves the user name and password as text in the data connection, any user with permission to open the site in Office SharePoint Designer 2007 can view these credentials. A security warning informs you that the user name and password are saved as text in the data connection and that other site members can access this data source. If you want other site members to access the database by using the same user name and password, click OK. If you do not want other site members to access the database by using the same user name and password, click Cancel. If your site is located on a server running Office SharePoint Server 2007, you can talk to your server administrator about configuring Single Sign-On, and then see the later section Connect to a database by using Single Sign-On authentication.
10. 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.
11. 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 Librarytask pane.

 NOTE   If you are trying to connect to an external server running SQL Server, make sure that SQL Server authentication is enabled on that server.