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.