Unable to create Data Source when CF...

I have just setup CF8 on a new server running Windows 2003
Std.



SQL Server is running on the old server running W2K Server.
It also has CFMX6.1 running on the old server. I made the
DataDirect JDBC drivers (version 3.5) update several months ago to
the old server and set all CF services to run under the
domain\Administrator account. Everything is running fine on the old
server.



The problem appeared when I tried to create a new Data Source
in CF8 server (using the Microsoft SQL Server Driver) that would
link back to a SQL Server table on the old server. The error
message I receive is: ''Connection verification failed for data
source: GAISSQL

java.sql.SQLException: [Macromedia][SQLServer JDBC
Driver][SQLServer]Login failed for user 'XENONX2\GAIS'.

The root cause was that: java.sql.SQLException:
[Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user
'XENONX2\GAIS'.''



SQL Server on the old server is running in mixed mode right
now, but you get the same error message regardless of the mode. I
setup the XENONX2\GAIS account specifically to link back the the
GAIS table SQL Server. The passwords are the same on both machines;
i.e., in both the Windows account and SQL Server Account.



I'm afraid the rub might be that I'm still using work groups
rather than a Domain with Active Directory. But it seems to me that
as long as I pass back the domain\accountName for the system/SQL
Server that I want to log onto the it shouldn't matter that they
are not on the same domain.



I tried setting up a ODBC connection from the new server back
to the old server and essentially got the same error.



If anyone knows how to get this going I would appreciate it
if you would share the secret. Thanks in advance for sharing.



:-}

LenUnable to create Data Source when CF...
I don't think your issue is your workgroups/domain setup. I
connect like this locally in my home network without issue. The
driver is trying to authenticate to the server, it's just being
rejected. Either both machines aren't part of the same workgroup
(doubtful) aren't on the same subnet (even more doubtful) or
there's an issue with the permissions configuration. I know that I
also had to set up SQL to allow those remote access connections as
well (don't ask me how, it's been too long, but it's on MSDN
somewhere).Unable to create Data Source when CF...
Theorecally, there are two ways to go: 1) Setup a ODBC
Socket, or 2) use a regular SQL Server connection where you specify
the SQL Server name and the login account and password. The key,
given my workgroup scenario, is to setup a SQL Server account and
then login using that account name and password. Also, you have to
give permissions to that account on every table in the database --
major pain if you have more than 2 or 3 tables.



I've done all that and actually defined the data source on
the remote SQL Server. CF8 gives me an OK whenever I check out the
datasource. But I've learned before that this ''OK'' doesn't really
mean that things are OK.



I have the same table setup in the local SQL Server . I'm
using the same defintion as to the remote sever except for the SQL
Server name. The local one is GBServer (Datasource==GAISSQL) and
the remote one is XenonX2 (Datasource==GAISSQLx2). I can now see
both data both in the datasource window in DreamWeaver. And I can
pull data from both in my CF page now.



The only problem is, is that they are both pulling data from
the local SQL Server, GBServer. I know this because I have the name
of the remote server in the first field on the remote server so
that I can tell them apart. Both in my output query and in
Dreamweaver, neither remote datasource (GAISSQLx2) shows that data.



I'm going to get some sleep and try it again in the morning.



I anything comes to mind, please share.



Thank your for your thoughts so far.



:-}

Len
Hi Guys,



Any more news on this? I'm having the same issues. I can
create a datasource on my local box that points to the server's db
with no problem. I can connect to it with EMS SQL Manager using the
same username as the datasource, but when I try to setup the
datasource in CF Admin I get the error previously mentioned.
Unfortunately, I can't get access to the SQL logins since this is a
third party app MSDE installation so I'm going with NT Auth.



Thx!

Mike
What I figured out is that there are actually two problems
here: one appears to be an access problem between the two SQL
Server databases; i.e., I can access the SQL Server db (GBServer)
on the new server (W2003) from the old SQL Server db (XenonX2) on
the old server (W2K), but I can not go from GBServer back to
XenonX2. I have yet to resolve this issue.



The second issue I believe is a bug in CF8. What I left out
of the initial description is that both databases had the same
name: GAISSQL. Apparently the order of precedence when CF8 goes to
setup a datasource using the SQL Server format rather than the ODBC
format (which apparently no longer works for SQL Server databases)
is it looks for the database name on the SQL Server database that
it is running on before it looks to the actual Server name that you
enter when you setup the datasource. What this means is that if you
already have a Database with the name you're looking for on the
server you're on, it just uses that one. I determined this by
changing the name of the database on the other SQL Server (XenonX2)
to CAISSQLx2, and recreated the CAISSQLx2 data source. This time
the creation failed because it could on access the remote database
within the remote server.



This does nothing to solve my remote connection problem, but
it is something to keep in mind when trying to do this kind of
thing.



It has been our experience that you have to use SQL Server
logins and give the user account that you setup permissions down to
the table level to do this. There are something like 50 tables in
our system that needed to have these permissions setup. Believe me
that we wouldn't have done this if there was any way around it. I'm
pretty sure that we found this out last spring from tech note, but
no one seems to remember the number any longer. Sorry I don't have
an answer for you.



:-}

Len
MickeyJ,



For using NT logins to MS SQL with ColdFusion you may find
the following links helpful:

Microsoft SQL Server 2005 JDBC Driver 1.2


http://www.microsoft.com/downloads/details.aspx?familyid=C47053EB-3B64-4794-950D -81E1EC91C1BA%26amp;displaylang=en


http://msdn2.microsoft.com/en-us/library/ms378428.aspx#Connectingintegrated



CF Forums


http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?forumid=1%26amp;catid=6%26amp;t hreadid=1206110%26amp;highlight_key=y%26amp;keyword1=integrated