basic connection problem

[Middletree]

I'm moving an intranet app from one machine to another. Both are Win2000
Server, with SQL Server 2000. I'm trying to keep them the same in every
possible way. However, when I try to connect to the site through the
browser, I get the following:

Microsoft OLE DB Provider for SQL Server (0x80004005)
Login failed for user 'xxx'. Reason: Not associated with a trusted SQL
Server connection.
/includes/database_connection.inc, line 13


Obviously, I replaced the user name with 'xxx' in the above example.

I have remote sessions with both machines (the one that works and the one
that doesn't) and I am comparing every SQL Server setting, IIS setting,
etc., and cannot see the difference.

Here's the include file that is referenced above ( replaced the actual names
with xxxx's):

<%
Dim strDBConnection
strDBConnection = _
"Provider=SQLOLEDB;" & _
"Persist Security Info=False;" & _
"Data Source=xxxmachinenamexxx;" & _
"User ID=xxx;" & _
"Password=xxx;" & _
"Database=xxxx;"

Dim objConnection
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open strDBConnection
%>

 

[Dave Anderson]

...Microsoft OLE DB Provider for SQL Server (0x80004005)
Login failed for user 'xxx'. Reason: Not associated with a trusted SQL
Server connection...

strDBConnection = _
"Provider=SQLOLEDB;" & _
"Persist Security Info=False;" & _
"Data Source=xxxmachinenamexxx;" & _
"User ID=xxx;" & _
"Password=xxx;" & _
"Database=xxxx;"

Have you tried using the .udl trick for creating connection strings? There
may be slight differences in the provider, WINS resolution, etc. that are
revealed when you do so.

For example, I usually see Provider=SQLOLEDB.1 (not SQLOLEDB), and you may
need a fully-qualified machine name: machinename.yourdomain.com. In
addition, the SQL provider expects [Initial Catalog], not [Database]. Each
of these become obvious with the UDL file.

Lastly, you may need to use the Client Network Utility (or Server Network
Utility) to specify protocols.

 

[Middletree]

Good points. I did already specify the proper protocols, because it was
suggested in that article I linked to earlier.

I forgot how to do the UDL trick. You got directions?

 

[Dave Anderson]

Good points. I did already specify the proper protocols, because it
was suggested in that article I linked to earlier.

I forgot how to do the UDL trick. You got directions?

This sums it up as well as any:
http://www.devx.com/tips/Tip/13354

In short, the .udl file is a simple text document containing a connection
string. You double-click it in windows to open with OLEDB Core Services
(where you use a wizard to configure and test your connection), then save
and and open in notepad to read the connection string.

 

[Mike Brind]

I'm moving an intranet app from one machine to another. Both are Win2000
Server, with SQL Server 2000. I'm trying to keep them the same in every
possible way. However, when I try to connect to the site through the
browser, I get the following:

Microsoft OLE DB Provider for SQL Server (0x80004005)
Login failed for user 'xxx'. Reason: Not associated with a trusted SQL
Server connection.
/includes/database_connection.inc, line 13


Obviously, I replaced the user name with 'xxx' in the above example.

I have remote sessions with both machines (the one that works and the one
that doesn't) and I am comparing every SQL Server setting, IIS setting,
etc., and cannot see the difference.

Here's the include file that is referenced above ( replaced the actual
names with xxxx's):

<%
Dim strDBConnection
strDBConnection = _
"Provider=SQLOLEDB;" & _
"Persist Security Info=False;" & _
"Data Source=xxxmachinenamexxx;" & _
"User ID=xxx;" & _
"Password=xxx;" & _
"Database=xxxx;"

Dim objConnection
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open strDBConnection
%>
-------------
I went to ASPFAQ.com and found
http://databases.aspfaq.com/database/what-should-my-connection-string-look-like.html
but I guess I don't understand it enough to use the info and advice it
provides.

Usually, this error arises because the Server instance you are trying to
connect to has been set up for Windows Authentication only. You should make
sure it is set to Mixed Mode.

 

[Dave Anderson]

Usually, this error arises because the Server instance you are
trying to connect to has been set up for Windows Authentication
only. You should make sure it is set to Mixed Mode.

That's yet another thing that configuring a UDL would have exposed.

The "not associated with a trusted connection" error can also occur when the
client and server are using different protocols. I have more than once
solved this error with the [Network Library=DBMSSOCN] option.

 

[Middletree]

Usually, this error arises because the Server instance you are trying to

connect to has been set up for Windows Authentication only. You should
make sure it is set to Mixed Mode.

I have tried changing that, but am not sure how to do it. I do know how to
do it when installing SQL Server, but I'm obviously past that point now. I
will Google it, but if you have a quick description of how to do this, I'd
appreciate it.

 

[Middletree]

OK, I figured out how to change the mode, but now get this error:



Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'xxx'.

 

[Dave Anderson]

Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'xxx'.

It sounds like you have two problems: talking to SQL Server, and
authenticating. You solved the first. The rest is purely a SQL Server issue.

Make sure your new SQL Server instance has the expected Login, and that the
Login has the expected password. Then make sure to grant that Login
appropriate permissions in the target database.

 

[Middletree]

Weird. Turns out that when I copied the database over, it kept all the users
and stuff except for this one. I got it to work by:

1. Switching to mixed mode.
2. Adding a database login by the name I have in my connection string, and
making that login a user on the database.


thanks for your time!