Link to home
Start Free TrialLog in
Avatar of Rick Danger
Rick DangerFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Classic ASP code not working after upgrade to Windows 2008, IIS7

The attached code worked under Windows 2003, IIS6, but my hosting company has had to upgrade to Windows 2008, IIS7. Now I get this error:

Microsoft OLE DB Provider for SQL Server error '80040e4d'
Invalid authorization specification
/database/add_visit.asp, line 197

Line 197 is where it tries to execute the insert. All my other pages work, but they only perform reads, this page performs an insert. Well, it used to...
Avatar of Big Monty
Big Monty
Flag of United States of America image

are you using a DSN or DSN-less coneection? can you verify that provider you're using is installed on the new machine?
They probably changed the way you need to connect to the db.   Or they also upgraded your sql server and maybe the username/pass did not go through?  Perhaps they used to allow you to connect remotely and now you have to use localhost?
PLease post/attach what is in connections/conweb.asp
Avatar of Rick Danger


To answer the previous suggestion from padas - it still works for other pages, with the same connection strings, so I don't think it's that.

Connection file attached
Avatar of Paul Jackson
Paul Jackson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I don't see anywhere that 'sqloledb' is supported for SQL Server 2008.  I thought you were required to use SQL Native Client.  This is what I'm using in Classic ASP on my Godaddy IIS7 hosting:

connectstr = "Provider=SQLNCLI;Server=" & db_server & ";Database=" & db_name & ";Uid=" & db_username & ";Pwd=" & db_userpassword & ";"

Also see
to add to @DavBaldwin some more options
@padas, that's the first place I checked just in case I was missing something.  'sqloledb' isn't listed there either.
I've tried changing the connection string to this:
conn.ConnectionString = "provider=SQLNCLI;uid=admin_elysian;pwd=elysianpass;connection
timeout=620;database=elysianSQL;data source="

as per DaveBaldwin's suggestion, but it falls over with this:
ADODB.Connection error '800a0e7a'
Provider cannot be found. It may not be properly installed.

As you may have guessed, I am a bit out of my depth with this.

But if the connection string needs to change, how come it works with the other pages?
Who is your hosting company?  They should be able to tell you what drivers are installed since they had to do it.  As far as the other pages, is it the same Exact same connection string to the exact same database?
They are not being particularly helpful actually. They say that all of their other customer's pages have not had a problem.

Every other page with exactly the same connection string works. I have made a further change to the connection string, having read a little about it. The string is now:
conn.ConnectionString = "provider=sqlncli10;uid=admin_elysian;pwd=elysianpass;connection
timeout=620;database=elysianSQL;data source="

But I now get this:
Microsoft SQL Server Native Client 10.0 error '80040e4d'
Invalid authorization specification
/database/add_visit.asp, line 197

But still all of the other pages are still working.
Is it only the insert?  Are their other inserts on other pages working?  Do you have multiple users on sql server? with different permissions?  dbo.sometable vs something.sometable?
It fails on both inserts, but all reads are OK. It is a multi-user web-based database. All have same permissions.
If you open up mssql in server management studio, right click on your database, select properties, select permissions... is there anything checked?  Are there multiple users?  Are you using  something like plesk?
We are using Plesk - I am now thinking that this is a database permissions problem.Would this be why I can perform reads but not inserts?
I seem to have lost my audience...

However, if I insert a record directly in the database, via SQL Server, it works. If I try to insert a record via my .asp pages, it falls over. This is beyond my understanding, but is this significant?
Yes, if they made some updates and updated plesk as well, you probably need to update your username/permission via plesk.  Something probably is out of sync with plesk.
The solution, I eventually found out from my hosting company, is as follows:

conn.ConnectionString = "Provider=SQLOLEDB.1;Password=password;Persist Security Info=True;User ID=userid;Initial Catalog=database;Data Source=datasource"
I've requested that this question be closed as follows:

Accepted answer: 0 points for rick_danger's comment #38190279

for the following reason:

All advice was appreciated but I eventually found the answer myself.<br /><br />Thanks to all who tried to help.
The solution of a possible change in sql server, connection string and permissions was mentioned multiple times in this thread.  I think you should award points.