SQL DB connection error...

Posted on 2005-04-25
Last Modified: 2010-03-19
Hi All,

I have a webserver (W2k) and a separate SQL (2000 ) server.
I configured ODBC on the webserver and the test was successfull:

Microsoft SQL Server ODBC Driver Version 03.80.0194
Running connectivity tests...
Attempting connection
Connection established
Verifying option settings
Disconnecting from server

I use SQL server authentication and the integrated sql account has public and db_datareader rights.
I created an .asp page to use this ODBC to connect to the database and read all contents of a given table.
Unfortunately when accessing the asp page it returns the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

The contents ( the part which should make the connection) of the page is as follows:
Dim Connect, selectSQL, RecSet
Set Connect = CreateObject ("ADODB.Connection")
Connect.Open "DSN=dsnname"
Set RecSet = Connect.Execute (selectSQL)
Response.Write RecSet("1") & ", " & RecSet("2") & "<BR><BR>"
End If
Set RecSet = Nothing
Set Connect = Nothing

Can you maybe help me on this? It's giving me a hard time....
Question by:Schurink
    LVL 44

    Accepted Solution

    Hi Schurink,

    This is a common problem with integrated security. The problem is this: when you connect from the webserver, the webserver's process is running under a local IIS account regardless of the user logged into the server, therefore integrated security will fail. Your options are basically to run with SQL authentication using a user name and password or to change the account under which IIS is running to a domain account.

    Each of these options have limitations, if you run IIS under a domain account then you have a *potential* security risk because any failings in your code that can be exploited could have wide (if not unrestricted) access to the domain. If you use SQL authentication then this is not an issue but you would either have to store the password in the dsn (which then becomes available to anyone who knows where to look) or not use a dsn and place it in your asp code.

    Your choice really, personally I would steer clear of a dsn and use a connectionstring like:

    "Provider=SQLOLEDB;Data Source=<MYSQLSERVER>;Initial Catalog=<MyDATABASE>;User ID=<USERNAME>;Password=<PASSWORD>;"

    From asp.

    Tim Cottee
    LVL 50

    Expert Comment

    by:Steve Bink
    >>> Connect.Open "DSN=dsnname"

    Have you saved the login information in the DSN?  You can also provide it in the connection string:

    Connect.Open "DSN=dsnname;UID=username;PASSWORD=password"

    Author Comment

    Hi Tim,
    I used your suggestion and it works now.... thank you for replying!


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now