Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003

    Unanswered: DSN Connection Problems on ASP Page

    I'm having a problem with a trusted connection on an asp page on Windows 2000 and SQL Server 2000.

    I have a DSN set up on the web server (Basil_BAIC) that is set to windows authentication and is accessing our database server.

    This is the connection code in the page...

    set basilConnection = Server.CreateObject("ADODB.Connection")
    basilConnection.Open( "DSN=Basil_BAIC")

    When you open IE and navigate to this page then in 90% of cases you get the following error...

    Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
    [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    However, when you go back and re-click the link the page works fine.

    Sometimes it works fine first time, but if you close IE and re-open it then it will almost certainly fail first time round again.

    The web server is using anonymous authentication with a domain account that has a login on the SQL server and has all the relevant permissions for the database. As the page ALWAYS works on the second attempt then the SQL permissions must be correct.

    Does anyone have an answer to this.... please... it's driving me mad!!!

  2. #2
    Join Date
    Jul 2003
    Why do you want to use Windows Auth on the DB? For a website using the DSN, that username and password will always be the IIS anonymous user. You should set it to SQL Server authentication, then provide the login username and password that has access to the DB on the SQL Server.

    A better option is to go DSN-less:

    strConnString = "Provider=SQLOLEDB; Data Source=<servername>; Initial Catalog=<database>; User Id=<username>; Password=<password>"
    Set objDataCmd = server.CreateObject("ADODB.Command")
    Set objConn = server.CreateObject ("ADODB.connection")
    Set objRS = server.CreateObject("ADODB.recordset")
    objConn.Open strConnString
    objDataCmd.ActiveConnection = objConn
    objDataCmd.CommandText = "YOUR SQL HERE"
    Set objRS=objDataCmd.Execute ( , ,adCmdText)
    'Work the resultset
    If NOT objRS.BOF AND NOT objRS.EOF Then
      While NOT objRS.EOF
        'Read from the recordset
      'Recordset was empty
    End If
    Set objRS = Nothing
    Set objConn = Nothing
    Set objDataCmd = Nothing
    <servername> = IP Address or Server Name of the DB Server
    <database> = The name of the DB on the DB server you want to bind to
    <username> = The username of a login that has access to the DB
    <password> = The password for the login

    The benefit of going DSN-less, is you don't have to setup a DSN on every web server you have (if this is going to be load-balanced for example). It's also easier to manage in the long run. I usually put the connection construction and destruction in a sub, call it at the start of the page to build the connection, do all of my SQL queries, then destroy the connection at the end of the page. Then stick those subs in an ASP that has just global functions, and include that global functions ASP at the top of all your pages. Now it takes one change to the global functions ASP to change your connection string site-wide.
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Oct 2003
    Yes i support the above reply.
    Instead of using DSN go for the connection string.
    It is more useful.
    We have a std practice of using connecting string


  4. #4
    Join Date
    Oct 2003
    Thanks guys for this, I realise that this is the best method however I work in a particularly strict environment where passwords are not dished out to developers, I've asked them and the thought of putting a DB password in a text file made them turn green. So windows authentication is my only route. The strange thing is that it works fine on all our other ASP pages and we have a complete helpdesk system that works on a dsn and we never get this problem.

    It is the intermittent nature of the problem that perplexes me, I don't understand why within the space of seconds it will fail then work or work then fail.

    I've tried stripping down the page to just the connection open then close and I still get the problem.. I had thought it may have been something to do with my code but!

    Do you know the prcocess that the web server goes through when authenticating in this way?

    Any other ideas?

    Thanks !!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts