Reporting Services Single Sign On (SSO) Authentication - Part 1
This will be the first in a series of posts related to setting up single sign on for SQL Reporting Services. As you may know SQL Reporting Services supports forms authentication and there is a forms authentication sample that can be found here. By leveraging the logic in the sample we can incorporate SSO (isngle sign-on) behavior into the SQL Reporting Services (SSRS) environment. A working Visual Studio 2010 solution is uploaded here.
There are typically two scenarios that define single sign-on. The idea behind both is that the user only has to enter their credentials once and can access multiple applications from that single authentication action.
Scenario One
In this scenario there is user information extracted from the request. This can come from a certificate or other container that holds data about the user. There is no action on behalf of the user. The extracted information can be added to the request so that the target application can extract and compare the data with expected values.
Scenario Two
In the second scenario the user makes a request of an application. If that request does not contain an authorization token issued by the central authentication store, the request is redirected to the authentication provider, which will require the user to enter their credentials and then be redirected back to the application. On the redirected request, the authentication token will be in place and the user request will be forwarded into the application.
In this particular sample we will be focusing on the SSRS specifics using headers that have been added to the request to authenticate our users. This is similar to how ISAPI and federated single sign-on solutions work, by injecting a token into the header that can be inspected on the request. For this example we are going to use a token, AuthToken, that is provided in the header to ensure that the request is coming from the correct place. The header will also use a username token, UserToken, that will be validated against a authentication database. Needless to say, this type of scenario begs for the use of SSL, so make sure that it is configured properly. To ensure authentication is working it may be best to test it using straight HTTP first, and then add SSL later once you have it setup and running.
Authentication to reporting services requires using the Report Server web service and calling LogonUser(). If that call succeeds it returns an authentication ticket we can add to the response which will be used in subsequent requests. There is some common plumbing for validating the request information and the user that will be different depending on your scenario. We’ll abstract that out into a class called the ValidationManager, so let’s start there.
Again, the logic here will change depending on how the user needs to be authenticated or validated. In this case we are doing several things. First we validate the headers AuthToken and UserToken are present in the request and check that the AuthToken token provided in the header matches with what we expect. The IsValidHeaderHash method provides this validation by comparing the value to one in the web.config file. This tells us that the request went through the proper channels before getting to us. This token could be stored anywhere, but for simplicity we are storing this in the web.config file. The next step is to parse the user token in the header and check our database for the user. If that is successful in combination with the first step we consider the user authenticated and return the result to the calling code. The core of the ValidationManager is shown below.
1: /// <summary>
2: /// validates that the user is valid based on values in the header
3: /// in the form of a hashed value and username
4: /// </summary>
5: /// <param name="headers">headers from the http request</param>
6: /// <returns>true if a valid user account with correct header info</returns>
7: public bool ValidateUserInfo(NameValueCollection headers) {
8:
9: bool isValid = false;
10:
11: //are our headers present?
12: if ( headers != null && headers.Count > 0 ) {
13: if ( headers["UserToken"] != null ) {
14: //need to validate the header information related to the request
15: //expecting a header with AuthToken
16: //need to validate the user is a valid one in the database
17: //expecting a header with UserToken
18: isValid = ( IsValidHeaderHash( headers["AuthToken"] ) && IsValidUserToken( headers["UserToken"] ) );
19: }
20: }
21:
22: return isValid;
23: }
24:
25: /// <summary>
26: /// validates that the user exists and matches in the database
27: /// </summary>
28: /// <param name="userName">username to validate</param>
29: /// <returns>true if user exists and matches provided username</returns>
30: private bool IsValidUserToken(string userToken) {
31: //assume not valid
32: bool isUserValid = false;
33:
34: if ( userToken != null && userToken.Length > 0 ) {
35:
36: string sql = "SELECT userName, [role] FROM [dbo].[UserSchema] WHERE @username AND [role]='Reports'";
37:
38: //create parameter
39: SqlParameter pram = new SqlParameter( "@username", SqlDbType.VarChar, 256 );
40: pram.Value = userToken;
41:
42: SqlParameter[] prams = {
43: pram
44: };
45:
46: //check the database
47: if ( DataExists( _connectionString, sql, prams, "userName", userToken ) ) {
48: _validatedUserToken = userToken;
49: isUserValid = true;
50: }
51: }
52:
53: //return
54: return isUserValid;
55: }
56:
57:
58: /// <summary>
59: /// Validate the header auth token to ensure the request is valid
60: /// </summary>
61: /// <param name="authKey">header authetication token</param>
62: /// <returns>true if valid</returns>
63: private bool IsValidHeaderHash(string authKey) {
64: //assume not valid
65: bool validHeader = false;
66:
67: if ( authKey != null && authKey.Length > 0 ) {
68:
69: if( authKey.Equals(ConfigurationManager.AppSettings["F5AuthKey"]) ) {
70: //hash validated
71: validHeader = true;
72: }
73: }
74:
75: return validHeader;
76: }
77:
78:
79: /// <summary>
80: /// runs the specified query and tests to see if the data returned matches the value to test
81: /// by checking to see if it contains the value
82: /// </summary>
83: /// <param name="connectionString">connection string to database</param>
84: /// <param name="sqlString">sql to run</param>
85: /// <param name="prams">parameters to pass</param>
86: /// <param name="columnToTest">column to validate against</param>
87: /// <param name="valueToTest">value to test against column</param>
88: /// <returns>true if the value matches</returns>
89: private bool DataExists( string connectionString, string sqlString, SqlParameter[] prams, string columnToTest, string valueToTest) {
90: bool dataExists = false;
91: //connect to store for configuration
92: using ( SqlConnection conn = new SqlConnection( connectionString ) ) {
93:
94: //run command to validate account name
95: SqlCommand cmd = new SqlCommand( sqlString, conn );
96: cmd.CommandType = CommandType.Text;
97:
98: //create parameter for account name
99: if ( prams != null ) {
100: foreach ( SqlParameter pram in prams ) {
101: cmd.Parameters.Add( pram );
102: }
103: }
104:
105: try {
106: conn.Open();
107: using ( SqlDataReader reader = cmd.ExecuteReader() ) {
108: //If a row exists for the user, then assume user is valid
109: //but to be safe we want to explicitly check for a match, rather than assume
110: if ( reader.Read() ) {
111:
112: if ( reader[columnToTest].ToString().Equals( valueToTest ) ) {
113: //data valid
114: dataExists = true;
115: }
116: }
117: }
118: } catch ( Exception ex ) {
119: throw new Exception( ex.Message );
120: }
121: return dataExists;
122: }
123: }
With the plumbing work done we can turn our attention to the Report Manager. Report Manager is just a UI that sits on top of the Report Server web service. All information is passed through to the report server. We are going to allow reporting services to redirect us to a UILogon.aspx page if the user isn’t properly authenticated with the authentication ticket. In the Page_Load event of this page we’ll call our ValidationManager to ensure that the request contains the proper headers and the user exists in our database. If that succeeds then we’ll call the ReportServer LogonUser method. The logic is shown below.
1: private void Page_Load(object sender, System.EventArgs e)
2: {
3:
4: ValidationManager mgr = new ValidationManager();
5: if (mgr.ValidateUserInfo(Request.Headers))
6: {
7: ReportServerProxy server = new ReportServerProxy();
8:
9: string reportServer = ConfigurationManager.AppSettings["ReportServer"];
10: string instanceName = ConfigurationManager.AppSettings["ReportServerInstance"];
11:
12: // Get the server URL from the report server using WMI
13: server.Url = ValidationManager.GetReportServerUrl(reportServer, instanceName);
14:
15: server.LogonUser(mgr.ValidatedUserToken, Request.Headers["AuthToken"], null);
16: string redirectUrl = Request.QueryString["ReturnUrl"];
17: if (redirectUrl != null)
18: {
19: HttpContext.Current.Response.Redirect(redirectUrl, false);
20: }
21: else
22: {
23: HttpContext.Current.Response.Redirect("./Folder.aspx", false);
24: }
25:
26: }
27: }
Notice the ReportServerProxy instance that is created. This is a proxy class that is used to call the Report Server web service and is responsible for taking the cookie returned in the request and adding it to the response. The code is the almost the same that is used in the FBA example provided by the SQL Reporting Services team. I have abstracted it out into a separate code file and added the two headers for our example to the request so that the Report Server can take care of authenticating the user. The code is shown below.
1: namespace Sample.ReportingServices.Security {
2: // Because the UILogon uses the Web service to connect to the report server
3: // you need to extend the server proxy to support authentication ticket
4: // (cookie) management
5: public class ReportServerProxy : ReportingService2010 {
6:
7: protected override WebRequest GetWebRequest(Uri uri) {
8: HttpWebRequest request;
9: request = (HttpWebRequest)HttpWebRequest.Create( uri );
10: // Create a cookie jar to hold the request cookie
11: CookieContainer cookieJar = new CookieContainer();
12: request.CookieContainer = cookieJar;
13: Cookie authCookie = AuthCookie;
14: // if the client already has an auth cookie
15: // place it in the request's cookie container
16: if ( authCookie != null ) {
17: request.CookieContainer.Add( authCookie );
18: }
19: request.Timeout = -1;
20: request.Headers.Add( "Accept-Language", HttpContext.Current.Request.Headers["Accept-Language"] );
21: if ( HttpContext.Current.Request.Headers["AuthToken"] != null ) {
22: request.Headers.Add( "AuthToken", HttpContext.Current.Request.Headers["AuthToken"] );
23: }
24: if ( HttpContext.Current.Request.Headers["UserToken"] != null ) {
25: request.Headers.Add( "UserToken", HttpContext.Current.Request.Headers["UserToken"] );
26: }
27: return request;
28: }
29:
30: [System.Diagnostics.CodeAnalysis.SuppressMessage( "Microsoft.Usage", "CA2201:DoNotRaiseReservedExceptionTypes" )]
31: protected override WebResponse GetWebResponse(WebRequest request) {
32: WebResponse response = base.GetWebResponse( request );
33: string cookieName = response.Headers["RSAuthenticationHeader"];
34: // If the response contains an auth header, store the cookie
35: if ( cookieName != null ) {
36:
37: Utilities.CustomAuthCookieName = cookieName;
38: HttpWebResponse webResponse = (HttpWebResponse)response;
39: Cookie authCookie = webResponse.Cookies[cookieName];
40: // If the auth cookie is null, throw an exception
41: if ( authCookie == null ) {
42: throw new Exception( "Authorization ticket not received by LogonUser" );
43: }
44: // otherwise save it for this request
45: AuthCookie = authCookie;
46: // and send it to the client
47: Utilities.RelayCookieToClient( authCookie );
48: }
49: return response;
50: }
51:
52: private Cookie AuthCookie {
53: get {
54: if ( m_Authcookie == null && HttpContext.Current != null && HttpContext.Current.Request != null )
55: m_Authcookie = Utilities.TranslateCookie(HttpContext.Current.Request.Cookies[Utilities.CustomAuthCookieName] );
56: return m_Authcookie;
57: }
58: set {
59: m_Authcookie = value;
60: }
61: }
62: private Cookie m_Authcookie = null;
63: }
64:
65: [System.Diagnostics.CodeAnalysis.SuppressMessage( "Microsoft.Performance", "CA1812:AvoidUninstantiatedInternalClasses" )]
66: internal sealed class Utilities {
67: internal static string CustomAuthCookieName {
68: get {
69: lock ( m_cookieNamelockRoot ) {
70: return m_cookieName;
71: }
72: }
73: set {
74: lock ( m_cookieNamelockRoot ) {
75: m_cookieName = value;
76: }
77: }
78: }
79: private static string m_cookieName;
80: private static object m_cookieNamelockRoot = new object();
81:
82: private static HttpCookie TranslateCookie(Cookie netCookie) {
83: if ( netCookie == null )
84: return null;
85: HttpCookie webCookie = new HttpCookie( netCookie.Name, netCookie.Value );
86: // Add domain only if it is dotted - IE doesn't send back the cookie
87: // if we set the domain otherwise
88: if ( netCookie.Domain.IndexOf( '.' ) != -1 ) {
89: webCookie.Domain = netCookie.Domain;
90: }
91: webCookie.Expires = netCookie.Expires;
92: webCookie.Path = netCookie.Path;
93: webCookie.Secure = netCookie.Secure;
94: return webCookie;
95: }
96:
97: internal static Cookie TranslateCookie(HttpCookie webCookie) {
98: if ( webCookie == null )
99: return null;
100: Cookie netCookie = new Cookie( webCookie.Name, webCookie.Value );
101: if ( webCookie.Domain == null ) {
102: netCookie.Domain = HttpContext.Current.Request.ServerVariables["SERVER_NAME"];
103: }
104: netCookie.Expires = webCookie.Expires;
105: netCookie.Path = webCookie.Path;
106: netCookie.Secure = webCookie.Secure;
107: return netCookie;
108: }
109:
110: internal static void RelayCookieToClient(Cookie cookie) {
111: // add the cookie if not already in there
112: if ( HttpContext.Current.Response.Cookies[cookie.Name] == null ) {
113: HttpContext.Current.Response.Cookies.Remove( cookie.Name );
114: }
115:
116: HttpContext.Current.Response.SetCookie( TranslateCookie( cookie ) );
117: }
118: }
119: }
That’s enough for now. In the next part in the series we’ll wire up the authentication and authorization extensions and build some additional code sections that we’ll need. Stay tuned.
Comments
- Anonymous
January 06, 2013
hi guys,i have 1 problem when i deploy my report on share point on that time i give url properly in properties of projectbt still i get thes error and becoz of this m geting stuck from morningTITLE: Microsoft Report DesignerCould not connect to the report server http://infor:81/reportserver. Verify that the TargetServerURL is valid and that you have the correct permissions to connect to the report server.ADDITIONAL INFORMATION:This operation is not supported on a report server that is configured to run in SharePoint integrated mode. ---> Microsoft.ReportingServices.Diagnostics.Utilities.OperationNotSupportedSharePointModeException: This operation is not supported on a report server that is configured to run in SharePoint integrated mode. (System.Web.Services)and becoz of this m geting stuck from morning so plz help me.thanx.....G.K - Anonymous
January 06, 2013
Ganesh,Perhaps this helps.whitepages.unlimitedviz.com/.../deploying-reporting-services-reports-to-sharepoint-using-business-intelligence-development-visual-studio - Anonymous
January 29, 2013
Cliff,Thanks for a very detailed article. I am attempting to use your code with an SSO solution that exists on another server. I am getting stuck trying to have http://servername/reports first go to the other sso site i.e. http://company.com/sso. Where in this sample do I implement this?Thanks,Josh - Anonymous
January 29, 2013
Josh,Check for an SSO token in the login.aspx page. If it exists, continue. If it doesn't, redirect to the SSO provider. Generally this means providing a redirect url so the SSO provider knows how to get your users back to the login page. Once the SSO provider does its work it should redirect back. At this point you have a token to validate and then pass the user through to SSRS. - Anonymous
August 01, 2013
Hi,Does this work with with SRSS 2012? Cause I want it! :-) - Anonymous
August 01, 2013
I haven't tested it, but there is no reason it shouldn't work. Nothing has changed related to the authN engine. - Anonymous
August 14, 2013
Hi Cliff.. Thank you very much for the article. - Anonymous
August 14, 2013
Hi Cliff,I have tried the "Forms authentication sample" and also the sample provided by you. I using SSRS 2012 and woking on 64 bit operating system. I followed all the steps mentioned by you and also in the site "msftrsprodsamples.codeplex.com/wikipage exactly. However, when i try to connect to my report server with url "http://myserver/reportserver I am getting Service unavailable error i.e Http error 503. When i tried to check in "http://myserver/report" I get error "HTTP Error 404.0 - Not FoundThe resource you are looking for has been removed, had its name changed, or is temporarily unavailable". Please help me regarding this. Thank you. - Anonymous
August 15, 2013
@darsonAre you sure the services are running? Check the services MMC and make sure the services were started correctly after your changes. Sometimes in this process the services won't start due to configuration or other issues. - Anonymous
August 15, 2013
Hi Cliff,Thank you very much for your response. The service "SQL Server Reporting Services (MSSQLSERVER)" is up and running. I also tried restarting the other related SQL services and all the services are running.Thank you,Darsan. - Anonymous
November 17, 2013
Hi Cliff,I am trying to implement SSO on SSRS server 2012, by reading your article. Its scenario two for me.All the code/classes/functions what you have mentioned, will it be going to web.config file or do we have create another file.Also token would be generated by the customer for whom we are implementing the SSO, do I need to know how the token would look like in prior.Regards,Sandhya - Anonymous
November 18, 2013
The comment has been removed - Anonymous
January 04, 2015
The comment has been removed - Anonymous
January 04, 2015
@Kennis -- I would validate that the configuration ReportServer and ReportServerInstance values are correct for the instance you have created, as well as the other configuration settings in the web.config. - Anonymous
January 05, 2015
If I configure Reporting Services to authenticate using SAML, will the Report Builder also support the single sign-on with my windows identity?Thank you. - Anonymous
January 05, 2015
@paras ... the client applications (BIDS, Report Builder, SSMS) do not support SAML or LiveID authentication.technet.microsoft.com/.../ff487970(v=SQL.105).aspx - Anonymous
March 03, 2015
I am not sure if this approach works when Login page does not belong to ssrs http.sys of report server.There are couple problems where ssrs is on serverA and sso login page is from web app on serverB - ticket translation and adjusted redirect url.I put work around for this using http module on ssrs side. - Anonymous
March 03, 2015
I am not sure if this approach works when Login page does not belong to ssrs http.sys of report server.There are couple problems where ssrs is on serverA and sso login page is from web app on serverB - ticket translation and adjusted redirect url.I put work around for this using http module on ssrs side. - Anonymous
March 09, 2015
@Kennis - That may be in relation to my question below.@Cliff - Thanks for the article. How do users that are not the admin user get their ACL list set? If I login with an account that isn't the "admin" account they always come up as null for all the ACL checks. I get the same error as Kennis. However, if I login with the same account under: AdminConfigurationUserName it works as expected. - Anonymous
March 09, 2015
@Patrick ... its been a while, but if memory serves me the admin has to create the ACL for the user. Remember in SSRS they have two settings to enable them (System User and then a Role assigned to at least browse). Without these the ACL checks may be null. - Anonymous
April 19, 2015
Thanks for this article!!