Connect to a database by using a custom connection string

A connection string provides the information that an application or provider requires to communicate with a data source. A connection string usually supplies the location of the database server, the particular database to use, and the authentication information. With a custom connection string, you can specify the connection string that is used to connect to the database.

1. If the Data Source Library task pane is not visible, on the Task Panes menu, click Data Source Library.
2. In the Data Source Library task pane, click Connect 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, click Configure Database Connection.
4. In the Configure Database Connection dialog box, select the Use Custom connection string check box, and then click Edit.
5. In the Edit Connection String dialog box, in the Provider Name list, click the data provider that you want to use.
6. In the Connection String text box, type your connection string.
   An example of an OLE DB connection string:

   Provider=myProvider;Data Source=myPath;Initial Catalog=catalogName;
   User Id=username;Password=password;

   An example of an ODBC connection string:

   Driver={myProvider};Server=myPath;Database=myDatabase;
   Uid=username;Pwd=password;

   An example of an Oracle connection string:

   Data Source=myDatabase;User Id=username;
   Password=password;Integrated Security=no;

7. Click Next.
8. 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 connection to the table or view. However, after you click Finish, you can specify a query by clicking Fields,Filter, and Sort in the Data Source Properties dialog 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 or edit 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.
9. 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.

  NOTES  

• If you are trying to connect to an external server running SQL Server, make sure that SQL Server authentication is enabled on that server.
• 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.