Microsoft SQL Client Provider
In This Topic
This article explains connecting a Page or an RDL report to a Microsoft SQL Client data source.
Connect to a Microsoft SQL Client Data Source
- In the Report Explorer, right-click the Data Sources node and select the Add Data Source option or click the Add button and select the Data Source option.
- In the Report Data Source dialog that appears, select the General page and enter the data source name in the Name field. By default, the data source name is set to DataSource1. This name appears as a child node to the Data Sources node in the Report Explorer.
- Under the Type field, select Microsoft Sql Client Provider.
- In the Connection Properties tab, enter the server name that you want to connect.
- To specify the authentication method for the data source connection, select the Use Windows Authentication or Server authentication option.
If you choose the Server authentication option, enter the user name and password in the respective fields.Note: The User name and Password fields are disabled in case of Windows authentication. - Click the dropdown next to the Database field and choose the database you want to use.
The Connection String tab displays the generated connection string as shown below.
data source=10.64.1.107\sql_2k8r2;initial catalog=auditing;user id=sa;password=**********;
For more information, see the Configuration Properties for Microsoft SQL Client Data Source section. - Verify the generated connection string by clicking the Validate DataSource icon
. - Click the OK button to save the changes.
Configuration Settings for Microsoft SQL Client Data Source
The Microsoft SQL Client Data Provider provides the following configuration settings under the Connection section in the Report Data Source dialog. Based on the defined configuration settings, the connection string is generated in the Connection String tab.
The Connection Properties tab describes the configuration settings for the Microsoft SQL Client data provider. It includes details related to server name, type of authentication method, and database name.
| Setting | Description |
|---|---|
| Server name | Enter a server name |
| Log on to server | Select whether to use Windows authentication or server authentication which requires a user name and password. Below this field you can also check the Save my password option for future reference. |
| Connect to a database | Select whether to enter a database name or attach a database file. |
The Advanced Settings tab gives access to the following properties.
| Setting | Description |
|---|---|
| Application Name | Indicates the client application name. |
| Auto Translate | Indicates whether the OEM/ANSI characters are converted. You can set this property to True or False. By default, the value is set to True. If True then SQLOLEDB performs the OEM/ANSI character conversion when multi-byte character strings are retrieved from, or sent to, the SQL Server. |
| Current Language | Indicates the SQL Server language name. It also identifies the language used for system message selection and formatting. The language must be installed on the SQL Server, otherwise opening the connection will fail. |
| Network Address | Indicates the network address of the SQL Server, specified by the Location property. |
| Network Library | Indicates the name of the network library (DLL) used to communicate with the SQL Server. The name should not contain the path or the .dll file name extension. The default name is provided by the SQL Server client configuration. |
| Packet Size | Indicates a network packet size in bytes. The Packet Size property value must be between 512 and 32767. By default, the SQLOLEDB network packet size is 4096. |
| Trusted Connection | Indicates the user authentication mode. You can set this property to Yes or No. By default, the property value is set to No. If Yes, the SQLOLEDB uses the Microsoft Windows NT Authentication Mode to authorize user access to the SQL Server database, specified by the Location and Datasource property values. If this property is set to No, then the SQLOLEDB uses the Mixed mode to authorize user access to the SQL Server database. The SQL Server login and password are specified in the User Id and Password properties. |
| Use Procedure for Prepare | Determines whether the SQL Server creates temporary stored procedures when Commands are prepared by the Prepared property. |
| Workstation ID | Denotes a string that identifies the workstation. |