Connect to a SQL Data Source

In the designer, select the Data Source icon on the Detail section of the report or click the gray area around the design surface and select the Edit Data Source link in the Properties pane.
In the Report Data Source dialog that appears, select the SQL tab to connect to a SQL data source.
Click the Build button next to the Connection String section to open the Data Link Properties dialog box.
In the Providers tab, specify the OLEDB provider you want to use to connect to the data source. For example, select Microsoft OLE DB Provider for SQL Server and click the Next button to move to the Connection tab.
The fields in the Connection tab depends on the chosen OLEDB provider. For more information, see the Configuration Settings for SQL Data Source.
In the Connection tab, enter the server name that you want to connect.
To specify the authentication method for the data source connection, select the Use Windows NT Integrated security or Use a specific username and password option.
If you choose the Use a specific username and password 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 Select the database on the server field and choose the database you want to use.
You can verify the data source connection through the Test Connection button.
Then, click the OK button to close the Data Link Properties dialog box.
The Connection String section displays the generated connection string as shown below:
```
data source=10.64.1.85\sql_2k8r2;persist security info=False;initial catalog=auditing;user id=sa;password=**********;
```
In the Query field on the SQL tab, enter a SQL query to fetch the required data from the connected database. For example,
```
SELECT * FROM Location
```
You can also click the Query Designer button to access the Visual Query Designer for creating SQL queries. For information on how to create a query using the interactive query designer, visit the Visual Query Designer article.
Click the OK button to save the changes.

Configuration Settings for SQL Data Source

The SQL Data Provider provides the following configuration settings in the Report Data Source dialog.

The Provider tab describes the type of OLEDB provider you want to use for connecting to a data source. You can choose from the following -

Microsoft JET 4.0 OLE DB Provider
Microsoft Office 12.0 Access Database Engine OLE DB Provider
Microsoft Office 16.0 Access Database Engine OLE DB Provider
Microsoft OLE DB Provider for ODBC Drivers
Microsoft OLE DB Provider for Oracle
Microsoft OLE DB Provider for Search
Microsoft OLE DB Provider for SQL Server
Microsoft OLE DB Simple Provider
MSDataShape
OLE DB Provider for Microsoft Directory Services
SQL Server Native Client 11.0

The Connection tab specifies the properties required for accessing the data. The properties in the tab depend upon the OLEDB provider you want to use.

For Microsoft JET 4.0 OLE DB Provider and Microsoft OLE DB Provider, following properties are available in the Connection tab -

Setting	Description
Select or enter a database name	Enter a server or a file name along with its location.
Enter information to log on to the database	Specify the username and password required to access the database.

For Microsoft Office 12.0 Access Database Engine OLE DB Provider, Microsoft Office 12.0 Access Database Engine OLE DB Provider, Microsoft OLE DB Provider for ODBC Drivers, Microsoft OLE DB Simple Provider, MSDataShape, and OLE DB Provider for Microsoft Directory Services, following properties are available in the Connection tab -

Setting	Description
Enter the data source and/or location of the data	Enter a server or a file name along with its location.
Enter information to 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.
Enter initial catalog to use	Specify the name of the database you want to connect.

For Microsoft OLE DB Provider for SQL Server and SQL Server Native Client 11.0, following properties are available in the Connection tab -

Setting	Description
Select or enter server name	Select your server from the drop down list.
Enter information to log on to the 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.
Select the database on the server	Select a database from the server or attach a database file.

The Advanced tab gives access to the initialization properties required by the chosen OLEDB provider.

Setting	Description
Impersonation Level	Set the impersonation level to any of the following options - Anonymous, Delegate, Identify, and Impersonate.
Protection Level	From the drop down, choose Call, Connect, None, Pkt, Pkt Integrity, or Pkt Privacy.
Connect Timeout	Specify the amount of time (in seconds) for a connection to establish.
Access Permissions	Specify the access permissions on the database, such as Read, ReadWrite, Share Deny None, Share Deny Read, and Share Deny Write.

Note: In case of SQL Server Native Client 11.0 provider, only the Connect Timeout setting is available.

The All tab lets you view and edit the initialization properties available for the selected OLEDB provider. These properties can differ based on the OLEDB provider you want to use.