Started reading a very interesting article this morning on the importance of coding standards. This article is particulary interesting to me because of the nature of the work that I do on a day-to-day basis: custom software development. Now while working in custom software development I have heard many conflicting thoughts and practices when it comes to standards. In a perfect world, I would agree with the camp that likes to have every developer stick to a certain set of standards and practices. Reality is that deadlines sometimes put a damper on doing things the right way.
How do you cope with this? I hate to start a project using standardized practices and then told to make shortcuts to meet a tight deadline. I know that custom software development is a little more fast-paced than an in-house development job, but I would assume that managers would put a priority on consistency. When the project is done and delivered I don`t care if the person that takes over my job thinks they could write better code. It is more important for me leave them with code that is easy to follow and make adjustments to. Consistency is key.
Some of my "favorite" malpractices:
|SQL Server (table name)
|SQL Server (field)
|.NET (handler name)
I know that there are those of you that would go about things in different ways. Like the article above states, "Show me code written by ten developers and I'll show you ten different coding styles." I think one thing we can all agree on is that the malpractices above are in no way excusable.
Some of you might notice that I use
Id instead of
ID. I have seen that the field seems to be split on this. I like to think of
Id being a common enough convention that one not need to capitalize both letters, much like
What are your thoughts?
I have been knee-deep in SQL Reporting Services for the past week and have worked long hours in creating and configuring reports. Funny thing is that for every 1 hour I spend on editing a report I spend at least 3 hours configuring the damn thing! Needless to say, the past week I have gone through just about every configuration error under the sun and will try to help those of you that are having the same frustrations that I have. My operating system happens to be Windows 2003 Server SP1 but the concepts throughout this post should be fairly universal. I am accessing the SQL Reporting Services from an ASP.NET web application using the rs.render method. These instructions also presuppose that you have published a report to your ReportServer. I am not a SQL Reporting Service expert in any way, shape, or form. At best the following will provide a starting point in troubleshooting similar errors and experiences.
ASP.NET not installed or is not registered on web server
This error pained me for some time while attempting to install SQL Reporting Services Standard Edition. I just ignored it at first and was clueless as to why SRS setup would only install client tools. I traced the problem to the fact that I was using Windows 2003 Server SP1. A little Googling and I found a starting point. Windows 2003 shipped with .NET pre-installed. Well, sort of installed. I had to actually install it by performing the following:
- Click `Start` button > `Control Panel` > `Add or Remove Programs`
- Click `Add/Remove Windows Components`
- Highlight `Application Server`
- Click `Details`
- Make sure `ASP.NET` is checked
- Click `OK`
Now you need to register this version of ASP.NET with IIS.
- Click `Start` button > `Run`
- Type `cmd`
- Click `OK`
- Type `cd C:\WINNT\Microsoft.NET\Framework\v1.1.4322`
- Press `Enter` key
- Type `aspnet_regiis.exe -i`
- Press `Enter` key
This will install and register ASP.NET v1.1.4322 with IIS.
At this point try to reinstall SRS Standard Edition and see if you can bypass the error message. If the error message still appears there is one more thing you can do.
Still Getting ASP.NET 1.1 Error Messages After Registering ASP.NET v1.1?
I had this problem too after registering ASP.NET with IIS. After browsing my installed programs I noticed that I now had ASP.NET versions 1.1 and 2.0 on my machine. After searching the Internet I found that there is an installation log file for SRS that might give some more information.
C:\Program Files\Microsoft SQL Server\80\RS Setup Bootstrap\Log\*.cab
In this file you are looking for the following:
<Prerequisite Name="IsASPNET4322Installed" Checked="Registry" Item="HKLM\SOFTWARE\Microsoft\ASP.Net\RootVer"
Found="Can not get value" Expected="1.1.4322.x" Result="Fail" />
Now simply open Registry Editor (`Start` > `Run` > Type `regedit` > `OK`) and browse to the ASP.NET folder by using the path from above and check to see what (if any) root version you have. Make sure that the `RootVer` value is `1.1.4322.0` and close Registry Editor. Now try reinstalling SRS Standard Edition.
Original savior: Jon Swoveland via Google Groups
Now after successfully installing SRS things should go smoothly right? Well for me: wrong, completely wrong. If you are new to SRS (and don't have networking experience) then you are probably falling into an endless pit of permissions-issues. The following instructions are for setting up SRS in a way similar to my installation.
First you need to disable anonymous access to the ReportServer and Reports applications.
- Click 'Start' > 'Run'
- Type 'inetmgr' > 'OK'
- Click 'Default Website' (could be different depending on your setup)
- Right-click 'Reports' application > 'Properties' > Click 'Directory Security' tab > Click 'Edit'
- Uncheck 'Enable anonymous access'
- Check 'Integrated Windows authentication'
- Click 'OK' > Click 'OK'
- Right-click 'Reports' application > 'Permissions'
- Highlight the user that will access the reports (Most likely Domain\I_USR) - If user doesn't exist Click 'Add' > 'Advanced' > 'Find Now' > Select the users > 'OK' > 'OK'
- Give this user (check) everything but 'Full Control' and 'Special Permissions' (just to be safe!)
- Repeat the steps above for the 'Report Server' application
Now it's time to create permissions in SQL Server. You will need to remember all the users that you added above. While we are on the subject this would be a good time to create a Group (i.e. SQLReportUsers) and add users to it. This way instead of adding multiple users each time you create a new report you could simply add that user (or users) to the Group and they will inherit the permissions. Feel free to configure however you want though.
- Open Enterprise Manager
- Click 'Your_Server_Name' > 'Security' > 'Logins'
- Create new logins for the users you granted permissions to above
- Right-click 'Logins' > 'New Login' > Click '...' button > Select user/users > Click 'OK'
- Make sure 'Windows Authentication' is selected
- Make sure the appropriate domain is selected
- Make sure 'Grant access' selected
- Select 'ReportServer' for the default database
- Click 'Database Access' tab
- Select (check) all the databases this user needs to access (everything the Report Service touches)
- For each database selected make sure that you select at least: 'public', 'db_datareader', 'db_datawriter'
- Open the database that includes the stored procedure used to generate the report
- Click 'Users' > Right-click the login > 'Properties' > Click 'Permissions' button
- Scroll down to the stored procedure object and make sure that the 'Exec' column is checked
- Make sure the user has 'Exec' privileges on any other relevant stored procedures
Time to Configure the Reports in Report Manager
Upon installing SQL Reporting Services there
will should be a folder called 'SQL Reporting Services' in the 'Start' > 'All Programs' > 'Microsoft SQL Server' directory. In this folder you should find the 'Report Manager' shortcut. Opening this takes you to the Report Manager homepage. From here you can edit existing reports, publish new reports, and most importantly: configure report security and user roles. If you are still getting permissions-related issues then this module is where you would hopefully correct the problem(s).
The permissions granted to user 'DOMAIN\user' are insufficient for performing this operation.
That error threw me off for the longest time. I performed all of the operations above and still had no luck. Here are insructions for how I solved this problem via Report Manager.
- Navigate to the report you are configuring by drilling down to the folder where the .rds and .rdl file(s) reside.
- Click 'Properties' tab (top navigation bar)
- Click 'Security' tab (on left navigation bar)
- Click 'New Role Assignment' (top navigation bar)
- Type 'Group_or_Username_From_Above' in the 'Group or user name' textbox
- Select 'Browser' for the role (May view folders, reports and subscribe to reports) > click 'OK'
- Click 'ReportFileName'
- Click 'Properties' tab (top navigation bar)
- Repeat above steps
Update Your web.config File Accordingly
This was the crucial piece to the puzzle for me. I had the authentication section set correctly but forgot to include the authorization section in my web.config! I was impersonating the user correctly but said user wasn't allowed. Don't let this happen to you.
<authentication mode="Windows" />
<identity impersonate="true" />
<deny users="?" />
<allow users="*" />
Related: Authentication, Role-based Security, and SQL Reporting Services Web Service
Hopefully now SRS will work for you from your web applications. Feel free to post questions or e-mail me and I will try my best to respond with a fix or something close to it!
My entry has been submitted! I`ll let you all know what it is after the deadline.