CFMX7 vs. SQL 2000 on Win 7 Ultimate

[coder1957**]

Been using CF since ver 1 and never had db issues before.

Installing CFMX7 (temporarily) on a MacBook PRO running Bootcamp,

Succesfully installed SQL personal Edition. 2000. Pack 4.

Also CFMX7 installed fine.

Attached a known good and visible DB into SQL.

But when I add it as a data source it will not connect to CF.

I read in MS notes about needing Windws Authentication permission only perhaps.

But in the properties screen in CF I have always needed to supply a DB sys account name and password.

I have one in there but it simply will not take .... how do I make this work without

- getting SQL2005? 

Many thanks.

Robert

[JR__Bob__Dobbs]

Questions/Things to Check:

1. Does your SQL 2000 install support SQL authentication?

2. Is TCP/IP communication enabled for SQL server?
    http://support.microsoft.com/kb/827422
    http://support.microsoft.com/kb/841252

3. Is the port that CF communicates with SQL open, not blocked by a firewall?
    http://support.microsoft.com/kb/287932

4. What does "a known good and visible DB" mean?  Visible where?

[coder1957**]

It's simpler ... I am using it locally only ...

Local CF, local SQL.

I told SQL 2000 when installing it on this spare laptop to support local auth, yes.

Yet in security it also has a login.

Good and visible ... I mean I succesfully attached a known db to the local "list" ... and it took and is visible from within Enterprise Manager.

Since then I really ... well, went in another direction ... figured it was time to upgrade so downloaded and install the eval for SQL 2008 R2.  3 hours later ... oh no ... I was NOT looking for a LEARNING CURVE ... so now wondering if I should down grade again or press on and learn SQL 2008 and see if IT even succesfully is read by CF!

As for firewall, GOOD Q.  I don't know ... brand new refurbished test laptop. Looking fo firewall!

[JR__Bob__Dobbs]

Can you successfully use a SQL login (not a Windows account) to login to SQL Server using Query Analyzer?

[Reed_Powell-ttnmOb]

I'd stick with SQL 2008, since SQL 2000 is long dead.  But I didn't see a firm answer the earlier suggestion that you make sure that TCP is enabled in SQL - by default it is not.  I do not believe that your test of connecting to the SQL 2000 db via Enterprise Manager (or SMS in the case of SQL 2008) proves that TCP is enabled.  CF can only talk to the DB via TCP.

I do not believe that there is any way to use Windows Authentication for the CF <> SQL connection.  I believe that it must always be a SQL login.  Be sure that the login you are using has privs for the database that you are trying to connect to.

-reed

[coder1957**]

Ok, I am back down to SQL 2000 on Win 7 ...

Back where I started ... in Query Analyser ... in SSEM I can access the databases that are attached perfectly.

The only thing I cannot do is to "register" them in CF7 data sources.

I tell it the db name "EXAMPLE"

Server is called MAC-PC

Username is the standard installation two letters. Port 1433 as usual.

Password is what I assigned to SQL and the username and password are working fine in Query Analyser.

I DID for the first time (before this week) ever get a warning from SQL installer saying there are known incompatibilities ... but I'd have thought that being able to do as much as I have done (in the Analyser), it would be fine.

CF when saving is calling opn 127.0.0.1:8500as usual.

But then errors out

Connection verification failed for data source: xxx

Error establishing socket.  Yadadada.

Connection refused.

So ... is this an ODBC issue?

Something else in the CF Admin panel I need to do to let CFS7 work with Win 7?

All I am trying to do is start with SQL 2000, then migrate to 2005 and use this PC, (well Mac), to do it on ... before using a server at the DC.

[Reed_Powell-ttnmOb]

Did you verify that TCP was enabled?  I don't have a SQL2000 box handy, and don't remember where to look, or else I'd tell you where to look for this.  Although I'm pretty sure that the SQL2000 default was to turn on TCP, that's exactly what this problem sounds like.

-reed

[coder1957**]

I went here ...

http://www.websina.com/bugzero/errors/sql-server-connection-error.html

and followed all their pointers ...

I have disabled Windows Firewall.

TCP/IP is enabled in svrnetcn.exe.

But when I telnet into 1433 on 127.0.0.1 and I do not see sql server listed.

I checked the registry ... the tcp port is properly set to 1433

Cannot determine using netstat as I cannot keep the window open!

No good ...

I downloaded and installed SP4 ...

Rebooted the laptop ...

And ...

Yes!!!  It works ... no idea what I did differently ...

Now I have to get the app to work on the new machine ... and do all the CF settings.  Lovely ... another day or 2!

Many thanks for your patience ... just wish I could tell you what I did to make it work ...