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 Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Connect to SQL Server using ODBC

Status
Not open for further replies.
alexisb

alexisb

Programmer
Apr 5, 2001
100
0
0
US
I have tried many different ways to connect to a SQL Server db but can't get any to work. I am logging in through VPN and then remote desktop onto a machine and am working directly on the machine (there's no external IP address). It's SQL Server 2005 and Windows Server 2003.

Each way I tried seems to point to a login issue. The last way I tried, setting up an ODBC connection, seemed like it was going to work. Using Windows NT authentication, the connection tested successfully. However, when I used that DSN in my ASP page, I got an error: Login failed for user ARAGORN\USR_ARAGORN. The system administrator said it's trying to use the IUSER account on the local machine (ARAGORN). I then changed the authentication to SQL Server and entered my domain account, but when I press Next, I get an error saying: "Login failed for...User is not associated with a trusted SQL Server connection".

I have only worked with ASP to SQL Server on hosts where the connection is pretty obvious or I am able to create a new user and just use that login. I tried creating a new user on this machine and used that user/password but got the same error as when I use my own login.

I would also like to not use a DSN and use the connection string directly in ASP but I couldn't get anywhere with that.

I can see that ASP is working. I can write ASP lines and see them in the browser. But as soon as I put a connection line, the page doesn't work. At least with the DSN, the page didn't totally bomb and I could see the ASP lines displayed and the error message on the line with the DSN call, which is line 17.

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>My title</title>
</head>
<body>
<p> hello there world!</p>
<%
response.write "hello from asp<br>"
response.write "hello again from asp"

' Set db Connection
set ref = server.CreateObject("ADODB.Recordset")
ref.ActiveConnection = "DSN=intranet;" 'line 17
ref.Source = "SELECT test FROM mytest"
ref.Open()

'set the cursor to the first record
ref.movefirst

'Get all the lastnames on the screen
While NOT ref.EOF
Response.Write(ref("test")&"<br>")
ref.movenext
wend
%>
</body>
</html>

Any suggestions are appreciated. I feel very stupid. I have checked all over this forum and others and can't get it to work.
Thanks,
Alexis
 
Sort by date Sort by votes
SQL Server supports 2 different authentication modes, Windows Authentication and SQL Server authentication. There are configuration settings that allow you to choose Windows Authentication or Both.

When you get the error "not associated with a trusted SQL Server connection", it is because you are trying to log in using SQL Server authentication but the server is not configured to allow SQL Server authentication.

To change the configuration, start the SQL Server Management Studio.
Right click the server, then choose properties.
Click the security item (near the top left of the window).
Under 'server authentication', make sure it is configured for 'SQL Server and Windows Authentication mode'.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Since you have access to run something directly on the server's desktop, I suggest making a little test script that runs as a .VBS file instead of an ASP page.

That would allow you to ignore the ASP security related issues for a bit and just focus on getting a ConnectionString that will actually work. Then once I had the core ADO stuff working I'd slip it back into my ASP.




The main difference between the VBS and ASP files is that you'd use just plain [tt]CreateObject[/tt] instead of the [tt]Server.CreateObject[/tt] syntax and also probably a [tt]MsgBox[/tt] instead of [tt]Response.Write[/tt].


I then changed the authentication to SQL Server and entered my domain account,
Click to expand...
Does your domain account have a SQL Server login that uses Windows Authentication or is it a member of a domain group that has a SQL Server login that uses Windows Authentication? The reason I ask is that SQL Server authentication is for accounts that are not necessarily members of the domain at all... like for example the old sa account in SQL Server... it uses SQL Server authentication because there is no domain or local machine account named "sa
 
Upvote 0 Downvote
I don't see how this can work:

ref.ActiveConnection = "DSN=intranet;"

you have to create a connection object first:

set myConnection = server.CreateObject("ADODB.Connection")

and open it passing it a connection string with the logon
info, ie:

myConnection.Open("DSN=intranet;User id=user;password=password")

then:

ref.ActiveConnection = myConnection

unless I am not reading this right.
 
Upvote 0 Downvote
Thanks everyone for your responses.

From hejamana's comments, I changed my code but still got the same error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'ARAGORN\IUSR_ARAGORN'.
/mytestpage4.asp, line 14

Here's the page now (note I put fake login info for this post):
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>My title</title>
</head>
<body>
<p> hello there world!</p>
<%
response.write "hello from asp<br>"
response.write "hello again from asp"
set myConnection = server.CreateObject("ADODB.Connection")
myConnection.Open("DSN=intranet;User id=myusername;password=12345")
set ref = server.CreateObject("ADODB.Recordset")
ref.ActiveConnection = myConnection
ref.Source = "SELECT test FROM mytest"
ref.Open()
ref.movefirst
While NOT ref.EOF
Response.Write(ref("test")&"<br>")
ref.movenext
wend
%>
</body>
</html>

In the SQL Server Mgmt Studio, under Server Authentication (Server Properties), I had already changed the radio selection to SQL Server and Windows Authentication, and not just Windows Authentication. When I talked about SQL Server authentication in my original post, I was only talking about the steps in configuring the ODBC connection.

When I open the SQL Server Mgmt Studio, I first get a dialog box "Connect to Server" where it says:

Server Type: Database Engine (selected)
Server Name: Aragorn (selected)
Authentication: Windows Authentication (selected)

If I change the Auth. to Windows, the login and password boxes are enabled and I can enter a login and password. I tried my login and others I've created, but none work. It seems the only way I can connect is to leave the Windows Auth, which defaults to my NT login/password. Maybe this info will give a clue to the problem.

I am not sure how to do the VBS file so I will look into that.

Thanks again.
Alexis



 
Upvote 0 Downvote
A VBS file is just a plain text file with .vbs file extension... you can double-click it to run just like a .bat file.
 
Upvote 0 Downvote
Well the error message pretty much says it all, it doesn't like either the user name or password or both.
Try OLEDB instead of ODBC:
myConnection.Open("Provider=SQLOLEDB;Data source=yourservername;Database=yourdatabasename;User Id=username;Password=password")
 
Upvote 0 Downvote
I tried this:
set myConnection = server.CreateObject("ADODB.Connection")

myConnection.Open("Provider=SQLOLEDB;Data source=Aragorn;Database=Intranet;User Id=XXX;Password=XXX")
'myConnection.Open("DSN=intranet;User id=XXX;password=XXX")
set ref = server.CreateObject("ADODB.Recordset")
ref.ActiveConnection = myConnection
ref.Source = "SELECT test FROM mytest"
ref.Open()

'set the cursor to the first record
ref.movefirst

While NOT ref.EOF
Response.Write(ref("test")&"<br>")
ref.movenext
wend

I got this error:
Microsoft OLE DB Provider for SQL Server error '80004005'

Login failed for user 'myuser'. The user is not associated with a trusted SQL Server connection.

/mytestpage5.asp, line 19

I tried this using my login/password and the one I created as a test (myuser). I always get this trusted connection error.

Thanks,
Alexis
 
Upvote 0 Downvote
Your connection string is wrong.

I recommend you go to
Click on SQL Server 2005, then 'SQL Native Client OLE DB Provider'.

If you want to use SQL Server authentication, use the connection string listed under 'Standard Security'.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Ok, we're close, change your sql db authentication mode from windows to sql server authentication or mixed.
 
Upvote 0 Downvote
My setting under SQL Server security is already SQL Server and Windows Authentication. I changed the Windows Only setting a few days ago.

I tried the connection string at and got the same trusted connection error. It's like the connection isn't even looking at the SQL Server users because I can put in a bogus userid and password in the connection string and get the same error. It's not even getting that far to see the valid login for the connection.

Thanks,
Alexis
 
Upvote 0 Downvote
Your SQL Server is configured for Windows Authentication, configure SQL Server to Mixed Authentication Mode (SQL Server and Windows Authentication), or just SQL Server Authentication.
 
Upvote 0 Downvote
Well the only other thing I can think of is are you sure the user in your connection string is a user in the database you are specifying in the same connection string? As a test use the admin user and password just to see if you can get in at all.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bslintx2021
  • Locked
  • Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
225
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
105
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
96
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to check if a datatable is empty ?
Replies
0
Views
84
leifoet
leifoet
leifoet
  • Locked
  • Question
Problem: sql-Select with request.form
2
Replies
22
Views
195
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top