September 28, 2007 at 9:43 am
Hi guys!
I have a SQL Server 2000 instance running on a Windows 2003 machine
We have a local network running WinXP, and some of them have IIS installed for local tests
The problem is that, ASP pages running on these machines cannot connect to the SQL Server.
Here's the error message:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'ead'. Reason: Not associated with a trusted SQL Server connection.
notes:
- SQL Server is configured to use both Windows Authentication and user/password connections
- Using classic ASP
- Query analyser connecting without errors
here's the simple test ASP code:
<%
set conn = Server.CreateObject("ADODB.Connection")
conn.open "Driver={SQL Server};Server=192.168.8.93;Database=ead;Uid=ead;Pwd=webead"
set conn = nothing
%>
September 28, 2007 at 10:39 am
Are you sure that SQL Server accepts Mixed mode connections? Just double check and be sure your IPs are correct. I've had people change them and someone else doesn't realize it.
This is a very simple connection. Does this code work locally on the server?
September 28, 2007 at 11:12 am
Hi Steve 🙂
Thanks for helping me on this.
Before I posted my topic, I checked this. Yes, the SQL Server is configured to mixed-mode and I can connect when running ASP pages on the same machine as the SQL Server.
September 28, 2007 at 11:26 am
Very strange. Is the SQL Server accepting TCP/IP as the protocol? Wonder if it's named pipes, which are limited to local connections.
Other than that, you've slightly stumped me. Can you make an ODBC connection from the client running XP?
October 2, 2007 at 9:24 am
Hi Fabio,
Do you have the ability to try other accounts? For example, do you have privileges to use the sa account? If so, try to see if you can get it to connect using another account. If you get the same error with the 'sa' account, something is telling SQL Server that you are trying to connect using windows authentication. Also, if you are testing from a client running IIS/ASP, try adding a connection from that machine with the ODBC manager. This is a quick way to see if it is a problem with ASP or with your connection/user account. Let me know what you find.
Thanks,
Eric
October 2, 2007 at 12:52 pm
Hi guys!
I created an ODBC connection and still did not get to connect...
here is what happened
using user sa
Provider (0x80004005)
Error not specified
/conn.asp, line 7
using user ead
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
/conn.asp, line 7
crazy!
October 2, 2007 at 1:00 pm
Sounds to me that you're having issues with ODBC/ADO access more than anything. depending on what user you're using to run this as - you might not have access to ODBC connections, or the registry settings thereof. Take a look as this and see if this helps at all:
http://support.microsoft.com/kb/295297
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 2, 2007 at 1:39 pm
From the client machine, try creating an alias using the Client Network Utility. Specify TCP/IP as the protocol and type in the IP Address of the DB server. Make sure that you put in the correct SQL port number too (default 1433).
October 2, 2007 at 3:09 pm
Hi Fabio,
I am 99% sure that the following error is caused by a failed connection using Windows authentication:
"Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."
The question is why is it (ASP code) trying to use Windows authentication to connect? The lines you provided shouldn't be connecting using this method, though. Do you want to try just doing the connection test in ODBC Manager (Start -> Programs -> Administrative Tools -> Data Sources (ODBC))? See if your username/pw works in the last config page (The test connection button). If it does, then ASP is probably the problem. You could try the following:
conn.open "Driver={SQL Server};Server=192.168.8.93;Database=ead;Uid=ead;Pwd=webead;Trusted_Connection=No"
I'm not sure if this will work, but it should force the connection string to use standard SQL authentication.
Thanks,
Eric
October 2, 2007 at 3:20 pm
I think I've got it... Well, you are using the OLEDB driver for ODBC, right? Well, try straight OLEDB with the following connection string:
"Provider=sqloledb;Data Source=192.168.8.93;Initial Catalog=ead;User Id=ead;Password=webead;"
This way, you aren't dependent on an ODBC DSN already being created. If the ODBC DSN is set for windows authentication and your connection string that uses it is set for SQL authentication, it will use the DSN's instead, trying to use Win authentication. At any rate, using OLEDB is probably best, anyhow, as you only have to configure it once per app, not per server.
Thanks,
Eric
October 2, 2007 at 3:36 pm
this is becoming too weird..:w00t:
here's what happened when trying your last suggestions:
conn.open "Driver={SQL Server};Server=192.168.8.93;Database=ead;Uid=ead;Pwd=webead;Trusted_Connection=No"
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'ead'. Reason: Not associated with a trusted SQL Server connection.
/conn.asp, line 8
conn.open "Provider=sqloledb;Data Source=192.168.8.93;Initial Catalog=ead;User Id=ead;Password=webead;"
Microsoft OLE DB Provider for SQL Server (0x80004005)
Login failed for user 'ead'. Reason: Not associated with a trusted SQL Server connection.
/conn.asp, line 8
October 2, 2007 at 3:38 pm
just to remember, I can connect to the SQL Server using Query Analyser (using the same user/password)
October 2, 2007 at 3:50 pm
R ur Protocols enabled(TCP/IP or named pipe)?
October 2, 2007 at 4:20 pm
This is a stumper... You've used 2 different kinds of connection methods, both have SQL Server telling you that you are connecting via windows authentication and failing... I haven't seen this error fail so persistently! As for Myank's thought, SQL Server is responding with an ODBC error, so the client is talking to the server fine. I don't know on this one. Does anyone else have any ideas?
Thanks,
Eric
October 2, 2007 at 4:29 pm
Network permissions?
All the patches installed?
Can you install QA on that client and see if you can connect from there?
... or try that connection string from a vbs file maybe.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply