1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Managing Data Source Connections

Discussion in 'SQL Server' started by MinalS, May 23, 2015.

  1. MinalS

    MinalS New Member

    Jul 8, 2014
    Likes Received:
    Trophy Points:
    The administrator is responsible for carrying the tasks like granting permissions for managing reports on the report server. The following tasks are performed by the administrator.
    1. Modify the credentials which are useful for providing access to users for managing reports with their credentials. The credentials are stored in the server
    2. Modify the connection strings used for changing the computer or moving the database. The static and dynamic connection strings can be managed.
    3. Change the embedded data source to a shared data source
    4. Create or change a shared data source on the report server
    5. The data source properties can be controlled by permitting permissions on reports, models, or shared data source
    Connecting to the report data sources

    The permissions for accessing the report on the report manager are not sufficient to render it. The user must connect to the data source from where the report derives the data. The report file contains the credentials to access the data source. When the user logs into the service account, the report is accessed on the report manager, it is immediately rendered. The service account was useful for all the services with windows authentication.

    The report has four options for connecting to the server data. They are as mentioned below:
    1. The credentials supplied to the user executing the report
    2. The credentials stored securely in the report server
    3. Windows integrated security for user
    4. The credentials are not needed

    Connect using user supplied credentials

    If user selects the option for user supplied credentials, user must provide the credentials for the data source every time the report is executed. When the report is executed, a prompt message for adding the user name and password is shown.

    If the Use Windows credentials when connecting to the data source is checked, the credentials will be treated as Windows login. If it is unchecked, it is treated as database login.

    Connect using the stored credentials

    In this category, the stored credential specification is selected in the data sources property of the report; they are stored in the report server database. The user name and password are encrypted on the report server and the password is not revealed.

    If the checkbox Impersonate the authenticated user after connection has been made to the data source is checked, the data source uses the credentials to impersonate the user.

    Connect user with Windows Integrated security

    Windows integrated security does not need the user to provide the credentials. The windows login credentials that user uses foe accessing the report manager are passes along with the database server. The database server can accept the credentials.

    Credentials not needed for connection

    They are not widely used unless some constraints like remote data do not need credentials. The exception holds for the secure connections, the credentials are part of the connection string.

    Windows user access to report on the report manager

    In the report manager, the user is assigned with the browser role for the specific folder. There are some steps used for accessing the report in SQL Server reporting services. They are as mentioned below:
    1. Login to the computer using the credentials created by the user
    2. Open the IE browser and add the URL to access the manager
    3. Click on the folder and follow the instructions
    4. The DBA needs to provide the login credentials to the server, access permissions to the database, select permission to the table
    5. Log on the system using the user credentials and access the report manager by adding the URL address
    6. Log out of the system by clicking Start, Log Off, Switch user for using the administrator login
    7. The report manager can be accesses using the URL
    8. Log out as administrator and login as user. Open the report manager in the IE browser by adding the URL.
    9. Add the credentials for Log In name and password. Click view report button
    Deploying reports

    The deployment works the similar way for reports as the shared data sources. User can deploy a single report and not the complete project. The Visual Studio calls the push method for deploying to the report server. The pull method is used for uploading the report to the report manager.

    Deploying a single report

    The steps for deployment of a single report are as mentioned below:
    1. Create a new folder and add a name to it
    2. Open the project in the Visual Studio application
    3. Click Project, Import MSAccess properties
    4. Add the URL of the server in the TargetServerURL field
    5. Click Apply and click ok
    6. Right click on the .rdl file and click deploy in the drop down menu
    7. The report is displayed in the output window of the application
    8. Click on the Properties tab of the report
    9. Click on the Data sources and open the page
    10. Select Windows Integrity and click on the contents
    Deploying the report with a shared data source
    1. Open control panel, click services in the administrative tools
    2. Click Project, Shared Data Properties
    3. Add the target server URL in the page
    4. Click Apply button and click ok
    5. Right click on the folder in solution explorer, click deploy from the drop down list
    6. The project is deployed and displayed in the output window
    7. The report manager contains the SharedData folder
    Uploading a report using report manager
    1. Open the Report manger file in the browser. Create a new folder and save it
    2. Click on the Upload File icon
    3. Click on the Browse and select the .rdl file to be uploaded

    4. Add a checkmark to overwrite item if exists. Click ok
    5. Click on the folder by clicking the properties
    6. Click Data Sources option
    7. In the page, connect using credentials supplied by the user running the report. It is selected by default as MS Access file needs authentication
    8. Click View tab. The page displays the login user input credentials
    9. Add the Admin for user and keep the password field as blank.
    Modifying the reports on the report server and linked reports creation

    There are instances where the report server consists of the report but the definition file is unavailable and the reports needs to be modified. User can download the file to the hard disk and modify with the BIDS and upload to the server.

    The following figure shows the modification of the report definition file.


    The process consists of the following tasks.
    1. Download the file from the report server using the report manager to the file system
    2. Pull the report file to the Visual Studio project and save the files
    3. Upload the report back to the report server from the manager
    Linked reports

    There is requirement when the same report needs to be used by different users assigned with different roles. A single report is created and links to it are controlled by a hidden parameter restricting the report content based on the role.

    Download a report definition file from the report server

    The following steps are used for downloading the report on the report server and persisting it to the drive.
    1. Access the report manager using the URL in the browser
    2. Click Home, the folder created by the user
    3. Click Properties, General
    4. Click on the Edit hyperlink above the apply button
    5. The File Downloaded message box is displayed
    6. Save the file to the location which can be used by the Visual studio project or uploaded to the report server
    Report delivery

    Delivering the report after the development is completed is the important task of the report manager. The delivery of the reports can be scheduled or can be delivered on demand. The manager has a flexible interface.

    Report Caching

    The cached information is retrieved from the temporary location, the turn around time is very fast and data is not needed. The report caching consists of a lifetime that can be specified and the same report is not needed. The report cache can enforce security for migration. The report is available to the user or group that created the cache.

    The user creating the cache must have the report data source configured for saving the credentials. The cache report has a finite lifetime after which it is removed. While accessing the cached report the credentials are stored. The permissions for changing the cache properties must be covered by security.

    Report cache is created by the user but it can be automatically created. The execution snapshot is used for the creating cache on a scheduled basis.

    Working with Report Cache

    The execution navigation link for a report provides user with the access on how the report is executed. The execution timeout for the report can be modified.
    1. Access to the report manager and properties of the report. Click on the Properties tab and select the execution link on the left.

    2. Change the default cache to temporary copy and keep the default expiration to 30 minutes
    3. Click on the apply button. A cache will be created after 30 minutes after which it disappears and the report will send the query to the database
    4. Click View button. A cached copy will be rendered in the default format.
    5. Return to the execution link page from the properties for the report
    Schedule a report cache

    The access to the new schedule or edit schedule page on the report manager page is possible. The scheduling can be used for subscriptions, refresh cached reports, snapshots creations.
    1. Select the option Cache a temporary copy to the report. Expire, copy report on the following schedule. Click configure

    2. Choose the schedule and click ok
    Report subscription

    The reports are extracted from the report manager by searching the report the user needs. The reports can be delivered at a specific time in a specific format to the user to use the subscriptions. The timer or an event is used for this purpose.

    The reporting service initiates the execution of the report and sends it to the user. The report subscriptions are processed on the server. The end users receive it through the delivery extensions and deployed on the report server.

    There are two subscriptions types as mentioned below:
    1. Standard: It is managed by individuals and has only one set of presentations, parameter and delivery options
    2. Data driven: The subscription is query based and need knowledge of parameters. The report server administrators create and manage the subscriptions.
    The reports can be delivered in two different ways.
    1. Email: There are several ways the reports are delivered by email. They can be done through an hyperlink to generate report. By sending the notification in the subject line. Embedding and attaching the report.
    2. The report file is sent to the shared folder
    Creating an Email subscription

    Before executing the email subscription, the SQL server agent service must be running. If it is not present, click Start, Control Panel, Administrative Tools, Services.
    1. Open the report and modify the data source properties. The use windows credentials when connecting to the data source is checked. Click Apply button
    2. Click Subscriptions tab and click new subscription
    3. Select the Select schedule button
    4. Select the option Once and set the time for the report to be scheduled. Click Ok.
    5. Click on the subscriptions tab in the report. The list of subscriptions gets displayed.
    6. Wait for specific time and click on the Status list column in the subscriptions
    7. The email delivery starts and the updates can be viewed.
    Creating a file share subscription

    Before the activity is started, a shared folder on the hard drive is created.
    1. Access report manager by providing the URL. Click on the report to be subscribed and open it
    2. Click Subscriptions tab and click new subscription
    3. Click on the drop down for selecting the item. Select Windows File Share and click ok
    4. The report delivery options and subscription processing options page is displayed.

    5. Add the details. Fill in the credentials in the page
    6. Click on select schedule button. In the displayed page, select once option and schedule the timing in the box. Click ok
    7. Click subscriptions tab. The file share subscription is displayed.
    Creating a data driven subscription
    1. Access the report manager by providing the URL. Click on the report in the Shared folder and open it
    2. Click on the subscriptions tab and click new data driven subscription
    3. Add a description and accept the default. Select Email from the drop down list and click next
    4. In the connection string, add the credentials. Place a checkmark for Use a Windows credentials when connecting to the data source. Click next button
    5. Add the command and accept the default time out, click next button
    6. For the delivery extension settings, select the suggested options
    7. Select the on a schedule created for this subscription and click next
    8. The subscriptions tab page is displayed. Check the status of the subscription after the specified time
    Last edited by a moderator: Jan 21, 2017

Share This Page