Classic ASP – maintaining sessions between secure and non-secure pages

If you use secure (SSL) and non-secure pages on your IIS-powered web site, and are using session variables to hold information about your users, you most likely will find those session variable values disappearing as your user switches from non-secure to secure pages – for example, they are in your online shop, and then click to Checkout and Pay.

The problem is IIS – by default it creates a new sessionId for users when they hit the SSL pages – so any session values you already created against the non-secure page sessionId are lost.

It’s easy to fix, in IIS7 anyhow. In IIS go to the properties for your web site and open the ‘ASP’ properties page. There is an option down the bottom entitled ‘New ID on Secure Connection’. By default this is set to True. Change it to False and click the Apply link.


Switching between SSL and non-SSL can also be a reason users experience a time out, or seem to be automagically logged out in the middle of something.

Twitter API returns “There is no request token for this page”

Part of the fun of being a developer is keeping up with the advances, improvements and quirks in the APIs of the various online services you need to interact with. I spend a lot of time working with APIs from sites like Twitter, Facebook and SalesForce.

Late yesterday users on one of my sites started to report problems connecting their Twitter accounts, and posting to Twitter. The code on the site has been happily working for more than a year, so it felt to me something must have changed somewhere – and most likely at Twitter’s end. The error stated “There is no request token for this page”.

There is no request token for this paget 11.21.14 AM

I traced the issue to the initial OAUTH call to request a token, the first step to actually issuing a full request to the Twitter API. After a little research online I discovered someone else had experienced exactly the same issue. 

The solution is to specify a User-Agent in the POST, for example:

objXMLHTTP.SetRequestHeader "User-Agent", "something"

Note, this is exactly what I added – “something” seems to work fine. I’ve read that Twitter is continuing to tighten up access to their API, this issue only appeared yesterday for me, so clearly the Twitter devs have been tweaking things behind the scenes.

As a sidebar I was led up a small garden path for a short time. I tried pasting the request token URL directly into my browser and was rewarded with a message from Twitter “Failed to validate oauth signature and token”.

I played with this for a while before realising this error is almost always caused by the time clock on your server not being correct – remember, you are passing a time stamp as part of your URL, and Twitter checks that against its own internal time, if you are a little out of sync Twitter will reject the request. The time clock on my server is fine – and of course one then slaps ones forehead and realises that the time that elapsed between running your initial call, generating the URL, and pasting it into your browser is probably enough for Twitter to decide your time clock is wrong.

Using a SQL table function in a join in a query

I think this has to qualify as one of the most useful things I’ve learnt in SQL Server this month. How to to use a table function in a join in a query.

It’s called the APPLY operator, and it only works on SQL 2005 and above. Here’s an example I found:

select a.PersonId, b.Passportnumber,
p.col1, p.col2, p.col3 from Person a OUTER APPLY dbo.fn_Passport(a.PersonId) p

You use CROSS APPLY when the function must return 1 or more rows to retain the Person record. Use OUTER APPLY to keep the Person record even if the function results in no rows.


There’s some real power in this, for example, you can write a query that returns to the first X rows from each category of people, as illustrated by this article ‘Using CROSS APPLY in SQL Server 2005‘.


Reseed a SQL Server identity column

We all use them. But confess rarely need to change the seed on an identity colum in SQL server. Or if I do, it's probably going to happen via dropping and recreating the table. But once in a while it's handy to be able to change the seed.

How do I… Reseed a SQL Server identity column?

  [, [NORESEED | RESEED [, newreseedvalue]]]

Is something I'm tucking away in my goody bag for another day.







Using SSL on your localhost for web site development

SSL is a fact of life for virtually every web developer, and crucial if like me you work on ecommerce projects on a daily basis. Of course we all have an SSL cert on our development and staging servers, along with production, but what about your localhost machine? Wouldn't it be good if that was running SSL as well? And so you can:

Tip/Trick: Enabling SSL on IIS 7.0 Using Self-Signed Certificates

This came up for me because a) I was dealing with a problem that was only evidenced under SSL; and b) because I recently had to do a clean Windows install I'd lost my previous self-signed certificate in IIS7.



The microsoft.ace.oledb.12.0 provider is not registered on the local machine

Every so often I experience problems importing data from Office files (usually XLS) into SQL. Sometimes everything works fine, others not.  I've always suspected it's because I don't actually have Office installed on my Windows part of my Mac, although I do have SQL, SQL Express, and an old copy of Access.

This morning I was importing an XLS with a couple of thousand lines, into SQL 2008. And up popped this message:

The microsoft.ace.oledb.12.0 provider is not registered on the local machine

A quick Google and I found this forum which leads to this Microsoft download page for the 2007 Office System Driver: Data Connectivity Components. I downloaded and installed and now SQL likes my XLS file.


Mobile device IP not necessarily a geolocator

Following on from my post yesterday 'Calculate distance between web site visitor IP and list of locations' I've come across an interesting revelation.

The client was off playing with my new page that establishes the web site visitor's geolocation from their IP number. And he complained because nothing was right.

Turns out he was browsing the page via iPhone. So I set up a test with my own iPhone, and hey presto, the IP geolocator system database thinks my phone is in Sydney. Which is definitely incorrect because it's sitting on my desk in front of me here in Melbourne. I proved the point by switching the iPhone's wifi back on, and now it correctly reports its location as Melbourne.

What I presume is that mobile carriers assign IP numbers to mobile devices from a central place – eg the IP range is probably designated to their head office or datacentre. And thus, like me, your phone can think it's in Sydney when in reality it's in Melbourne.

Of course iPhone Apps can access a phone's coordinates in a different way via the cellular network – hence some applications popping up a message asking you to agree to them accessing your location. That's how cool gadgets like Four Square work.

But it you rely on IP/coordinate lookups then the answer will be wrong.

For the moment I think we'll disable the fancy stuff on the web site for people browsing with mobile devices, but now I'm wondering how we can deal with this revelation.


ISAPI ReWrite Rules on Individual Web Sites

ISAPI ReWrite has been driving me up the wall for the last day or two. I have Version 3 (fully registered) installed on a web server, which has half a dozen individual web sites up and running. I wanted to implement some rewrite rules for one of the web sites, so I used the ISAPI ReWrite Manager interface to edit the .htaccess file in the root of that specific web site and add a rule along the lines of:

RewriteRule /register/([^/]+)/ /register/register.asp\?eventId=$1 [I,L]

I already have this web site running on my development server, and I know this rule works.

The rules didn't work. I moved them up to the top level of the Manager, which edits the httpd.conf file in the installation directory for ISAPI ReWrite. And the rules worked.

I turned on logging by adding this to the httpd.conf file:

#enabling rewrite.log
RewriteLogLevel 9
#enabling error.log
LogLevel debug

The logging clearly showed the .htaccess file being accessed and the rules in that file being run through:

Htaccess process request d:\inetpub\wwwroot\\.htaccess
applying pattern '/register/([^/]+)/' to uri 'register/My-Event-Name/'

But no dice in the browser, just file not found errors. So the rule is being processed, but the resulting URL clearly is not right.

I read through the troubleshooting information on the Helicon web site. Which is when this phrase caught my eye:

Also remember that rewriting base inside .htaccess files by default is different from httpd.conf. ISAPI_Rewrite automatically strips the local directory prefix from the path when in .htaccess or <Directory> section and applies rules only to the remainder. So if your rules start with a '/' character, this should be removed in .htaccess file in the root folder of web site.

This is the first time I've used the registered version of ISAPI ReWrite, in the past I've only used the Lite version, mostly because I didn't need to bring rules down to the level of the individual web site (on this project I will need to because multiple sites will need the same URL/folder combinations).

So the idea that there is a subtle but key difference in the way you construct the rewrite rules between .htaccess at the individual site level, and the top level httpd.conf file has never come across my radar before.

I amended the rule to:

RewriteRule register/([^/]+)/ /register/register.asp\?eventId=$1 [I,L]

Notice there is no forward slash in front of the register/ name. And the redirect worked completely fine.





I love descriptive, intuitive error messages

If you see an error like the following in your web code and SQL Server, what do you naturally think is the problem:

Microsoft Cursor Engine error '80040e21'
Multiple-step operation generated errors. Check each status value.

Well, looks like a database error, given the 'cursor engine' phrase. But a 'multi-step operation'?

I've now discovered that it appears almost always to be you trying to insert a string that's too long for the field.

I love descriptive, intuitive error messages.

Solving ‘Login timeout expired’ Problem on Windows Server 2008

I’ve been helping set up a new web site, we have two servers, all very off the shelf, both running Windows Server 2008, one with SQL Server 2008 as the database sever, the other acting as the web server.

The two boxes sit behind a firewall with only the necessary ports open (eg 80, 21, 443 etc). Windows FIrewall is turned on for both boxes, and on the SQL box we have port 1433 open but restricted to only the internal IP of the web server, so the web application can connect to the database.

The application is talking to the database using a standard connection string along the lines of:

Provider=sqloledb; Network Library=DBMSSOCN; Data Source=[sql server ip]; Initial Catalog=[db name]; User ID=[db user]; Password=[password];

We loaded up the site without major problems, but then started to notice this error occuring at seemingly random moments:

Microsoft OLE DB Provider for SQL Server error ‘80004005’
Login timeout expired

There seemed no rhyme nor reason, one minute you are browsing pages, the next up pops this error, then you refresh the page and all was well.

Clearly it’s a connection error between the web application and the SQL server. I dug into the Windows and SQL logs everywhere trying to correlate entries with the time the error occured, but to no avail.

There’s a fair amount online about this error, although it’s often tied to simple connectivity issues, such as incorrect IP numbers, server address, firewall settings and the like.

Finally I stumbled over this page ‘Troubleshooting: Timeout Expired‘ which gave the clue, and particularly:

‘Either start the SQL Server Browser service, or connect specifying the TCP/IP port number.’

We don’t have the SQL Server Browser service running – it’s not started by default. So I simply added the port number to the connection string and, fingers crossed, everything’s been fine since, eg:

Provider=sqloledb; Network Library=DBMSSOCN; Data Source=[sql server ip],[port]; Initial Catalog=[db name]; User ID=[db user]; Password=[password];

Actually I normally DO have the port number in the connection string – because I fell into the habit for a long time of running SQL on a non-standard port to avoid nasties with 1433 worms and the like in situations where I wasn’t so confident about the firewalling, or where we needed to leave the SQL Server exposed with IP or tunnel protection.