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.

scenario1

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.

authN

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 (21)

  1. Ganesh says:

    hi guys,

    i have 1 problem when i deploy my report on share point on that time i give url properly in properties of project

    bt still i get  thes error and becoz of this m geting stuck from morning

    TITLE: Microsoft Report Designer


    Could 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

  2. Josh says:

    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

  3. 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.

  4. Patrick says:

    Hi,

    Does this work with with SRSS 2012? Cause I want it! 🙂

  5. I haven't tested it, but there is no reason it shouldn't work.  Nothing has changed related to the authN engine.

  6. Darsan says:

    Hi Cliff.. Thank you very much for the article.

  7. Darsan says:

    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&quot; I get error "HTTP Error 404.0 – Not Found

    The resource you are looking for has been removed, had its name changed, or is temporarily unavailable". Please help me regarding this. Thank you.

  8. @darson

    Are 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.

  9. Darsan says:

    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.

  10. Sandhya says:

    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

  11. @sandhya,

    Yes, all code will fetch information from a web.config or reportserver.exe.config file depending on where it is implemented.  For example, the UILogon.aspx page would use the web.config.from the report manager.  Other components will use the report server web.config file.

    I would expect you will need to know specifics about the token so that you can validate it, otherwise you are open to exploitation.

  12. Kennis says:

    Hi Cliff,

    I follow exactly what you mentioned up there, it works perfectly on default instance "MSSQLSERVER".

    However, when i use named instance, i cannot access the report manager with error:

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

    I have checked the log but its dont seems quite helpful:

    ui!ReportManager_0-4!1364!01/05/2015-16:32:24:: e ERROR: Microsoft.ReportingServices.UI.FolderPage+InsufficientPermissionsToRoot: User '' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

      at Microsoft.ReportingServices.UI.FolderPage.Page_Init(Object sender, EventArgs e)

      at System.EventHandler.Invoke(Object sender, EventArgs e)

      at System.Web.UI.Control.OnInit(EventArgs e)

      at System.Web.UI.Page.OnInit(EventArgs e)

      at System.Web.UI.Control.InitRecursive(Control namingContainer)

      at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

    ui!ReportManager_0-4!1364!01/05/2015-16:32:24:: e ERROR: HTTP status code –> 500

    ——-Details——–

    Microsoft.ReportingServices.UI.FolderPage+InsufficientPermissionsToRoot: User '' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

      at Microsoft.ReportingServices.UI.FolderPage.Page_Init(Object sender, EventArgs e)

      at System.EventHandler.Invoke(Object sender, EventArgs e)

      at System.Web.UI.Control.OnInit(EventArgs e)

      at System.Web.UI.Page.OnInit(EventArgs e)

      at System.Web.UI.Control.InitRecursive(Control namingContainer)

      at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

    ui!ReportManager_0-4!1364!01/05/2015-16:32:25:: e ERROR: System.Threading.ThreadAbortException: Thread was being aborted.

      at System.Threading.Thread.AbortInternal()

      at System.Threading.Thread.Abort(Object stateInfo)

      at System.Web.HttpResponse.End()

      at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)

    ui!ReportManager_0-4!a44!01/05/2015-16:32:25:: e ERROR: System.Threading.ThreadAbortException: Thread was being aborted.

      at System.Threading.Thread.AbortInternal()

      at System.Threading.Thread.Abort(Object stateInfo)

      at System.Web.HttpResponse.End()

      at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)

    Do you have any idea? Thanks!

  13. @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.

  14. Paras says:

    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.

  15. @paras … the client applications (BIDS, Report Builder, SSMS) do not support SAML or LiveID authentication.

    technet.microsoft.com/…/ff487970(v=SQL.105).aspx

  16. Sasha Bond says:

    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.

  17. Sasha Bond says:

    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.

  18. Patrick says:

    @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.

  19. @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.

  20. pritikkk says:

    Thanks for this article!!

Skip to main content