Using ISA as a Proxy Server in your ASP.NET apps

If the web server your web application or web service is running on is accessing a resource on the outside (a 3rd party web service for example) and it cannot, it could be that the web server your application is running on has an internal IP address. The issue is that it is not being instructed to use ISA as a proxy server. There are a couple of ways of doing this, but one way that has been proven to work is as follows.

The first thing is to change your app's web.config to specify the following section. This should be in a level within the .... Please note that case and spaces are important.


...


usesystemdefault = "false"
proxyaddress="http://YourISAServer:YourPort"
bypassonlocal="true"
/>


...


Once this has been done, you need to tell your Network Admins to setup a rule for allowing your application to pass through the ISA to outside resource. You can either supply them with the URLs of the outside resource or they can monitor the ISA server to see what is being denied. Once the ISA server is configured with the appropriate rules, then you should back in business.

Better SQL Server Security - How to protect your database

I have a database that was created for a custom application. The application may be broken up into multiple tiers or not. All interaction with the data is done using stored procedures. My problem is that I want to constrain my end users to accessing and executing the stored procedures through the custom application and nothing else. That means the user should not be able to go applications like Excel and make a connection to the database and execute one of the stored procedures. And keep in mind my users must authenticate and be authorized in SQL Server using their Windows identity for security and auditing purposes.

I have read posts on the Internet saying that Logon Triggers are a great way to prevent people from connecting to databases using “unapproved” software applications. The application would specify a Application Name in its connection string (Application Name=MyCustomApplication). A logon trigger can be written and the app_name can be evaluated and only allow the applications you ever wanted to connect to the SQL Server, thus eliminating Excel. While this is all fine and good, this isn’t 100% foolproof. Rouge EXEs could spoof an application name that is on the good list of EXEs and circumvent the logon trigger’s purpose.

I have also read that using a loginless account is one way to prevent users from accessing a database directly using their Windows identity. For example, let’s assume you have stored procedures written and you grant the loginless account rights to execute the procs. As long as the EXECUTE AS ‘loginless account’ is executed prior to executing the procs, then this works pretty well. The end users don’t have rights themselves to execute the procedures, but as the loginless used for the impersonation does. The problem I have with this approach is that you are requiring your application code to execute that EXECUTE AS statement before anything else is executed. And because the EXECUTE AS is scoped for the session/module, each time the application were to execute a procedure, it would have to first call the EXECUTE AS and do so over the wire (problem if you aren’t using SSL).

I also read about using signed procedures. In this case a certificate user is granted access to the tables themselves. And then the stored procedures are signed with the same certificate. Then the end users are permissioned to the stored procedures. But I don’t see how it is relevant because it seems this is addressing the issue of where ownership chaining is not possible. The users would still have the ability to execute the procedures from any application they wanted to.

So my problem is there is no good surefire way of allowing a Windows authenticated user to a database and only to the database if the user is using the application the database is intended for. There just seems to be no good way to handle this. There are of course things like Application Roles, but if you have spent any time with them, you know they are pretty much worthless in an enterprise class application. It seems in SQL 2005, Microsoft started to make improvements towards handling this issue, but it is still appears to be half baked.

Have you figured out something that I have yet to read about? Are you the guy/girl that has the answer? If you aren’t, have you thought about the possibility of users accessing your database other than using the application it was intended for?

To me, it almost seems as though the ultimate solution to this problem is to sign the application code and then in SQL Server, per database, say what certificate is allowed into this database. That could be the certificate of a user or a certificate of the application. By doing the user and application would people like DBAs could still get to databases using any application and end users could access the databases only with the “approved” applications.

Thoughts?

Global.asax Session_Start code not running

We were moving an ASP.NET 2.0 application from IIS 6 to IIS 7 and we ran into a problem. The code in the global.asax Session_Start was not running. However, this code had been running fine in every environment (development, testing, production) in IIS 6. Through a lot of research and trial and error, we figured out a solution to the problem. I’m not sure the problem was necessarily with IIS 7 because we saw posts on the Internet of the same symptoms on IIS 6.0. It should be noted that when the code is executed within VS, it ran fine too.

Our standard has been to use the Web Deployment Project for creating the compiled output that is then copied to a IIS server. Our standard has also been to merge all outputs to a single assembly. When this is done, all compiled code is put into a single DLL. If you use the global.asax, there is file called App_global.asax.compiled that is used to tell the runtime engine to reference the single DLL for the global.asax code. It would appear that when you have your project compiled into a single DLL, this problem manifests itself.

Our solution was to change the way the code was to be compiled. Rather than merging all outputs to a single assembly, we set it to merge all pages and control outputs to a single assembly. Do so still creates the App_global.asax.compiled file, but instead the compilation of the global.asax code is into its own DLL (App_global.asax.dll). By doing this the code in the global.asax started working.

Job hangs when running SSIS package

I had a SSIS package that I modified and put out onto a development server. When I ran the existing job, the job would hang. Based on troubleshooting, it would appear the package would not even begin to start. When the package was executed from the development PC, it ran fine. When remoted into the SQL Server and ran the package in BIDS, it ran fine. However, when we ran in a command line the command that was in the job step, it would produce the following error. Note that the package would in fact run though under this method.

The file name is not valid. The file name is a device or contains invalid characters.

It turns out the problem was the fact that the package used a configuration file and the config file specified the location of where the checkpoint file should be written. The config file on the server had the checkpoint file location as the developer’s workstation and not of the location for the development environment. Upon changing the config file, the job would then run the package successfully. It would appear that when this type of error is encounter in a job, the job would hang and not handle the exception correctly.

Kerberos and SharePoint

Great link on Kerberos and SharePoint. Probably one of the best descriptions of how Kerberos really works.
http://www.sharepointjoel.com/Lists/Posts/Post.aspx?List=0cd1a63d%2D183c%2D4fc2%2D8320%2Dba5369008acb&ID=2

Quickly adding data to SharePoint List

I was working on a SharePoint project where I needed to quickly add 100’s of list items to a list. The list was not your ordinary list. The list had the following column types.

Text
Person or Group (Show Name field)
Person or Group (Show Account)
Yes/No (default Yes)
Lookup (based on a “division” list)

This list was to store a list of approvers for a workflow application I was working on. The Person columns were used to get the Display Name and the account name of the approvers (keep in mind we use Active Directory for our user store). The Yes/No column indicated whether or not to email the user. And the Lookup column was for identifying what division the person worked for. When it came time to populate this list, the users did not want to have to type in 100’s of people into this list. So they asked me to put them in. Lucky me.

Since I’m lazy, I wanted a quick way of doing this. First, I thought about doing it programmatically, but again, I’m lazy. So, there had to be a better way. Why can’t I just use SharePoint’s native functionality? Please know that what I’m about to describe took me the better part of half a day to figure out. But once I had the process down, it only takes a few minutes to accomplish what I needed to do.

Step 1: Clean Data
The users gave me a spreadsheet of the people they wanted added. And if your users are like mine, the spreadsheet contains data that is not very conducive to “importing”. It is always dirty and never contains all the data you really need. Since I knew that I would be adding users that are in our Active Directory, I did tell them I needed to have a unique way of identifying people. Luckily, we store our employee numbers in Active Directory, therefore, I was able to bounce the list against AD to obtain the approvers’ Display Name and Account ID. Hopefully, you have similar environment where you can easily do this. Once I had the AD data I needed, I continued in Excel and removed duplicates and so forth. Tip: learn about Excel’s VLOOKUP function. Its great!

Step 2: Build an “Import Spreadsheet”
Once I had clean data to work with, I turned to how I was going to get the data into the SharePoint list. I knew the list had the following columns.

Title
DisplayName
Account
EmailBackup
Division

So I went into Excel and created a sheet that had the same column names, mapping the clean data accordingly. For the Title column, I just entered “Approver” for all the rows. For the EmailBackup column, the users indicated which approvers should get an email. Therefore, using some Excel functions (IF and LEN functions), I populated this column with either a value of “Yes” or a blank value. For the Division column, I had a value of the Division for the approvers I was importing. The key here is to make sure the value matches that of one in the list the defines the lookup column. Once done, I had what looked like a clean and complete Excel spreadsheet that matched my SharePoint list.

Step 3: Importing data into SharePoint
When you look at a list, you have under the Actions menu a option called Edit in Datasheet. This is the key to doing what I needed to do quickly. But there are some problems with doing this. To actually get your data from the Excel spreadsheet into the list, all you really have to do is copy all columns/rows from the spreadsheet and paste them into the Edit in Datasheet view. Simple, simple! But, because of the column types, this becomes very problematic. Most notably, the Person or Group column is the biggest problem! When you try to paste, you may get an error similar to the following.

Cannot paste the copied data due to data type mismatches or invalid data. Some source rows which would have created new rows contained invalid values in required fields. These rows were skipped.

Say what? Okay, to save a lot of Googling, what this really means is you have data that cannot be used. And as I already clued you into, the columns that are defined as People or Group are the problem. The reason is because of how the Edit in Datasheet view works with these columns. I don’t have the 100% accurate explanation of this, but it appears that SharePoint gets from its “user store” all possible users and puts them into a dropdown list. If you are in the Edit in Datasheet view and dropdown a Person or Group column, you will see what I mean. So, what is happening is you have users in your spreadsheet data that is not in the SharePoint “user store”. Hence, invalid data.

Step 4: Fixing the Invalid Data error
So, how do you fix this? Easy, return to Excel for a quick moment. Create a new column in your spreadsheet. Using the CONCATENATE function, append a semicolon onto the end of either the user Display Name or Account (keep in mind, this is the data I got from AD). Now that you have a list of users with a semicolon at the end, copy that column and return to SharePoint.

Go to the Site Settings of the site where your list is and go to People and Groups. Create a new group, naming it so that it sticks out like a sore thumb. Now, add users to this group. In the Users/Group textbox, do a paste. What should happen is everything you copied from the spreadsheet (users with an semicolon appended to the end) should have pasted. Uncheck the “Send email” option and click Ok. The group should be populated with all of the users. Great. Now, delete the group.

This is the magic operation to get Edit in Datasheet People or Group dropdown list populated with the values you need!

Step 5: Finally, Populating the List
Return to your list and change back into Edit in Datasheet mode. Now you should be able to copy and paste your Excel data directly into the Datasheet, without error. It is as simple as that!

Validation of viewstate MAC failed as a result of 3.5 SP1

On the eve of installing the 3.5 SP1 framework on our production IIS servers, I uncovered a problem on our development server. Today I went to use a rather old application 2.0 on DHSIISD1 for a totally unrelated note and found that it was getting a runtime error. The error that was being produced was as follows.

Validation of viewstate MAC failed. If this application is hosted by a Web Farm or cluster, ensure that configuration specifies the same validationKey and validation algorithm. AutoGenerate cannot be used in a cluster.

After spending the past 4 hours trying to determine the root cause, I found that it has to do with the installation of the 3.5 SP1 Framework. How I determined it is what introduced the problem is because I could run the ASP.NET app without error locally on my PC (it also runs in production right now). I then installed the 3.5 SP1 Framework on my PC. After the installation the application running on my PC begin exhibiting the same symptoms. Then I began reviewing what changes were being addressed in the 3.5 SP1. There was a feature put in that allows you to set a form’s Action (HTMLForm.Action) during runtime for example. It is my belief that this is what broke the app. Actually, “broke” is rather subjective. There are a few things to make this stop working.

First, the page in question had controls that caused post backs to occur. The page also had a HTML form defined and the form has an action set.

Because of the HTMLForm.Action change, the form’s action was now being honored where as before, it was not, it was just ignored. As a result, when a control on the form caused a post back, the post back caused the framework to honor the form’s action and route to the confrimpage.aspx. As a result, that page tried to process the viewstate and it was not valid because it came from a different page. Thus, the error being generated. I have read people reporting this as a bug to Microsoft. In their case the symptoms were different. I added to the bug report our experience.

Now, you have to go back to the question of why did a 3.5 SP1 affect a 2.0 application. The bottom line is it should not have (the whole reasoning behind frameworks). But it would appear that 3.5 SP1 does more than just a service pack for the 3.5 framework. It also installs a SP2 for the 2.0 framework. If you look at the MSDN article for the HTMLForm.Action, it says that it is supported in 3.5 SP1, 3.0 SP2, 2.0 SP2. So, it looks to me that they also introduced this feature into the 2.0 SP2 as well. Which by the way is not a distributable you can just download.