?
Solved

Connecting to SQL Server 2000 with IIS 7

Posted on 2010-01-07
13
Medium Priority
?
762 Views
Last Modified: 2012-06-21
I recently switched from XP Pro to a 64 bit Window 7 machine with IIS 7.
After installing SQL Server 2000 and ennabling ASP within IIS, I'm not able to connect to the database with the same connection string(s) that worked on the XP machine.  I've tried full connection strings and have created a DSN (tested succesfully) but neither are able to complete a connection from within a local asp page.  I think that there may be additional settings within IIS or possibly some "permission" settings that I'm not aware of.

Thank you
0
Comment
Question by:davelt00
  • 6
  • 3
  • 3
  • +1
13 Comments
 

Expert Comment

by:sram868
ID: 26202363
Parts of the original post was wrong because Windows 2000 comes with IIS 5 not IIS 6 and if you are using IIS 7 with SQL Server 2000 may need to use it in IIS 6 mode. And IIS 7 if run in IIS 7 mode does not require IIS to be in x86 that is 32bit mode because you can run both x86 and x64 applications in one IIS 7. And the reason for the problem is Asp.net is using the limited permissions Network service account so you need to Add Asp.net at the server level and in the database or your application will not run. You are having double hop issues so go to your C drive and run aspnet_regiis in you .NET folder in your C drive and use the link below to configure IIS 7 in IIS 6 mode.


C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

http://blogs.msdn.com/webdevtools/archive/2006/09/18/developing-web-applications-on-windows-vista-with-visual-studio-2005.aspx
0
 
LVL 15

Expert Comment

by:Tray896
ID: 26203236
What's the error?
0
 

Author Comment

by:davelt00
ID: 26203727
All is fine until it attempts to make a connection using a defined connection string:
sConnStr = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=localServer"
sConnStr = "Driver=SQL Server;Server=DAVID;DSN=localServer;Persist Security Info=False;"
sConnStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=carnival;Data Source=DAVID"

I've tried both of the above, they are constructed using a .udl file and both connect successfully. "localServer" is a DSN I created pointing at the SQL Server database.

However when used within an .asp page: The error returned is "An error occurred on the server when processing the URL. Please contact the system administrator."

dim sConn sConnStr
Set sConn = Server.CreateObject("ADODB.Connection")  
'' the error appears when attemption to process the next line
sConn.open sConnStr
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 15

Expert Comment

by:Tray896
ID: 26203772
First thing you need to do is change the config so that you are seeing the true error rather than this general error message.  Follow the steps here to turn off friendly HTTP error messages and enable detailed errors in IIS: http://mvolo.com/blogs/serverside/archive/2007/07/26/Troubleshoot-IIS7-errors-like-a-pro.aspx
0
 

Author Comment

by:davelt00
ID: 26204079
Thanks, I did that and restarted the browser.

This is the connectioon string I am using:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Trusted_Connection=Yes;Persist Security Info=False;Initial Catalog=carnival;Data Source=DAVID
This same string connects to database successfully from within a VB app but when used in VBscript on an .asp page generates the following non-descriptive error:

An error occurred on the server when processing the URL. Please contact the system administrator.
0
 
LVL 15

Expert Comment

by:Tray896
ID: 26204433
Oh man, sorry.  I just realized your app is ASP and that link was for asp.net.  Try this and see if you get a better error.

To see the real error, go to MMC and load the IIS Manager snap-in and go to your website. Double click on "ASP" in the Features View tab, drill down into Debugging Properties and change "Send Errors To Browser" to true
0
 

Author Comment

by:davelt00
ID: 26204693
Set "Send Errors to browser" to true and rebooted browser  but still get same error message.
"An error occurred on the server when processing the URL. Please contact the system administrator. "

Again this same code when inserted into a Visual Basic app works correctly.
dim sConn sConnStr
sConnStr="Provider=SQLOLEDB.1;Integrated Security=SSPI;Trusted_Connection=Yes;Persist Security Info=False;Initial Catalog=carnival;Data Source=DAVID"
Set sConn = Server.CreateObject("ADODB.Connection")  
'' the .asp error appears when attempting to process the next line
sConn.open sConnStr
0
 
LVL 13

Expert Comment

by:Springy555
ID: 26205995
Try this -

Change the identity that the application pool for your website uses.  Open the IIS console, right click your app pool on the left part of the screen, choose advances settings, then under identity change this to NETWORK SERVICE account.

Ensure that within SQL 2000, you have the NETWORK SERVICE account there, and it has the correct rights to the database.
0
 

Author Comment

by:davelt00
ID: 26209339
I checked the applicxation pool for the site and it's identity was already set to "Network Service"

Could you tell me how to ensure that the NETWORK SERVICE account is listed within SQL Server 2000.

I've a;so uploaded a brief screen shot of IIS settings for this site

Thank you,
Dave Tucker
IIS.jpg
0
 
LVL 13

Expert Comment

by:Springy555
ID: 26209552
To add this account to SQL, do the following:

Open up SQL Server Enterprise Manager
On the left, expand the groups until you see the Security folder
Under Security folder is Logins.  Right click and select New Login
For the name, type in NT AUTHORITY\Network Service
Select the Database Access tab - tick the database the webserver is using, and give appropriate permissions below
Clock OK to close all the windows.

Try the web page again (might need an IISRESET or restart of SQL)
0
 

Author Comment

by:davelt00
ID: 26209879
Error message now reads:
Microsoft OLE DB Provider for SQL Server error '80040e4d'
Login failed for user 'NT AUTHORITY\IUSR'.
/carnival1/Default.asp, line 15


0
 
LVL 13

Accepted Solution

by:
Springy555 earned 2000 total points
ID: 26209947
Try adding that account as well to SQL.

There might actually be a local security group named something like that too, which you can add.
0
 

Author Closing Comment

by:davelt00
ID: 31674118
Thanks so much for your help.  It was greatly appreciated!!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What is an ISAPI filter?   •      It's an assembly (.dll file) that can add or change the way IIS works.   •      They can be enabled globally for your web server or on a site-by-site basis.   When the IIS server receives a request, enabling the ISAPI fi…
Running classic asp applications under Windows Server 2008 R2 (x64) and IIS 7 is not as easy as one may think. It took me a while to figure it out while getting error 8002801d a few times. After you install the OS you will need to install the fol…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question