The Microsoft SQL Client Data Provider supports following options under the Connection section in Report Data Source dialog.
Connection Properties
The Connection Properties tab gives access to properties specific to the following data types.
- Server name: This field requires you to enter a server name.
- Log on to server: Through this field, you can 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: Through this field, you can select whether to enter a database name or attach a database file.
Connection String
Sample Connection string
data source=in-data-sql\sql_2012;initial catalog=ADventurewORKS2012;user id=user1;password=password@123
Advanced Settings
The Advanced Settings tab gives access to properties specific to each data type.
With the SQL data type, the Advanced Settings tab gives access to the following properties:
- 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.