Embedded Data Source in SSRS

In SQL Server Reporting Services, Data Source is a connection settings which is used to connect with underlying Databases or Data Warehouses. Data source is a combination of Provider name, Server instance Name, Database Name and Database credentials. SQL Server Reporting Services supports two types of data sources: Shared Data Source and Embedded Data Sources. In this article we will show you, Steps involved in creating Embedded Data Source in SSRS 2014.

Embedded Data Source: If you want to use the data source in single reports then there is no point in creating shared data source. In these situations, we create SSRS Embedded data Source

Creating Embedded Data Source in SSRS 2014

After creating the New SQL Reporting Services project (SSRS project), the Report Data looks like below.

To create Embedded data source in SSRS, Right click on the Data Sources folder present in the Report data and select the Add Data Source option from the context menu

Once you click on that option, it will open Data Source Properties window to configure the SSRS Data Source. Following are the properties inside the Data Source Properties window

• Name: Please specify valid unique name for the data source you want to create.
• Type: Please select the connection type from the drop down list. SSRS Data Source supports multiple connection types. For instance, if your data source is SQL Server then select Microsoft SQL Server or OLE DB. If your data source is SQL Server Analysis Service then select Microsoft SQL Server Analysis Services.
• Connection Strings: If you know, How to write the connection string then write down here. If not, click on the Edit button to specify the Data Source (Provider), Server Instance Name, Login credentials and Database Name.

TIP: If you want to use the already created Shared data source then, Please select Use Shared data source reference radio button and select the existing shared data source from drop down list.

Click on the Edit button from the above screenshot will open another window to configure the Connection Properties. Here we have to specify the Data Source (Provider), Server Name (or Instance Name), Login credentials and Database Name.

In this example,

• Data Source: We are retrieving data from SQL Server so, We selected the Microsoft SQL Server (SqlClient). You can change this option as per your requirement.
• Server name: Please specify the SQL Server name. For now, we are using our local host name.
• Log on to the Server: Please specify, How you want to log into SQL Server. For now, we are using windows authentication.

From the above screenshot, you can observe that we are using selecting the [AdventureWorksDW2014] database from the list.

NOTE: Here we are working with local database so we are using windows credentials but in real-time, you have to select the SQL Server authentication and provide the credentials given by the database admin people or your team leader.

Click on the Test Connection button provided below to check whether the connection is successful or not.

Click Ok to finish configuring the connection properties.

Credentials Tab:

• Use Windows Authentication: It will use the current user windows credentials to connect to the data source. For now, We are using this option
• Use this User name and Password: Please specify the user name and password to log into data source. Report will use this user name and password to connect to the data source
• Prompt for credentials: When the report is connection to data source, it will prompt the given text (asking for credentials).
• Do not use credentials: Report will not use any credentials to connect with data source.

Click Ok to finish creating the Embedded Data Source in SSRS. Let us see in the Report Data tab

Thank You for Visiting our Blog