locked
SQLServer 2005 Express Connection failing in Classic ASP application RRS feed

  • Question

  • User-621602117 posted

    I have a Classic ASP 32-bit application in IIS7 Windows 2008 Web Edition that uses SQL Server 2005 Express.

    The SQL connection works fine if I included the Administrators group in the member properties of the anonymous user, which of course is suicidal. But if I remove Administrators from the anonymous user, the application fails in Global.asa.

    The connection string uses named pipes (the entire server/database is on a single computer):

    connectionstring=Provider=SQLOLEDB;Network Library=dbnmpntw;Data Source=DSVR0xxxx\SQLEXPRESS;Initial Catalog=<catalogName>;User ID=%%uid%%;Password=%%pwd%%

    The group/users with permissions for SysWOW64\dbnmpntw.dll (which I assume to be the named pipes library) are SYSTEM, Administrators, Users and TrustedInstaller.  The properties dialog for dbnmpntw.dll won't let me make other changes to the permission (specifcally add the anonymous account) - but as this is a member of the Users group and the Users group has Read & Execute rights for the DLL that shouldn't be needed, methinks.

    The anonymous user appears to have adequate rights throughout the application.

    Any tips or clues about fixing the permissions for the anonymous user would be appreciated.

    Thursday, February 9, 2012 2:57 PM

Answers

  • User989702501 posted

    Mmm... line 1137 is in global.asa. I thought IIS will log entries for global.asa or any include files, at least that what's I have been troubleshooting in IIS6.

    so with send detail asp error msgs enabled, do you get more info from browser end?

    line 1137 in global.asa not helping?

    • Marked as answer by Anonymous Tuesday, September 28, 2021 12:00 AM
    Monday, February 13, 2012 3:14 AM
  • User-621602117 posted
    Thank you, Bernard (and others). As thought, the problem was permissions, but not for SQLServer - there was an ini file that needed modify permissions, and I'd tried to set these through the right pane of explorer rather than the left (foder) pane, which only confused the issue (I guess MS have a reason for making the settings different between panes).  And I had to figure the ASP code, which took a bit of learning, and then I got diverted, so have been a bit slow to acknowledge assistance.  Mio culpa.
    • Marked as answer by Anonymous Tuesday, September 28, 2021 12:00 AM
    Monday, March 5, 2012 5:08 PM

All replies

  • User989702501 posted

    what's the error msgs when it failed to connect?

    for access related issue, try procmon to trace it.

    Thursday, February 9, 2012 8:12 PM
  • User-621602117 posted

    Thanks, Bernard.

    After many hours trawling through procmon traces, I have found nothing that I can recognize as a problem.

    The only error message I see is the standard 500 error (or the non-friendly version on the server which refers me to the server administrator).

    Nothing is recorded in the failed request log.

    The server log file reports:

    ...|800a0046|Permission denied - <my IP address> etc...

    Clearly this is a permissions issue, as the site runs fine if (and only if) the anonymous account has Administrator group membership. As far as I can see, the anonymous user has all the necessary rights, except (evidently) for setting up a dynamic connection to the SQL Server Express 2005 database (which an administrator can do). As Windows Server 2008 inhibits the addition of permissions in program code areas, I'm a bit stumped.

    Saturday, February 11, 2012 7:45 PM
  • User989702501 posted

    Procmon can't find it?

    can you post the full error msgs and the iis log entry?

    Saturday, February 11, 2012 11:40 PM
  • User-621602117 posted

    Procmon can't find it?

    Probably more correct to say that I couldn't find anything useful.  My strategy was to run in the error condition, then with admin rights given to the anonymous account, and look for any differences (watching one condition at a time to try and avoid information overload!).

    • Watching mssql.exe, nothing at all was recorded when calling the site when the anonymous user did not have administrator rights. Zip.
    • Watching w3wp.exe,  the error condition generated 5700+ lines, and the successful condition generated 10,700+ lines.  Up to the point of the 500 error, the traces looked pretty much the same.  The last lines of the 500 error trace looked pretty normal.
    • I couldn't find anything else in procmon that threw any light on the problem (but then I'm not a 2008 server or IIS7 guru).

    can you post the full error msgs and the iis log entry?

    Problem is, I don't see any error messages other than the 500 error in the browser (or a bland message referring me to the administrator when running on the server). There's nothing in the failed request log (which I know is working because I do have a 404 error recorded for a missing favicon.ico file).

    So there's nothing to post.

    Where it seems to me that this is failing is in the Global.asa file, which is pretty comprehensive - 1500 lines of code - and if the SQLOLEDB connection string fails during the classic ASP application's initialisation (which it doesn't when run with administrator permissions), then the application kicks out a 500 error state. So as far as IIS7 is concerned, everthing appears to be working OK.

    So the only sign of an error condition that I can see is the single entry line in the web log:


    #Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) sc-status sc-substatus sc-win32-status time-taken
    2012-02-12 00:11:26 217.174.248.181 GET /default.asp |1137|800a0046|Permission_denied 80 - 121.212.249.184 Mozilla/5.0+(compatible;+MSIE+9.0;+Windows+NT+6.0;+Trident/5.0) 500 0 0 686

    ...compared to the first logged line of a successful access:

    #Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) sc-status sc-substatus sc-win32-status time-taken
    2012-02-12 00:42:10 217.174.248.181 GET / - 80 - 121.212.249.184 Mozilla/5.0+(compatible;+MSIE+9.0;+Windows+NT+6.0;+Trident/5.0) 200 0 0 1060
    2012-02-12 00:42:10 217.174.248.181 GET /html/banner.htm - 80 - 121.212.249.184 Mozilla/5.0+(compatible;+MSIE+9.0;+Windows+NT+6.0;+Trident/5.0) 304 0 0 390

    Am I missing something here?

    TIA

    Sunday, February 12, 2012 1:12 AM
  • User744767459 posted

    Hi,

    2012-02-12 00:11:26 217.174.248.181 GET /default.asp |1137|800a0046|Permission_denied 80 - 121.212.249.184 Mozilla/5.0+(compatible;+MSIE+9.0;+Windows+NT+6.0;+Trident/5.0) 500 0 0 686

    The 1137 is line number of the code in default.asp causing 500 error. As you enabled Anonymous Authentication, please make sure the account used for anonymous access(IUSR by default) has permissions to connecting database.

    In addition:

    Classic ASP Script Error Messages No Longer Shown in Web Browser by Default

    http://learn.iis.net/page.aspx/564/classic-asp-script-error-messages-no-longer-shown-in-web-browser-by-default/

    Hope this helps, thanks.

    Sunday, February 12, 2012 9:48 PM
  • User-621602117 posted

    Thank you, Leo - that's useful.

    A wee problem in that default.asp is only 100 lines long, so I'll proceed on the assumption that line 1137 is in Global.asa and is effectively read in inline.

    Monday, February 13, 2012 2:44 AM
  • User989702501 posted

    Mmm... line 1137 is in global.asa. I thought IIS will log entries for global.asa or any include files, at least that what's I have been troubleshooting in IIS6.

    so with send detail asp error msgs enabled, do you get more info from browser end?

    line 1137 in global.asa not helping?

    • Marked as answer by Anonymous Tuesday, September 28, 2021 12:00 AM
    Monday, February 13, 2012 3:14 AM
  • User-621602117 posted
    Thank you, Bernard (and others). As thought, the problem was permissions, but not for SQLServer - there was an ini file that needed modify permissions, and I'd tried to set these through the right pane of explorer rather than the left (foder) pane, which only confused the issue (I guess MS have a reason for making the settings different between panes).  And I had to figure the ASP code, which took a bit of learning, and then I got diverted, so have been a bit slow to acknowledge assistance.  Mio culpa.
    • Marked as answer by Anonymous Tuesday, September 28, 2021 12:00 AM
    Monday, March 5, 2012 5:08 PM
  • User989702501 posted

    That's what we called real life experience :) glad you fixed it.
    Yet, the error msgs is really confusing :)

    Monday, March 5, 2012 11:00 PM