Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ODBC connectivity issue 1

Status
Not open for further replies.
SunnyByfleet

SunnyByfleet

Technical User
Feb 24, 2003
146
GB
Hi,

I am having a wonderful time trying to reconstruct our company website on a test server.

The test server runs Windows 2003, IIS 6 and SQL Server 2000.

I have FTPed all the ASP files etc from the live web server.
I have restored a backup of the live server database into the test SQL server.

I have created an ODBC link, which passes the little test when you click the button etc

However, when I query the database in ASP using code like:

Code: 
set conn = server.CreateObject("yada")
conn.open "connection name"

I get the following error:

Code: 
Microsoft OLE DB Provider for ODBC Drivers error '80040e4d' 

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

If I instead specify the username and password, as in:

Code: 
conn open, "connection name", "username", "password"

It works fine.

The only problem with that is the live website doesn't specify the username and password, so presumably it has its ODBC set up differently.

Anybody know what I need to configure to get this working?


 
Sort by date Sort by votes
The test SQL Server was probably set up with Mixed Authentication and the logins may be set up to use SQL Server authentication.

Check it out via Enterprise Manager > Security > Logins.

-SQLBill

Posting advice: FAQ481-4875
 
Upvote 0 Downvote
Hmmm, no. The server is setup to use mixed, but the only other option is Windows only. If I drill down as you say to an individual login account, in this case called AC_WEB, then its properties show that it uses SQL Server authentication.

AC_WEB is the account that I specify in the ODBC setting I create via control panel. I can input the right password and do a test and it works fine. There is no domain account called AC_WEB so it cannot be using windows authentication.

Any other ideas?
 
Upvote 0 Downvote
Again, your test server is set up for Mixed Authentication (like I said) and the login you are using is set for SQL Server authentication (like I said). That means that you MUST supply the login and password to make the connection.

I didn't say, but figured you would understand, that means the production server is most likely set up for Windows Authentication and the login on the production server also is set to use Windows Authentication.

-SQLBill

Posting advice: FAQ481-4875
 
Upvote 0 Downvote
That means that you MUST supply the login and password to make the connection.

Ah, thats the bit of knowledge that was eluding me. I will try that out on Monday.

I thought you were trying to tell me to use SQL Server logins only. This puzzled me, as it wasn't an option. I now see what you meant!
 
Upvote 0 Downvote
Yes that did indeed work. Well, for a few lines at least!

I have found an .ASP page which I think the original programmers would have used to test the database connectivity. It is called ODBCTEST.ASP and I have included it below.

Code: 
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>SQL Database interface</title>
</head>

<body>
<%

Dim compare
compare = StrComp(Request.ServerVariables("REQUEST_METHOD"), "POST", 1)

if compare = 0 then
	Check
else
	Ask_Info
end if

sub Check

dsn = Request("dsn")

compare = StrComp(Request("uselogin"), "true", 1)

if compare = 0 then

	dbuser = Request("dbuser")
	dbpass = Request("dbpass")
	
	Set Conn = Server.CreateObject("ADODB.Connection")
	Conn.Open dsn, dbuser, dbpass
else

	Set Conn = Server.CreateObject("ADODB.Connection")
	Conn.Open dsn

end if


Set maketable = Server.CreateObject("ADODB.RecordSet")

Response.Write "<B><FONT SIZE=2 COLOR=BLUE>DOING: </B> Creating test table... <br><BR>"
sqlcommand = "create table test9net ('number' int null, 'name' char(40) null)"
maketable.Open sqlcommand, Conn

Set filltable = Server.CreateObject("ADODB.RecordSet")

Response.Write "<B><FONT SIZE=2 COLOR=BLUE>DOING: </B> Populating test table... <br><br>"
sqlcommand = "insert into test9net values ('24', 'Testing Database')"
filltable.Open sqlcommand, Conn

Set deletable = Server.CreateObject("ADODB.RecordSet")

Response.Write "<B><FONT SIZE=2 COLOR=BLUE>DOING: </B> Deleting test table... <br><BR>"
sqlcommand = "drop table test9net"
deletable.Open sqlcommand, Conn

Response.Write "<B><FONT SIZE=2 COLOR=BLUE>DOING: </B> Tests complete and successfull...<br><BR>"

Conn.Close
%>

</body>
</html>

<%

end Sub

Sub Ask_Info
%>

<form action="odbctest.asp" method="post">
<table width="500" align="center">

<tr>
<td width="500" bgcolor="navy" colspan="2" align="center">
<font color="white">ODBC Test</font>
</td>
</tr>

<tr>
<td width="200" bgcolor="#f0f0f0">
Dsn Name: 
</td>
<td width="300" bgcolor="silver">
<input name="dsn"> 
</td>
</tr>

<tr>
<td width="200" bgcolor="#f0f0f0">
Username: 
</td>
<td width="300" bgcolor="silver">
<input name="dbuser" > 
</td>
</tr>

<tr>
<td width="200" bgcolor="#f0f0f0">
Password: 
</td>
<td width="300" bgcolor="silver">
<input name="dbpass" > 
</td>
</tr>

<tr>
<td width="350" bgcolor="#f0f0f0">
Use username &amp; pass? 
</td>
<td width="150" bgcolor="silver">
<input type="checkbox" name="uselogin" value="true"> 
</td>
</tr>

<tr>
<td width="500" bgcolor="#f0f0f0" colspan="2" align="center">
<input type="submit" value="Query">
</td>
</tr>

</table>
</form>
<%
end Sub
%>

</body>
</html>

Now, when I run the above, and type in the name of the ODBC data source I have created, it opens it ok. Before I was getting that error. Having set it to use Windows, and then created an IUSR_WEBUSER login account on the LOGINS section of Enterprise manager, it works fine.

However, when I run the above program, I get this far:

Code: 
Response.Write "<B><FONT SIZE=2 COLOR=BLUE>DOING: </B> Creating test table... <br><BR>"
sqlcommand = "create table test9net ('number' int null, 'name' char(40) null)"
maketable.Open sqlcommand, Conn

At this point I get the following error:

Code: 
DOING: Creating test table... 


Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'number'. 

/odbctest.asp, line 45

I know that strictly speaking this is now an ASP as opposed to a SQL Server problem, but if anybody has any ideas I would be most receptive.

Thanks
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

CodeWell
  • Locked
  • Question
SQL Server 2017 Install Issue - Database Not in Configuration Manager
Replies
0
Views
676
CodeWell
CodeWell
mrmovie
  • Locked
  • Question
ODBC connection, or other from Linus system using AD account details 2
Replies
2
Views
335
mrmovie
mrmovie
intel233
  • Locked
  • Question
Backup/Restore DB from SQL issues
Replies
2
Views
320
intel233
intel233
dd2maker
  • Locked
  • Question
SQL server 2008 R2 backup restore issue
Replies
2
Views
295
dd2maker
dd2maker
stevo1961
  • Locked
  • Question
Issue with Concurrent Users
Replies
1
Views
236
fredericofonseca
fredericofonseca

Part and Inventory Search

Sponsor

Back
Top