5

I've been running classic ASP / SQL Server 2008 R2 / Windows Server 2012 R2 / IIS 8 for about 10-15 years; no problem. But I'm trying to get these same scripts to run on a new machine with Windows Server 2016 (IIS 10) and SQL Server 2017, and am having problems with connections to the databases. (And rewriting them in ASP.NET isn't an option for right now).

For example, I'll create a connection to the database:

set conn = server.CreateObject("ADODB.Connection")  
conn.ConnectionTimeout=120  
conn.Open("Provider=sqloledb;Data Source=(local);Initial Catalog=myDatabase;Integrated Security=SSPI")

First problem, it takes two seconds (exactly two seconds, for every connection) to create the connection. And even though the connection is supposed to stay open, subsequent connections (same db; same connection string) on that page or another page take exactly two seconds (even though I've set the ConnectionTimeout to 120 seconds).

Second problem (more serious), it then takes two seconds (almost exactly two seconds) to do any operation on that connection, even if it's just one row, e.g.:

sql = "update myTable set zipCode = 91839 where ID = 12345"  
conn.execute(sql)

And then if I do another update (for example) on just one row, it takes another two seconds (almost exactly two seconds):

sql = "update myTable set zipCode = 02930 where ID = 67890"  
conn.execute(sql)

With the previous setup (SQL Server 2008 R2 / Windows Server 2012 R2 / IIS 8), it took a couple of milliseconds to create a connection, and then simple updates like those above would take 1-2 ms. But now every connection and every operation on that connection takes two seconds + the amount of time that it should take (e.g. 2 ms).

I'm guessing that somehow the default settings for SQL Server connections in IIS 10 have changed and that's what's causing the problem, but I'm not sure. BTW, I'm using the default IUSR account in IIS to connect to SQL Server (both in the old setup and now).

Dale K
  • 25,246
  • 15
  • 42
  • 71
CorpusProf
  • 51
  • 1
  • 3
  • 2
    wow never thought i would see classic ASP still alive in 2019 having seen it first in 2002. I am grasping straw here, maybe you need to change the connection string into Integrated Security=True. I would also try using Provider=SQLNCLI11. I am thinking maybe it has to do with the connection string and most likely be wrong about this, but it's a simple change to try. – Earl Feb 21 '19 at 04:45
  • That was it. THANKS!! – CorpusProf Feb 21 '19 at 05:47

1 Answers1

2

As my comment on the question was correct, I will make it as an answer for everyone else:

Please try the following (each one or a combination of) change on your connection string:

Integrated Security=True
Provider=SQLNCLI11

The SSPI works well with both SQLClient & OleDB that is why it is usually preferred but maybe the compatibility portion makes it slower as it tries to gracefully make everything correct. The provider portion change from sqloledb to SQLNCLI11 forces the engine to use SQLClient as most likely you are using SQL Server, no need to go for compatibility again (let us say switching to Oracle). Compatibility takes a performance hit sometimes.

Reference(s):

What is the difference between Integrated Security = True and Integrated Security = SSPI?

Difference between Sql Connection and OLEDB Connection

Dale K
  • 25,246
  • 15
  • 42
  • 71
Earl
  • 420
  • 5
  • 16