Rahul's space

Error Message : “User does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed”

One of the frequently asked question in many forums is “I am an admin on the machine (Windows Vista or Windows 7 or Windows 2k8) where SSRS is installed but I am not able to access the reports”.

Recently I faced a similar issue. I am an admin on the machine which has Windows 2008 R2 installed. On top of it, I installed SSRS 2008 R2 and then configured the RS with proper values.

Then when I tried to access the report manager by navigating to the URL, I got an error message like

User ‘Domain\User’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.
 

I once again checked my configuration but everything looked fine. Since the error message gave some hint on UAC, I ran the browser as admin and navigated to the report manager. Now it did not throw any error message. I was able to see the home page and all other menu including the Site Settings.

I added myself as System Admin in the Site Setting

Closed the browser and opened it again in normal mode. Still  it was throwing same error.

Opened the browser again in Admin mode. Navigated to the home folder and added myself and assigned to all the roles.

After this when I ran the browser in normal mode, I was able to access all report manager and all the folders.

The issue is that by default UAC allows the users (even the administrators) to do the tasks with the privilege of normal user, i.e, say when you open the browser or an application, it is opened with the normal user’s privilege and not that of administrator’s privilege.

What I understood is that when I opened the browser in normal mode, UAC passed standard user’s privilege to SSRS and hence SSRS was not able to identify that I am an admin. To get the admin access either I have to run the browser in admin mode or add myself explicitly to SSRS System Admin roles.

So you have two options to tackle this issue

1) Fix it at your SSRS end as outlined above  (or)

2) Manage your UAC appropriately.

For managing UAC account follow the following steps

1. Go to Control Panel
2. Select User Accounts
3. Change User Account Control Settings

Change notify option to never notify and press OK

I hope this would be helpful 🙂

Introduction

In our earlier articles, we have seen how to design a report and connect to the database to pull out the values based on the requirement to be shown in the report. Also, we have seen the configuration section on how to configure the SQL Server Reporting Services manually by providing the Report Server name. In this article, we will cover how to deploy the application to the SQL Report Sever and access it online using the web browser.

Steps

To start deploying a report to the server first we need to design and develop a report based on the requirement. Since we have already prepared some samples from the earlier articles, we will be using the Part 2 sample and start deploying to the server gradually.

Once we have opened our project, we can see the design in the IDE as shown in the screen below.

Now we are ready with the design. Since we need to publish the report to the server, click on the ‘solution explorer project name’ and right click to select the Deploy option as shown in the screen below.

Once we click on the Deploy button, the Visual Studio starts deploying the application report to the server and we can see the status from the output window as shown in the screen below.

Once the report is deployed to the server we can see the output message indicating that the deploy is completed and we can access the report as shown in the screen below.

Once we are done with the deploy, in order to access the report just copy the path from the output window of the Visual Studio IDE. This path will be the server path to access the list of reports as shown in the screen below.

Copy and paste the URL on to the browser and navigate through the URL. This will allow us to see the list of projects deployed as shown in the screen below.

In this article, we have deployed the Part2sample application so click on the Part2Sample project. It will navigate to the next page where we can see the report name listed (Report1) as shown in the screen below. Similarly, for all the other projects we can see the report listed in the respective folder.

Clicking on the report name (Report1) we can see the report being generated in the web browser with the complete formatting along with the data as shown in the screen below.

Conclusion Therefore, in this article we have seen the options to deploy the report on to the reporting server to access it online across the client.

Reference :http://beyondrelational.com/modules/12/tutorials/26/tutorials/4601/getting-started-with-ssrs-part-5-deploying-a-report-to-the-server.aspx

Introduction

In our previous articles, we have seen how to install SQL Server Reporting Services and to create a new report using the Report Wizardand Report Designer Window. Now, in this article we are going to see how to configure SQL Server Reporting Services in Windows Server 2008 environment.

To deploy the report on a server and to use it across the environment we need to have the Report Server up and running. To do so we need to do some configuration for the report server. It will be covered in this article. Let us see the step-by-step process on the configuration of the SQL Server Reporting Services.

Steps

Considering that SQL Server Reporting Services (Denali or 2008 R2) is installed, we will step forward to do the configuration. Go to All Programs>> Microsoft SQL Server 2008 >> Configuration Tools>> Reporting Services Configuration Manager as shown in the screen below.

The Reporting Services Configuration Manager will open as shown in the screen below.

Now we need to connect to the instance on which we need to configure the SQL Server Reporting Services. A pop-up window will appear prompting to select the Server Name and Instances Name. Select the appropriate Server and Instance Name and click on Connect as shown in the screen below.

Once connected successfully, we can see the Reporting Services instance running and option to start and stop the services. In addition, we can also see the details of the server running as shown in the screen below.

Now click on the Service Account on the left side menu to configure the account details. Here, we have two options to select the account details. If our server is domain controlled then we will go ahead with selecting the domain user name and password. Else, we can use the built-in account as shown in the screen below. Once we select the account click on Apply button to save the configuration.

Once we are done with the above configuration, click on to the next menu Web Service URL to configure the Virtual Directory. We can configure the virtual directory with any customized name or we can use the default name ReportServer and use the default configurations as shown in the screen below. Once we are done with the above configurations, click on Apply button to save the configurations

Now click on the next menu Database to create a new database or use an existing database for the SQL Server Reporting Services for saving all the reporting server data and application data for running the reports. Click on Change Database to select the database as shown in the screen below.

Once we click on the Change Database button, a new pop-up window will be displayed to configure the database. Here, we can select to either create a new database or use existing database as shown in the screen below.

Follow the basic steps used to create the database. Once the default steps are passed, we can see that the database is created as shown in the screen below.

Now click on Finish button. We will see the database selected and the login details on to the main wizard as shown in the screen below. Once the required changes are done for the database, click on Apply to save the database configurations.

Now go to the next menu Report Manager URL and select the Virtual Directory. Name it as per the requirement, else by default we can use the one created by the server (Report) as shown in the screen below. Click on Apply once we are done with the configuration.

Now we are done with all our configurations. To check if the configurations are done perfectly we need to navigate tohttp://<Servername>/Reports and check. Since in our article we have used the built in server we will try with http://localhost/Reports

If the configurations are done correctly, we will see the Report Server screen as shown in the screen below.

In the above screen, we can see some of the projects deployed to the server, which we will cover in our next article.

Summary

Therefore, in this article we have seen how to configure the reporting services on to windows server 2008 server. In our next article, we will see how to deploy the Reporting project, which we created in Part 2 of this series.

Introduction

This article would cover how to design a report using the Report Designer. In our previous article, we saw the option on designing a report using the Report Wizard with just the configurations used to design and develop the report with some simple steps. However, in this article we will design every component on our own with the help of the Report Designer available with the SQL Server Business Intelligence Studio templates. Let us see the step-by-step process on how to design the report with the Report Designer.

Steps

The first step towards our task is to open Business Intelligence Studio. To open it, we would need to navigate through Programs >> Microsoft SQL Server Denali / Microsoft SQL Server 2008 as per the installed version and select Business Intelligence Development Studio as shown in the screen below.

This will open Visual Studio. It is the Business Intelligence Development Studio for SQL Server as shown in the screen below.

Now select File >> New >> Project to create a new project. We can directly use the shortcut Control + Shift + N to open a new project as shown in the screen below.

Once we have selected the New Project, a list of templates will be available as shown in the screen below.

Select Report Server Project template from the list of templates available and provide a valid name for the project as shown in the screen below.

Clicking on OK will create a project and open the project in the SQL Server Business Intelligence Studio as shown in the screen below.

Initially, there are no files created and only two folders are available. The next step is manually adding a report to the project. To add the report, click on the Reports folder in the solution explorer and select ADD >> New Item as shown in the screen below. Selecting Add New Report will go directly to the Report Wizard that we have already covered in the previous article.

Now, a window will be displayed with a list of available items that can be added to the project as shown in the screen below. SelectReport from the list, provide a valid name and click on Add button to add a report to the project.

This will add the report to the project and open the Report Designer to allow designing the report as per the requirement as shown in the screen below.

Next step is to go to the Tool Box. Then drag and drop a table to design the report as shown in the screen below. Now we need to configure the data set first in order to design our report table.

To do the configuration we would first need to create a data source. This can be achieved by clicking on the New button. It will open a window where we would need to supply the details of the connection string as shown in the screen below. Configure it based on the database from which the data for the report needs to be collected.

Clicking on OK will go back to the old window with the data source available for pulling the data for the report. Now click on the Query Designer button to build the query required to pull the data as shown in the screen below.

Clicking on Query Designer will open a new window as shown in screen below. Write the query required to fetch the data. In this example, we are going to use the Authors table from the Pubs database. Once we write our query, click on the Execute button to get the complete details of the data for our reference to finalize the query. We can perform as many executes as required to get a final data as per the business requirement as shown in the screen below. Once we are done, click on OK to close this window and navigate back to the wizard.

Clicking on OK button will go back to the previous window and we can see the selected query available in the list as shown in the screen below.

Click on the OK button to complete the process. With this, we can see the table created in the designer view as shown in the screen below. We can customize the table as per our requirement.

Now we need to customize the report by dragging the outline of the table to the required length and width as shown in the screen below.

Next, we need to go to the data source we have created and select which columns should appear in which part of the report. To do this, first select the cell and click at the right side corner of the cell to get the list of columns as shown in the screen below:

Select the columns based on the requirement and edit the headers to the required customized format as shown in the screen below.

Click on the Preview tab to view the report’s preview as shown in the screen below.

Now we can see the report output in the preview mode. We can build and execute the project if we are satisfied with the report output. Else, we can go ahead and change the report design based on the business requirement. Since we have not used any inbuilt wizard, we need to manually do all the customization (Selecting Styles formats etc.). Now we have made some customization as shown in the screen below.

Press F5 or hit the Play button from the tool bar to build and execute the project to get the report output. However, we will get an error message that the start-up report project is not specified as shown in the screen below.

Go to the properties of the project and select the report as shown in the screen below. Since we have not used any wizard to build the report generation, these steps needs to be performed manually.

Once again, press F5 or Play button from the tool bar to build and execute the project. This will present the result as shown in the screen below.

Summary

Thus, in this article we have seen how to design and generate report from scratch without using the Report Wizard with the help of SQL Server Business Intelligence Studio.

Introduction

In this article, we would see what SQL Server Reporting Services are and a high-level overview of the architecture and the components involved in running reports on the SQL Server. This article is the first part of the series on SQL Server Reporting Services right from the architectural overview to running the customized report on different servers with deployment.

Overview

SQL Server Reporting Services provides a server based reporting platform, which allows customized reporting functionality for a variety of data that is transformed from different data sources. SQL Server Reporting Services 2008 provides a rich set of ready-to-use tools and services that help the developers and the designers to Create, Design, Deploy and Manage the Reports. It includes the basic report for the organization and provides customized reports with programming features to provide a rich user dashboard to extend and customize the functionality requirement.

SQL Server Reporting Services has a report server. It is a Windows Service with a set of features and runs separately in different application domains. SSRS has 2 components which are quite important for the architectural perspective. The first component is the Report Server, which acts as the heart of the report. Whenever a new report is created, it will be saved in the report server. It is the centralized location where all the reporting modules resides and provides an access to all the report. The second component is the Report Designer, which is also used to design the report and maintain the report design with the help of the Microsoft Visual Studio 2010 IDE or the latest IDE based on the requirement.

SQL Server Reporting Services also provides a useful feature of Scheduling and Delivery processor that pushes the reports to email inboxes or ftp locations based on the requirement. Now let us see the steps to install the SQL Server Reporting Services on to the development environment (We can make use of SQL Server 2008 R2 or SQL Server Denali CTP3) and follow the step-by-step process.

Steps to Install SQL Server Reporting Services

Download Microsoft SQL Server Denali or Microsoft SQL Server 2008 R2. In our series, we are going to use the latest version Denali reporting services. We can download the installer for SQL Server Denali CTP using the link Download SQL Server Denali CTP3

Once the download is completed, there will be 4 files downloaded as shown in the screen below. Now double click on theSQLFULL_x64_ENU_Install application file to extract the required files for installation. It would take some time to extract all the files to the same location as shown in the screen below.

Once the extraction is completed, refresh the folder. We will see a new folder created SQLFULL_x64_ENU as depicted in the screen below.

Now navigate through the folder. There will be a list of files as shown in the screen below. Point at Setup file and right-click on the file and select Run as administrator.

This will start the installation process. We can see the status message as shown in the screen below.

A new window will open leading to a step-by-step guide for installation process and some links providing help for our installation steps as shown in the screen below. We can now select the Hardware and Software Requirement to check the necessary things available for our installation. Else we can navigate to the link http://msdn.microsoft.com/en-us/library/ms143506(v=sql.110).aspx and check for the same.

In the left hand side menu, navigate to Installation. The option New SQL Server stand-alone installation or add features to an existing installation is listed as shown in the screen below. Click on the option to proceed further.

A set of Setup Support Rules runs is the background to check if the required process is available for installation. Once completed, a high-level result of how many rules failed or skipped is shown as depicted in the screen below.

To check the details of the passed results we need to click on the Show details button. This will showcase a complete list of rules ran and their result as shown in the screen below.

Now click on OK button. As the next step, a new window with evaluation options for the licenses is presented. Select Evaluation license or Express license as per the requirement. Since in this article we are going to evaluate the product installation we will select Evaluation from the list and click on Next button as shown in the screen below.

Clicking on Next button will navigate to the terms and conditions of the installation. Select the check box as shown in the screen below and click on Next button

A list of updates available to be downloaded and installed will be listed. As mentioned in the screen, it first installs the Setup Update (30MB). Remaining is downloaded & installed later. Click on Next to proceed further after selecting the updates as shown in the screen below.

Next we can see the Setup Update being downloaded as well as the status of the steps as shown in the screen below. Once the download is completed, click on Install to proceed further.

Next step is the new set of Setup Support Rules running and providing a result as depicted in the screen below. We can see two warnings. Since the server is domain controlled it shows the warning. Just ignore it and we can proceed further by clicking on Nextbutton as shown in the screen below.

To proceed further the type of installation needs to be selected. Select SQL Server Feature Installation and click on Next as shown in the screen below.

This will present a list of Features that needs to be installed. An option of Select All is available at the bottom of the list to select the complete list. In addition, we can select shared feature directory option by selecting the folder as shown in the screen below. Click onNext button to proceed further.

Now we can see some set of Installation Rules running and get the result as shown in the screen below. Click on Next to proceed further.

Further, as a next step we need to provide instance with a Name. Here we have provided with DENALICTP as the instance name, then select the install root folder and click on Next button as shown in the screen below.

A summary will be presented showing the Disk Space available in the server and the required space for the installation of Denali CTP3 as shown in the screen below. Click on Next to proceed further.

A list of services and the Start-up type of each service (Manual or Automatic) will be shown based on the selection made as shown in the screen below. Click on Next to proceed further.

Now comes the Server configuration. We can use either Windows Authentication or the Mixed Authentication modes as per the requirement. Then provide a valid Password. At the bottom, we can see a button Add Current User, click on that button to add the current user to the list as shown in the screen below. Click on Next to proceed further.

Next is the Error Reporting option. If we need to report the error to Microsoft then we can initiate it by selecting the Check box as shown in the screen below. Click on Next to proceed further.

Now Configuration Rules will be triggered and provide the results as shown in the screen below. Click on Next to proceed further.

A summary of the list of service and applications that are going to be installed is presented as shown in the screen below. Click onINSTALL to proceed further and start the installation.

This will start the installation and the installer will do step-by-step process of installing all the necessary components as shown in the screen below.

Once the installation is completed we can see the result and the status as shown in the screen below. Click on Close to complete the process.

Summary

So in this first part of the series of articles. We have seen what SQL Server Reporting Services are and how to install it on a development environment to start designing the report. In our next article, we will see how to design a report using the Design wizard.