My diary of software development

Archive for October, 2009

Thinking about running ASP.Net alongside an existing ASP script reporting site

A potential client has an existing web report site coded in legacy ASP script. They want to port the application to ASP.Net but do not want to take on the risk of doing this all at once. So I was asked to write up something quick and short about doing something like this which our sales people could take to them. The information which I put together is below. I’ve x’ed out the name of the potential client to protect the innocent and all that:

I understand that the current XXXX reporting site is too mission critical to allow a whole scale conversion to ASP.Net but that they do want to migrate it to ASP.Net. So, one way to reduce the risk involved in conversion to ASP.Net is to run the ASP script site side by side with an ASP.Net site while transferring components from ASP script to ASP.Net in a staged timeline.


I do not have architecture specifics for the XXXX ASP script reporting site to give details on how to do this, but most web reporting sites contain the following common components:

1.       A Security token to indicate the user has been authenticated and authorized to access the site.

2.       A connection to the reporting database.

3.       Report query criteria.

4.       Report data.

5.       Control information.


I’ll discuss each of the above components with regard to how a dual Asp script / ASP.Net site may work with them.


Security Token

The security token is an indicator that the current user has been authenticated by the application and is authorized to use it. This token is normally stored in session state although some sites may store it in a web cookie.

There are at least two recognized solutions to allow session state interoperation between ASP script and ASP.Net:

1.       Storing the session state in a common state server. The ideal state server would be a SQL Server database which contains all of the active sessions. In order for the sites to distinguish their session state from another session state in the database, the two sites would maintain a cookie key and use it to extract their appropriate session.

2.       This solution requires two special pages:

a.       An ASP script form to extract session objects and write them to a dummy form as hidden input types. This dummy form would have an ACTION pointing to the ASP.Net page described below.

b.      This ASP.Net page would iterate through the hidden input types written above and store them into the ASP.Net session object.


If the security token were contained in a cookie that cookie would have a very near term expiration and it would be simple for both the ASP script and the ASP.Net site to access the same cookie.


Reporting Database Connection

There would be no value gained from trying to share the same database connection between the two sites. Although at a deep level, the connection object is simply a series of network TCP packets sent to and from the SQL Server, at the level the web sites use the connection, it varies greatly. ASP.Net uses an ADO.Net connection and ASP script uses an ADODB connection.


 Report Query Criteria

This is information such as the date range and additional query criteria chosen by the user to extract their report data. Usually, the user is presented with a single page where they enter this criteria and then push a button which submits the page and returns their report data. This operation, where the criteria data is submitted and a report page is returned could be performed quite easily between an ASP script page and an ASP.Net page or vice versa. The data would be transferred between the two using the request header of the page.


Report Data

Sharing the report data between the sites is probably one of the trickiest things to do and I don’t believe there would be a need to do it. One use for it that I can think of is for a drill-down report. Perhaps levels one and two of the drill down report would be implemented with ASP script but levels three and four would be done with ASP.Net. It is my opinion that if a situation arose where report data needed to be shared between the two sites, that entire report should be migrated to ASP.Net.


Control Information

Control information is the type of information that glues the pages together so that they form a single consistent experience for the user. For example, it may contain the user’s preferences, or their navigation history so that they can easily navigate back, or it may contain their security role which allows them access to a filtered set of reports. This information is best stored in the database, the session, or cookies. All three of which can be easily shared between the sites.

Figuring out why SAS BI Web Services for .Net doesn’t work Part 2

Well, in my last post, I basically couldn’t get the WebServiceMaker.asmx service to work. I now have it working and the problem was because it was not installed properly. You see, SAS is like a bucket of Lego bricks. It contains dozens and dozens of modules and services which can be installed in what seems like endless permutations of arrangements. These arrangements are defined by an XML ‘plan file’ used by the SAS deployment wizard and  SAS support had sent me a couple of plan files which ‘appeared’ to work but did not. There are also some pre-defined plan files provided by the installation download which didn’t build the exact Lego castle I needed but got close. So, I created my own plan file from the plan files provided by SAS and these pre-defined ones. I got everything installed fine and I am able to call the services within WebServiceMaker.asmx without trouble but, given that there is precedence in the world of SAS for gettting an installation with no errors but not necessarily working, I worry.
I still can’t call ListWebServices in the WebServiceMaker.asmx file from my .Net console app but the Enterprise Manager on the SAS server can call it without trouble. I verified this using netmon on the server. Right now I’m at the point where I can deploy a SAS Stored Process as a web service using the Enterprise Manager and then attempt to call it from my .Net console app. When I called my test SAS web service, I got this error:
System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: There was an error due to configuration of the middle tier.
Oh great. Now what? I thought for sure there was some issue with security (which seems to be plaguing me with the ListWebServices call). I poked around online a bit but didn’t really find anything so I decided to fire up Fiddler (such a wonderful tool) and see what my SOAP request and response looked like. Now I have some more information because in the SOAP response I see this information in the SOAP fault:
<Fault code=”4000″ xmlns=”“>
 <Exception message=”Folder path ‘/Users/sasadm/My Folder’ was not found or you may lack permission to view some part of the path.” />


 Well, that *kind of* makes sense. I created my stored process in the above SAS folder and the name of the folder leads me to believe there is one for every user of SAS. I was signed on as SASADM when I created the stored process and I sent the user sasadm in the Username token but maybe the identity attempting to run the service is other than sasadm.
What to do, what to do….
I tried calling the service without providing a WSE UsernameToken and got this response:
Could not establish a connection to the SAS server on the requested machine. Verify that the SAS server has been started with the -objectserver option or that the SAS spawner has been started. Verify that the port Combridge is attempting to connect to is the same as the port SAS (or the spawner) is listening on.

Figuring out why SAS BI Web Services for .Net doesn’t work – Part 1

For my current project, I have to document and define how to call web services on a Windows based SAS analytics server and so I’ve been working on installing a SAS virtual machine server which I can use in my development lab. I battled this installation for several days until I finally figured it out. Well, that’s not really true. I didn’ figure it out on my own, it took a ticket to SAS support to figure out how to install that monolithic beast of 13GB of installation files and laybrinths of documentation from

So now I was at the point where I needed to create a web service on my SAS server. Creating a web service on SAS means that you take a SAS stored proc, not to be confused with an RDBMS stored proc, and use the SAS Enterprise Console to deploy it as a web service. When the Enterprise Console deploys a web service, it will call some services within a web service file named WebServiceMaker.asmx which got installed with SAS. I was excited that I had got to this point because after all, the SAS vendor themselves helped me to install everything I needed. I figured all I needed to do was deploy the SP as a web servce but when I went to deploy, I got this error: Microsoft.Web.Services3.Security.SecurityFault: The security token could not be authenticated or authorized —> System.UnauthorizedAccessException: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).

Well, so much for my faith in things automatically working if I get them installed with the support of the vendor.

I took a look at the WebServiceMaker.asmx and found it contained these 3 services:

  1. ListWebServices
  2. MakeWebService
  3. RemoveWebService

The SAS Enterprise Console indicated it was calling ListWebServices as the first step in deployment so I set about creating a quick .Net console application which called the same service to see what would happen. I knew that WebServiceMaker.asmx was secured with WSE 3.0 and that I wouldn’t be able to call it without passing sometype of credentials but I called it without creds anyway to see what would happen. Of course I got an exception but after researching the error and refreshing my knowledge of WSE, I realized that I needed to pass a UsernameToken in the WS-Security header. I then setup my console app to provide the UsernameToken with some credentials which I assumed would work. These credentials were the sasadm account and password., but I got the same error. Just to make sure I was covering all of the angles, I placed a completely random id and password in the token and called the service again and got the same error.

I then fired up the Fiddler HTTP debugger on my SAS server to get a look at the SOAP request coming out of the Enterprise Console. But I couldn’t get Fiddler to show me the HTTP packets going to and from localhost, even with the tips I found for solving this type of problem during a Google search. So I then installed Microsoft’s netmon tool and was able to get a packet trace of these communications. I had to save the netmon packet trace and open it up with Notepad in order to locate and extract the SOAP request, which Fiddler would have made a piece of cake, but I was able to get it and create an XML file with the SOAP request.

Next I fired up Fiddler on my development workstation and pulled the SOAP request which my console app was sending to the ListWebServices web service. I compared the two requests and found that the two were not syntactically the same but I *think* they were more or less functionaly the same. Here is a list of the diffferences in the two requests:

  1. .Net used a prefix of soap and the SAS EC (Enterprise Console) used a prefix of soapenv. I was sure this was the problem but according to this article: it is probably not.
  2. The SAS EC placed a funky looking id in the element. I had connected to the SAS EC as the sasadm user so I expected to see the text ‘sasaadm’ here but instead it placed this text: element: sasadm@!*(generatedpassworddomain)*! into the element. My .Net console app was placing the text sasadm into this element.
  3. The password being passed from the .Net application was cleartext. So the Type attribute of the element was ‘PasswordText’ . The SAS EC used the same Type attribute but the password was definitely not cleartext, it appeared to be some hexadecimal value. This document: indicates a Type attribute of ‘PasswordText’ may not be an actual password but rather a password equivilant. The .Net application was sending a and a element within the element.

My next blog post will discuss my research of these differences. At this point, I don’t know that this will actually solve anything since, like I said, I believe the two requests are functionally the same and they both produce the same error from calling the web service.

Calling a WSE web service from a SQL Server CLR UDF

My current project is to document and lay the foundation for communicating with web services from several different clients. Well, that’s pretty much a common activity for most developers today but the twist here is that the web services are implemented with SAS Analytics Server v9.2 and one of the clients will be SQL Server 2005. 
I set about putting up a lab to work in consisting of a SQL 05 virtual machine, a SAS 9.2 virtual machine, and my development workstation. Writing a web service client in SQL Server is fairly straightforward: just write a CLR user defined function and install it. Here are some good articles on that:
Implementing web services in SAS is a challenge and I’ve not gotten completely through it yet but I do know that SAS web services are secured using WSE 3.0.  So, while waiting on SAS support to help me get the SAS server up and going, I figured I would write a WSE secured service in .Net and work through the issues of calling in from a SQL UDF. Wahoo! I thought, as I’ve pretty much forgotten how to work with WSE since I picked up WCF. But 1/2 day later I found that web services are like remembering how to ride bikes and learning to ride that multi-wheeled, multi-geared contraption of spokes, levers, arms, and seats out of a Dr. Suess book called WCF didn’t make me forget how to ride my WSE road bike I keep in the garage.
I quickly wrote and deployed an ASMX web service secured with WCF and wrote a simple .Net console client which communicated with it. Great, I thought, now all I have to do is lift my code from the console app and drop it into a SQL UDF and I’ll be done. Alas, I found that a SQL UDF cannot have an associated WSE policy configuration file. You see, normally a WSE client has the luxury of a policy configuration file (by default named wse3PolicyCache.config) to maintain the necessary policies and their assertions for calling the service. However a SQL UDF comes packaged as an assembly .dll so there can be no configuration files associated with it.
When I first began to work through this problem of no configuration file, I thought there would be 2 paths I could take: creating the policies found in the configuration file in code or using the ConfigurationManager class to build a placebo of my console app’s physical configuration file in memory. I tried the second path first and quickly realized that it just wasn’t possible. So next I began to research the first route and stumbled across an MSDN article that did exactly what I wanted – After the heavanly light dimmed back down to normal and the choirs of angels stopped singing, I began to implement it and now I have a CLR UDF in my SQL Server which can call a WSE secured web service.