Steve Chowles v SQL Server

Reporting Services 2005 for the DBA – Reporting Services Encryption 2 of 2

stevechowles — Tue, 28 Aug 2007 21:13:00 GMT

This is the second part of my two part series on Reporting Services Encryption. The first part of the series can be read here . This article will explain how we maintain the keys and various recovery scenarios. What Data is Encrypted First off let’s understand...(read more)

Reporting Services 2005 for the DBA – Reporting Services Encryption 1 of 2

stevechowles — Tue, 14 Aug 2007 20:22:00 GMT

If you understand how Reporting Services uses encryption then you will understand how to manage the encryption keys, recover from any encryption failure and more importantly, not to get in an unrecoverable situation. Although Books Online has some information...(read more)

Reporting Services 2005 for the DBA – Double Hop Authentication

stevechowles — Tue, 03 Jul 2007 13:58:00 GMT

One of the issues which crop up time and time again is the classic Double Hop Authentication problem. This article will explain what the issueis and how to configure your Reporting Services environment in order to use Double Hop Authentication. I will...(read more)

Reporting Services 2005 for the DBA – Changing Application Pool Identity

stevechowles — Mon, 02 Jul 2007 18:26:00 GMT

By default when you install Reporting Services it will by default use the NT Authority\NetworkService account for running the Application Pool processes. If you use NT Authority\NetworkService for the Application Pool used by the Report Server, when it...(read more)

Reporting Services 2005 for the DBA – Connectivity Issues

stevechowles — Mon, 02 Jul 2007 18:24:00 GMT

After an Installation of Reporting Services with the configuration complete, there is no guarantee you will be able to connect to Reporting Services straight away. So this article will discuss issues connecting to Report Server and Report Manager and...(read more)

Reporting Services 2005 for the DBA – IIS Security

stevechowles — Fri, 08 Jun 2007 16:30:00 GMT

This is the second part of my IIS article which today covers IIS Security. This is such an important area to understand especially when we get to double hop authentication in a future article. Before you read this article make sure you have read my previous article in the series since this one leads right off it.

Getting to the web pages can be quite tricky since there are many hoops you need to jump through in order to get there. What I will do is start from the client and progress through a security pipeline until you have reached the web resources you need.

IP Address Restrictions

IIS has the ability to deny access with clients that have a specific IP address. For a typical installation the chances are that there will not be any restrictions however; it is worth knowing in the event you get an HTTP Error 403.6 - Forbidden message.

IP Address restrictions are added in IIS Manager. When you view the Properties of the Virtual Directory, click on the Directory Security tab and click the Edit button on IP Address and Domain Name Restrictions.

Browser Authentication

When a browser issues an HTTP Request the web server will request Authentication information from the browser. Depending on how your browser is configured will determine if the browser will prompt you for a username and password or just forward your current credentials automatically. After the users credentials have been sent to the server a check is done to ensure the user has a valid windows account. If they have not then the user will get an HTTP Error 401.2 - Unauthorized message.

For Internet Explorer the simplest way of determining what your setting is set to is by first navigating to the web page. At this time you will either be prompted for a username and password or not. Also if you look at the bottom right of your browser you will see the Zone for the web site. Double click on the Zone to open the Internet Security properties at the correct zone. Some companies may disable the Custom Level button in Group Policy so you cannot change it. If you can click the button go ahead and then scroll right to the bottom. The last entry will be the User Authentication option allowing you to select which option is suitable for you.

One thing I want to stress is that I am only talking about intranet sites here within a company network. Accessing Reporting Services from the Internet is completely different ball game and requires extending Reporting Services functionality to support this.

Virtual Directory Security

Virtual Directory Security is made up of three areas of interest for us

· What Authentication does the Virtual Directory allow

· What Authorization does the Virtual Directory allow

· What Authorization does the NTFS folder Allow

For Reporting Services the only Authentication allowed by default is Integrated Windows Authentication. Unless you have a valid windows account you cannot access the web sites. You can view the authorization options from IIS Manager. When you view the Properties of the Virtual Directory, click on the Directory Security tab and click the Edit button on Anonymous Access and Authentication Control.

There are five different types of Authentication allowed however; for Reporting Services it does not support Digest Authentication or .NET Passport Authentication. My advice would be to stick with Integrated Windows Authentication only. You may be tempted to allow Anonymous Access for the virtual directories like you would for some web sites however; this gives everyone the same level of access in Reporting Services and in affect will switch off any Reporting Services security. So this is not advised.

Authorization is made up of Web Permissions and NTFS folder and file permissions.

On the Directory tab in IIS Manager the Web Permissions shown are those permissions allowed for every user’s access to the Virtual Directory. These permissions dictate the level of access allowed to the website however; there is not a one to one relationship to the underlying NTFS folder or files. If there is a failure at the Web Permissions level the user will receive an HTTP Error 403.2 - Forbidden message. You should not need to changes these permissions for Reporting Services.

The Virtual Directory points to a physical folder. A user accessing the website will need NTFS folder and file permissions in order to access the web pages. If the Web Permissions have Write access then there must be Write access on the NTFS permissions in order for the user to be able to write otherwise the user will receive an HTTP Error 401.3 - Unauthorized message.

By default the NTFS file permissions allow all Authenticated users access to Reporting Services by way of the local Users group.

Application Pool Security

The Application Pool runs under the context of a user as configured in IIS Manager. The user can be verified by searching for w3wp.exe in Task Manager and looking across to the User Name column. The default user is NT Authority\NETWORK SERVICE

This user will be used for compiling the application and loading any configuration information. The user used for running the application depends on the Impersonation value in the web.config file for the website.

If the there is an entry as follows <identity impersonate=”false”/>, the application will run under the context of the Application Pool user. If the entry is set to True, the application will run under the context of the user running the application.

Reporting Services has the value set to true which means when you access Report Manager, you are accessing the website as yourself which is required in order to use the Security built in to Reporting Services. One point to bear in mind is that Reportserver still uses the Application Pool account for accessing the Reporting Services catalog databases regardless of the value set for Impersonation.

So what user should you run the Application Pools as? Well when running with NT Authority\NETWORK SERVICE, the Report Server Application Pool account will connect to the SQL Server Repository as <domain>\<machine name>$ where <machine name> is the machine name where Reporting Services is installed. This appears rather generic and your company standard my dictate that you change it to something more meaningful. I will discuss changing the account in a future article.

INTERNALS ALERT

So how does IIS process a request from a browser?

· A user will issue an HTTP request something like http://<server>/Reports

· The request will be sent to the machine identified by <server> and since no port number was specified, there needs to be a process on the machine listening on port 80.

· Port 80 is a standard HTTP port number and on Microsoft platforms will be listened by a kernel process called http.sys

· When http.sys is first initialised it reads the routing table which links each URL to a specific Application Pool

· http.sys will route requests for Report Server to the Report Server Application Pool

· http.sys will route requests for Report Manager to the Report Manager Application Pool

· A worker process called w3wp.exe is started for each application pool

· The worker process will load the ASP.NET ISAPI DLL which is required for Reporting Services

· The worker process will then delegate the request to the HTTP runtime environment which is part of the ISAPI module.

· The worker process for an Application Pool will read the HTTP requests out of it Application Queue

· Once the http has completed the worker process will send the http response back to http.sys

· http.sys will send the http response out on to the network for the client to receive.

Reporting Services 2005 for the DBA – IIS

stevechowles — Fri, 08 Jun 2007 16:20:00 GMT

One of the most misunderstood aspects of Reporting Services is how it fits in with IIS and how does IIS security affect Reporting Services. The next two articles will explain the environment outside of Reporting Services and hopefully make things a bit clearer. This article will also be quite long however; it is one you can keep referring back to for future reference.

Can I start by saying that Reporting Services is just another web application. There is nothing special about it, so any knowledge gained from other web applications will hold true.

As mentioned previously I will only be discussing IIS 6.0 on Windows 2003 for this series.

IIS

OK so let’s start with IIS. Internet Information Services (IIS) is an application which hosts websites. There are typically a few ways to request website pages:

1. http://<server>/webpage.aspx

2. http://<server>:<Port>/webpage.aspx

3. http://<server>/virtual_directory/webpage.aspx

Using example 1 above, the server would only be able to host one website because there will only be one webpage.aspx file.

We can get around the limitation in 1 by having different Port numbers assigned to our websites as shown in example 2. If you do not specify a Port Number then Port 80 is assumed.

A third way to host multiple websites is to configure Virtual Directories as shown in example 3. These will have the same Port number but unique virtual directory names on the server. Each Virtual Directory maps to a different folder on disk containing the web pages for that website. That way we can have multiple virtual directories allowing multiple webpage’s called default.aspx for example.

IIS consists of the IIS Admin Service along with many other dependant windows services. There is also a kernel device driver called http.sys which is used for sending and receiving HTTP requests.

In order to understand how to configure IIS and then ultimately Reporting Services, you need to be familiar with the Internet Information Services Manager application which will be in the Administration Tools folder off the Start Menu on any machine which has IIS installed. Later on I will be making references to this tool simply as IIS Manager.

Web Site Name

Within IIS you create a Website. A website has associated with it; a specific IP Address and Port Number. The website can also be configured to listen on any IP address however; you need to be aware that if two websites are listening on the same IP addresses and Port numbers, you will not be able to start the second website. This will not be the case when one site is listening on localhost and the other on All Unassigned. If you connect using localhost as the server name, you may end up going to the wrong website and then getting a connection failure.

Within each website you can have zero or multiple Virtual Directories. The Virtual Directory names must be unique within the website.

When IIS is installed there will be a single web site created called Default Web Site with zero Virtual Directories and which listens on any IP address and port 80. You can view the IIS configuration by running IIS Manager on the Web Server.

Virtual Directories

Reporting Services requires two Virtual Directories created which by default will be created under the Default Web Site however; you can create a new web site just for Reporting Services if you want different default settings to those provided by the Default Web Site. In fact in some organisations the Default Web Site is locked down for security reasons so you may be forced to generate your own. Each Virtual Directory will inherit the properties from the website where it is created.

Under your chosen web site you will need two virtual directories for the Report Server and the Report Manager. You can choose your own names for the virtual directories however; I would stick with the defaults since everything you will read will use those names.

The default virtual directory for the Report Server is Reportserver and is reached by the URL:

http://<server>/Reportserver

The default virtual directory for the Report Manager is Reports and is reached by the URL:

http://<server>/Reports

<server> is either localhost if you are running from the web server or the web server machine name if you are running from outside the server. In fact your environment may require you to use a Fully Qualified Domain Name (FQDN) in order to reach the web server.

If you decide to install multiple instances of Reporting Services on the same machine, then you need to use instance names to differentiate them. The Virtual Directories will need to have $<instance> appended to them where <instance> is your chosen instance name for each. For e.g. http://<server>/Reports$Instance

Application Pools

One of the changes in IIS 6.0 is the introduction of Application Pools.

When you interact with a web site it needs to spawn an external process in order to communicate with the end users. This process is at the Application Pool level and provides an isolated environment for web sites. In affect it means that any failures within one Application Pool will not cause any issues for users in a different Application Pool.

By default the Report Server and Report Manager Virtual Directories will use a default Application Pool. My advice is to create either one Application Pool for both Virtual Directories or create two Application Pools, one for each Virtual Directory. This will become more apparent when I talk about IIS Security in the next article.

You can determine what Application Pool is used by a Virtual Directory by looking at the Properties of the Virtual Directory in IIS Manager. The Application Pool is on the Virtual Directory tab. You can also change it there as well.

Sharepoint Integration Mode

Reporting Services can be integrated with Sharepoint and is known as Sharepoint Integration mode. Without Sharepoint the installation is known as Native Mode.

I will not be discussing Sharepoint Integration Mode however; if you do intend to install Sharepoint on the same machine as Reporting Services you will need to configure Sharepoint to exclude the Reporting Services Virtual Directories otherwise Reporting Services will not work. See Books Online for more information.

So now we have explained the IIS Environment, the next article will explain IIS Security.

Reporting Services 2005 for the DBA – Touching on the Architecture

stevechowles — Fri, 01 Jun 2007 14:48:00 GMT

Now I do expect DBA’s to have an idea of what Reporting Services is however; it is a server based Reporting Platform to create a multitude of different reports which contains data extracted from non relational, relational and multidimensional data sources.

So to kick off the series; after Reporting Services is installed what do we have. We have four main components; three will be installed on the machine where Reporting Services was installed and the fourth will be part of SQL Server which may be on the same machine as Reporting Services.

The four components on the Reporting Services machine are:
• Report Server
• Report Manager ASP.NET application
• Windows Service
• Reporting Services Repository

Report Server
The Report Server is the main engine which processes the reports. It is written as a Web Service and also accepts URL based queries like any web application.

So what is a Web Service you may ask? Well firstly a Web Service was not designed by Microsoft; it is in fact a protocol designed by the World Wide Web consortium. It enables a non visual interactive client to send a message over HTTP and receive a response. Actually let’s go a bit deeper. A Web Service uses a protocol called SOAP (an acronym which I will let you look up), to take a message in XML and pass that message using HTTP to a server which then reads the XML. The server runs a function and then returns the results as XML back to the client. By using HTTP and XML the client and server can be on any hardware or operating system since they all speak XML and HTTP, great isn’t it.

Anyway our Report Server is a non visual interactive application which is great at taking in XML commands and returning XML responses.

Generally reports are run using the URL syntax where as maintenance of Reporting Services is generally done by specifying command through Web Services as this has a far richer command set that can take a greater number or parameters.

Report Manager
Since the Report Server is non visually interactive and we love graphical user interfaces, Microsoft gave us a neat ASP.NET application called Report Manager in order to interact with the Report Server. Using Report Manager enables us to run reports as well as administer Reporting Services. You will get to use this allot and you will become nice and familiar with it.

Windows Service
The third component is a Reporting Services Windows Service. Now the Report Server sits there looking pretty just waiting for things to do. The users will give it plenty to do with Report Manager but what if there needs to be tasks run in the background on behalf of users or general maintenance tasks; this is where the windows services comes in. It is responsible for background activities which will be covered later in this series. So in affect the Service is a background user.

Reporting Services Repository
Lastly the two databases which will be held in SQL Server are called ReportServer and ReportServerTempdb.

The ReportServer database is the catalog which holds all the metadata for our Reporting Services installation. When you play around with Report Manager, you are in fact reading and writing to and from the Reportserver database.

The ReportServerTempdb database is the storage area to hold user session information as well as any reports which have been executed. Every report run will end up here before the end user sees it in order to do things like moving between report pages so the data remains consistent. It is also worth pointing out that the contents of this database are persistent across reboots.

So there you have it, a quick overview of the architecture. Now I could have gone a bit deeper and tripled the size of this article however; Books Online will do this for me. I encourage everyone to go out and read a bit more about what I have said. What’s that I hear, where’s the “Internals Alert”, oh ok here is a little something for you.

INTERNALS ALERT
Unlike Reporting Services 2000, in the 2005 version you must keep the Reporting Services Windows Service running at all times. This is because the encryption and decryption built in to Reporting Services is done by the Windows Service. It use to be done inside the Report Server Web Service however; since Reporting Services uses IIS, any changes to the ASP.NET account password by external components or fixes would break the encryption. I will be discussing encryption later in the series.

Reporting Services 2005 for the DBA Series

stevechowles — Fri, 01 Jun 2007 14:32:00 GMT

Welcome to my blog which starts off with a "Reporting Services 2005 for the DBA" series.

So what will this series achieve? Well there is plenty of material out there and a great deal mentioned in Books Online however; a DBA’s first experience of Reporting Services possible comes from a developer asking for a Reporting Services installation followed by, “how should I do x, y, and z”. It is hard for a DBA to be a master in all areas and so without rewriting Books Online, I will be giving the DBA the information they need in order to meet the developer’s needs. Since Reporting Services uses IIS, I will only be covering IIS 6.0 on Windows 2003 since there is a big architectural difference between 5.0 and 6.0.

Now as the series progresses I will discuss various topics however; I may miss out some specific aspects on a topic not because I forgot to add it but rather I do not want to leave you in a situation where you cannot see the wood for the trees.

So in that case what will I not be covering? Well I am not going to talk about how to install Reporting Services, nor will I teach you how to write a report, since the DBA’s real exposure comes from answering the questions of someone who is already writing the reports. This is a real world series so I hope you will enjoy it. Also if it does not meet your needs, then there is Books Online right. No just send me some feedback and I will do my best to incorporate what everyone would like to see.

Hey, since my target audience are DBA’s I could not write a series like this and not include Internals information so keep a look out for those “Internals Alert” sections which will crop up here and there.