A nice video on SSRS with SP : http://technet.microsoft.com/en-us/video/Video/hh858469
A user can connect to a report server in a variety of ways. Commonly, a user opens a browser and connects to Report Manager to view reports or manage server properties for reports. Other user activities that require a connection to the report server include report deployment using SQL Server Business Intelligence Development Studio (BIDS), server administration using SQL Server Management Studio (SSMS) or a command-line utility, and report rendering using URL access. In addition, programmatic access to the report server can occur when a custom application implements the ReportViewer control or calls a Reporting Services Web service.
No matter which of these connection methods is used, the report server requires that the client authenticate the user or process that's requesting the connection. The client passes the information to the report server in an HTTP authentication request. The report server in turn passes this information to a Windows Authentication extension (which is the default) or a custom security extension. If the authentication fails, the report server sends an HTTP 401 Access Denied error to the client application.
In a SharePoint Server 2010 integrated mode deployment, the authentication process is similar to authentication in a native mode deployment, with one additional authentication point, as shown in Figure 5. After the user is authenticated when accessing the SharePoint site, the SharePoint site's application pool identity must be authenticated before connecting to the site's content database to retrieve the requested content. Then, just as in native mode, the user's request for a report requires a connection to the report server database and a connection to the data source host.
The Web application associated with the SharePoint site determines the authentication method used for the client connection. When you create the Web application in Central Administration, you can specify either classic mode authentication or claims-based authentication.
Classic mode authentication. This authentication method relies on Windows integrated security. The client application presents the SharePoint server with the credentials used to run the browser or, in the case of a custom application, the credentials used to run the current thread. When you select this method, you must further specify Kerberos or NTLM as the authentication type.
Claims-based authentication. This authentication method relies on the use of a token for an identity that has been issued by a trusted authority so that private information doesn't need to be passed around. By using claims-based authentication, you can more easily support access to a SharePoint site by internal and external users.
Claims-based authentication can be used in combination with more familiar authentication methods. For example, you can configure claims-based authentication to use Windows authentication (NTLM, Negotiate, or Basic) to translate a Windows identity into a claims identity. Another option is using forms-based authentication to prompt the user for credentials in a login page that uses ASP.NET membership and role providers to establish the claims identity. A third option is identifying a Trusted Identity Provider that issues tokens to a client that the SharePoint server will accept for authentication.
Data Source Authentication (Both Modes)
When a report executes, the report server provides credentials for authentication to the server hosting the data source for the report. When you configure the data source, you need to choose one of the following authentication types.
Windows integrated security. You should use this authentication type in only two scenarios. You'd use it when your data source and the report server are located on the same server. You'd also use it when your network is configured for Kerberos and the report server is configured to use Negotiate or Kerberos authentication. Otherwise, the report server won't pass user credentials to the data source, nor will the report server prompt the user for credentials.
User-supplied credentials. You can prompt the user for credentials when the report executes, although users might grow weary of this method if they frequently request a report. By default, the report server assumes the credentials represent a database login, but you can select a check box to change the usage to Windows credentials. If database credentials are used, the report server connects to the database server using the service account or the unattended execution account specified in Reporting Services Configuration Manager.
Stored credentials. If you need to execute reports on a scheduled basis for caching, snapshots, or subscriptions, you must use stored credentials and supply the credentials in the data source. If you don't need to send the user's identity to the data source to apply row-level security to query results, using stored credentials is a good option. As with user-supplied credentials, you can specify whether the database server or Windows will authenticate the credentials. In addition, you can specify whether the report server can impersonate the user after making the connection to the database.
No credentials. You can use this option for data sources that don't require authentication or if you provide credentials in the data source connection string (which isn't a recommended approach) as long as the unattended execution account is configured. The report server connects to the data source server using the unattended execution account, although authentication will fail if SQL Server Analysis Services (SSAS) is the data source.
Not a Straightforward Task
As you can see, configuring authentication in SSRS isn't always a straightforward task because it occurs at multiple points and involves multiple technologies. Network settings, browser support for authentication modes, and database configuration all affect the range of options available to you for implementing an authentication method. This article provides an overview of these various options. If you'd like to learn more, check out the resources listed in the Learning Path.
SSRS Authentication (SharePoint Integrated Mode)
When you configure SSRS to run in SharePoint integrated mode, you must specify an authentication mode on the Reporting Services Integration page in Central Administration. You can find this page in the General Application Settings section. SharePoint uses either Windows authentication or Trusted Account mode to authenticate with the report server.
Windows authentication. With this authentication mode, SharePoint uses a delegation model to perform its own authentication when a user accesses a site. SharePoint also uses the delegation model to forward the authenticated identity to the report server for another round of authentication, which requires the user to have a login for the report server. If Kerberos is enabled on your network, Windows authentication is a good option. You can then configure report data sources to use Windows integrated security to pass the user's identity to the database server.
Trusted Account mode. When the report server uses Trusted Account mode for authentication, SharePoint doesn't send the client identity but instead uses the application pool identity assigned to the SharePoint Web application hosting the requested site. The account assigned as the application pool identity must be in the list of service accounts in the Security Configuration section on the Operations page of Central Administration. SharePoint also generates a token, which it sends with the request to the report server. The token is passed back to SharePoint to provide the security context for the response. This token is valid only for the originating site to reduce the surface area for attacks. Data sources must use stored credentials or prompted credentials because Windows integrated security isn't supported when the report server is in Trusted Account mode.
If you configure a Web application to use claims-based authentication, the SSRS proxy in SharePoint will use Trusted Account mode authentication (even if you configured it to use Windows authentication) and forward a SharePoint user token to the report server. The report server, in turn, generates the appropriate security context for the user token and returns the request.
Reporting Services Service Authentication (Both Modes)
Whether you're running SSRS in native mode or SharePoint integrated mode, the Reporting Services service must be authenticated when connecting to the report server database in response to a report request or content management task. In fact, any activity that requires the report server to store the application state, retrieve reports, or retrieve other report-related data (but not including execution of queries for reports) also requires authentication of the Reporting Services service.
You configure the identity and the authentication mode on the Database page of Reporting Services Configuration Manager. There are three authentication options:
- Windows Credentials—You must supply a valid Windows account and password to use for authenticating the report server's connection to the report server database.
- SQL Server Credentials—You need to provide a SQL Server login and password.
- Service Credentials—The service account that you're using to run the Reporting Services service is automatically selected for authentication. If you later change the service account, you must also reconfigure the report server database credentials to ensure that the authentication process works properly.
No additional configuration is necessary. Reporting Services Configuration Manager runs a script to allow the applicable identity to connect to the database server
