SQL DB connection error...

Posted on 2005-04-25
Medium Priority
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 43

Accepted Solution

TimCottee earned 1000 total points
ID: 13858764
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 51

Expert Comment

by:Steve Bink
ID: 13858780
>>> 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

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


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

850 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