Sql Server Reporting services and forms authentication – Part2 - How to implement?

Before continue this post, please read the post understanding the concept forms authentication in sql reports – Part1. The implementation of the process is so simple.

• Microsoft team already given a sample application which has the forms authentication enabled.
• They have written some code which has a security extension with login pages.
• To understand it well or to implement just do this. Go here and download the sql server samples from Codeplex. http://www.codeplex.com/SqlServerSamples/Release/ProjectReleases.aspx?ReleaseId=4000
• The file SqlServerSamples.msi contains all the example types for sql server.
• Download the file and start installing them.
• After installed the file successfully, go to the location where it installed [Usually if it is sql server 2005 then the location would be C:\Program Files\Microsoft SQL Server\90.] and the folder named "samples".
• And in the samples folder we need to consider the sample "Samples\Reporting Services\Extension Samples\FormsAuthentication Sample".
• This sample has all the code we needed.
• Now, before proceed open the Readme file found in the location mentioned above.
• This is the time we actually start the implementation. Please do as is mentioned in the readme file without anything missed.

The above process will take around half an hour to configure everything correct. And if we do everything correct, then you are able to see the report server running using forms authentication.

What to remember while implementing the process?

• The connection string in the sample code. The sample code contains has the connection string as shown below.

  using (SqlConnection conn = new SqlConnection("Server=localhost;" +
                "Integrated Security=SSPI;" + "database=UserAccounts"))

• So be sure you installed the useraccounts database in the default instance of the sql server and the windows account you have logged in has access to it.

Now, you have everything installed and setup the forms authentication in sql server reports.

Hope you enjoyed the post. Please let me know your feedback and any questions if you have.

Sql Server reporting services and forms authentication - Part1

Till now, I know Sql Server reporting services supports only Windows authentication. And of course till now What I did for all clients is the same. The roles or access everything will define using the default report manager site and deploy that on the client environment. But, the current project I am working on is completely different and the main requirement is enabling forms authentication in the sql server reporting services. The story behind is, the client is running different applications based on the aspnetdb [Forms authentication]. So, they want us to run the report server using forms authentication based on the same database. So, how to achieve that? This was the question in my mind till last week. After done research about two days found some nice articles and the way to achieve that. So, I thought of explaining the research, analysis and the way we need to proceed in this article to my readers. So, if you have any requirement like this in future just go ahead and start the work in the same process as explained below. This Part1 describes all about understanding the concept.

To understand the concept:

• Using forms authentication in reporting services: http://msdn.microsoft.com/en-us/library/aa902691%28SQL.80%29.aspx

Here you need to understand one thing that Reporting services in SQL server which comes by default only supports Windows Authentication. There is no default way to make it or turn on forms authentication. To do this, we need to make some custom configurations, changes to web.config and custom coding. But, nothing to worry. It's easy. :)

Reporting services are developed to be extensible. You can write your own extensions and deploy or integrate them to reporting services by making some changes to their config files. So, now we need to concentrate on what is a security extension? Before proceed we need to understand some key points:

• Reporting services uses role-based security system to authorize users.
• Security extension supported is based on an open and existing API.
• Use custom defined authentication or forms authentication if any only if, you need to define different users and roles which is not based on windows accounts.
• Read the security extension API in the documentation given in the above link. This has all the classes and interfaces defined to start coding.

Now, what is the security extension. "A Reporting Services security extension enables the authentication and authorization of users or groups; that is, it enables different users to log into a report server and, based on their identities, perform different tasks or operations."

So from this post I hope you understand what we need to do to enable forms authentication in reporting services.

Sql Server Reporting services 2008 Tablix control

When working with Sql server 2008 reporting services I found a new control named Tablix. [Of course there are plenty of new features] This is a mix of both Table and a Matrix. The main and great advantage of it is when we drag a table or matrix to the report body you can't find table or matrix any more. The report control automatically convert it to Tablix. Now, it's very easy to show the complex and grouping of data with this control.

The interest thing in 2008 reporting services is, When I open report builder 2008, BIDS 2008 I didn't find the Tablix control at all and started researching for the control. Stupidly searched in google like download tablix control ssrs 2008 etc… But, once I drap and drop the table or matrix and right clicked for properties found "tablix properties" in list of properties there. Then understood the whole concept and implemented the reports very fast.

Hope this post will help you understand it. Let me know what you think.

Sql Express instance is not in the list of network servers

Today, I have a requirement to work with SQL Server express. So, I setup a server with Sql server express installed and configured everything we needed. My team has 4 developers and everyone should access the sql server express to run the project. But, when they try to access the sql server express instance the server is not responding and always it results an error. When I open network servers, I found only the instances of my sql sever and in that list I didn't find the new sql server express I have setup on the server nb11. See the below screen.

So, This stop us working together and started finding what's the problem. I logged into nb11 machine and checked all the services related to Sql Server and found Sql Server Browser status is stopped. Because of this, it is not able to expose to external environment or outside of the server.

So, the solution is just start running the Sql server browser. Now, go to list of server in network servers and you started seeing the nb11 express instance there.

Now, the question is what is Sql Server Browser? Simple definition is "Provides SQL Server connection information to client computers."

Hope you like this post and please let me know, if you have any questions.

Report project template in Visual Studio

This is the question most of the people asking. So, just thought of posting it for my readers.

Report project template is associated with the reporting services in the Sql server. This template is  not an addin to visual studio or a specific download won't be available. You will see the template if and only if you install client tools while installing the Sql Server. So, to get that template, open the sql server setup file and select the client tools option from the list of features available. That's it!!! Let me know, if you have any issues to questions.

Disable button in onclick and process the button click event

This is what I need to implement for one of my project. I am using Ajax and if user tries to hit the submit the button more than once, only the very first request should submit to server and all other requests shouldn't make any request. So, for this I need to disable the button when first  time click on button and enable it after processed. But, I faced a problem that when I try to disable the button in javascript of client side click event then the postback event not raising at all. There are couple of ways we can implement this. First, I tried from server side code.

Solution 1:

btnSubmit.Attributes.Add("onclick","javascript:" + btnSubmit.ClientID + ".disabled=true;" + this.GetPostBackEventReference(btnSubmit));

But, this solution won't work in all scenarios and especially if there are validations.

Solution 2:

OnClientClick='javascript:this.disabled=Page_ClientValidate("");' UseSubmitBehavior="false"

For ASP Button the two properties onclientclick and usesubmitbehavior will do magic for us. In onclientclick event I am disabling the button if and only if the validation passed. Go back to my previous post. It describes how to validate the controls in client side and give the result true or false. And because of using the UseSubmitBehavior property it will automatically re-enable the button once the request processed.

Hope this gives you the enough idea. Like to know what you think. 

Data Formatting in sql server reporting services

In Sql server reporting services the formatting the data is pretty much simple as we have plenty of built in options available for percentage, numbers, decimal, currency etc. So, just them to data format and display the data as you want. Below are most commonly use format for the usual reports.

• c – Currency
• d – Decimal
• n – Number
• p – Percentage.

If you want two decimal places in decimal number then in the format option [Text box properties -> format] then the  format should be d2. In percentage if you want two decimal places in percentage then the format is p2 etc.. But, I have tried it some reports, and found not working. Then used the default C# string formats for the data formatting in reports. That is, for two decimal places the format is like 0.00. Then the result data will be two decimal places. Example, 5.67. So, you can try both and use which one is best fit for you.

There are other plenty of formats available and they are completely described here.

http://msdn.microsoft.com/en-us/library/ms252080%28VS.80%29.aspx

This help you to understand the data formatting in SSRS. What do you think?

Fix to Divided by zero problem in Sql server reports

When we work with reports, most of the times we need to do complex calculations and logic. When we do calculations, there are chances of using the division operation. So, need of checking the divided by zero problem every time. So the solution I propose is, write a simple custom function and call it wherever needed. For every report there is a part called Code and there we can write the custom VB code.

Public Function CheckDividedByZero(ByVal param1 As Double, ByVal param2 As Double) As Double
        If param2 = 0 Then
            Return 0
        Else
            Return param1 / param2
        End If
    End Function

So, call this function wherever you are performing the division operation. Hope this will solve your problem and you never face the divided by zero problem any more.

Date formatting in Sql server reporting services

When we work with reporting services, we come across with different requirements to display the date in different formats. So, we need to know the formats we can give to get required value. Sql server reporting services supports different formats as T-sql and C# does. So, I just want to give the valid formats I use to display the date in required format.

=Format(Fields!DateCreated.Value, "M/d/yy") - 3/14/1986
=Format(Fields!DateCreated.Value, "d-MMMM-yy") - 14-March-86
=Format(Fields!DateCreated.Value, "MM/dd/yyyy") - 03/14/1986
=Format(Fields!DateCreated.Value, "M/d/yyyy H:mm") - 3/14/1986 22:10
=Format(Fields!DateCreated.Value, "dd-MMM-yyyy") - 14-March-1986
=Format(Fields!DateCreated.Value, "MMM-dd-yyyy") - Mar-14-1986
=Format(Fields!DateCreated.Value,"dd-MMM-yy") - 14-Mar-86

You can try different combinations of above to get desired output. Hope this helps and love to hear the comments.