• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

SQL DB connection error...

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
TESTS COMPLETED SUCCESSFULLY!

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"
selectSQL = "SELECT * FROM TABLE"
Set RecSet = Connect.Execute (selectSQL)
If NOT RecSet.EOF THEN
DO UNTIL RecSet.EOF
Response.Write RecSet("1") & ", " & RecSet("2") & "<BR><BR>"
RecSet.MoveNext
Loop
End If
RecSet.Close
Connect.Close
Set RecSet = Nothing
Set Connect = Nothing
%>

Can you maybe help me on this? It's giving me a hard time....
Cheers!
0
Schurink
Asked:
Schurink
1 Solution
 
TimCotteeHead of Software ServicesCommented:
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
0
 
Steve BinkCommented:
>>> 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"
0
 
SchurinkAuthor Commented:
Hi Tim,
I used your suggestion and it works now.... thank you for replying!

Cheers!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now