An Embedded data source, also known as a report-specific data source is a data connection that is saved in the report definition, and embedded data source connection information can be used only by the report in which it is embedded, and their scope is limited to the report in which you are creating them.
You can define and manage embedded data sources, using the Data Source Properties dialog box.
Create Embedded data source
Lets see the how to create an Embedded data source in SSRS report.
Either you can take a new project solution, or create a new report in existing report solution.
Here we are adding a new report in existing project solution as shown below.
First, right click on Reports folder then navigate to Add and select New Item.. .
After that you will see, Add New Item window opens.
Now select Report and provide a name for your report, here we named as RptDemo.
After that click on Add button.
Now you will see, a blank report Rptdemo is created inside a Reports folder, and you can see a Design Surface in center and Report Data pane on the left side.
[Also Read.. Create Shared Data Source in SSRS]
Now we will, add an Embedded Data Source for this report.
Lets go to the Data Sources folder, Right Click and click on Add Data Source…
After that, a Data Source Properties window opens, as shown below.
In General tab, you need to specified a Data Source name, type and connection properties, which are as follows:
- Name: you need to provide a name for the data source that you want to create.
- Embedded connection: you need to select the Embedded radio button, to enable the embedded data source type and connection string configuration.
Type: specify a connection type from the drop down list. Here our data source in Microsoft SQL Server.
Connection Strings: specify a connection string, that you can either write, paste or click on Build button to specify or modify (existing connection) connection string.
- Use shared data source reference: No need of this option, as we are creating a Embedded data source here. select Use Shared data source reference radio button only, If you want to use the already created Shared data source then select existing shared data source from the drop down list, or can create a new shared dat source by clicking on New.. button, or modify an existing connection string by clicking on Edit button.
Now click on Build button as shown below.
Once you click on Build button, you will see a following options:
- Data Source: As we have already selected a Type of data source as SQL Server so By default you will see a data source Microsoft SQL Server (SqlClient). You can also change this option.
- Server name: specify the SQL Server name.
- Log on to the Server: specify, authentication mode. here we have used a windows authentication.
Once you provide all the details you can test connection to ensure data source connectivity is configured properly, just click on Test Connection.
You can see, a dialog box opens, and notified that Test connection is succeeded.
Now close the dialog box, and click on OK button.
Once you click on OK button, you get back to Data Source Properties window, there you can see connection string is created.
Now you can also take a look at options available in Credentials tab, when you click on Credentials tab you will see following options:
- Use Windows Authentication: for this option, report uses the current user windows credentials to connect to the data source. Here we are using the Window Authentication.
- Use this User name and Password: for this option report will use this username and password to connect to the data source. required the username and password to log into the data source.
- Prompt for credentials: when this option is enabled, whenever a report establishes a connection to a data source, it will prompt the given text, asking for credentials.
- Do not use credentials: for this option, report will not use any credentials to connect with the data source.
After that click on OK button.
Now you can see, an Embedded data source is created inside Data Sources folder.