Am hoping someone can help.  I just did a clean install of SQL Server 2008 R2 Developer on a Win7 Pro PC.  It has been working fine, and I copied my database created in SQL Server 2000 to it, and have accessed it (within SQL Server) with no problems.

I just installed CF9 then the CF9.0.1 update, and had same problem, same error message when trying to set up the database connection in CF Admin.  However, I did not have TCP enabled in:  "SQL Server Configuration Manager" > "Network Configuration", as suggested by carltype3.

After I enabled that, and re-started SQL Server, I'm getting a different error message, which is (note, replaced actual User name with "xxxx" for security reasons:

"Connection verification failed for data source: SkynetSQL
java.sql.SQLInvalidAuthorizationSpecException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'xxxx'.
The root cause was that: java.sql.SQLInvalidAuthorizationSpecException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'xxxx'."
==============
It would appear I'm now getting through to the server, but it cannot validate my logon?  I'm new to SQL Server 2008.  I'm using (trying to connect to) a database created in SQL Server 2000.  Only 2 accounts show up in SQL 2008, which is using Windows Authentication.  My Windows UserID, which is "xxxx" (as shown above), has no password, and is the only account on my Windows 7 Pro PC.

There is also another account showing up, called: "Coldfusion", which I assume was copied from the SQL 2000 version, and was showing up in SQL 2008 before I even installed ColdFusion.

With our other PCs and servers running CF7 and SQL 2000, I use the "sa" account to connect to this database. And on those, SQL 2000 is configured for dual Windows and SQL Server Authentication.  But SQL 2008 doesn't use "sa" accounts, from what I can tell.

We will soon be upgrading our production servers to SQL 2008 and CF9, and need to determine how to make this connection work.  Appreciate any advice/help.  Thank you,

Gary

Can you telnet from your CF server to the SQL Server on port 1433?

Thanks to the awful formatting on these forums it's hard to tell which parts of that are your question, but there's a bit I think was yours saying "login failed for user" - isn't that just a credentials issue? You shouldn't be connecting to SQL Server as an administrator from CF.

I believe  SQL Server and Windows Authentication mode is setup to allow proper authentication credentials for our development environment. We have

Apparently not

For that to work, ColdFusion must also be running as the computer administrator (again, a bad idea) for it to connect. I'd try creating a proper login and user within SQL Server with only the permissions it needs over the one database, and use those credentials in CFAdmin to connect.

None of this "connect as administrator" nonsense

So basically, the CF website is given to us to take over and I'm to setup 3

different environments as follows:

Sandbox: A localhost CF9 test environment for training and playing in -

setup without a problem

Pre Production - The in house environment currently being worked on to

mirror the Post Production/Live Website

Post Production/Live Website: The 3rd party hosted provider setup and

working

We can see the restored database on our network and all the tables & rows

etc but unable to verify the data source in CF9 Admin Portal.

OMG this is getting frustrating;)

Ah? Okay since I have a simple index.cfm set in the root of my ColdFusion 9

Server to display an output of server and browser info via a cfscript as

follows:

I've also run the SQL Profiler and saved the file although not

seeing/understanding anything useful in it.

So, I remote back into the DATABOX today and open up the ODBC Data Source

Administrator and notice User Data Sources not filled in and think well, if

the source isn't defined here, how can it verify in CF9? I go through

adding it so it shows up in the list and tests successfully when

authenticity of login ID is set to use Windows NT authentication using the

network login ID.

However, when I check With SQL Server authentication using a login ID and

password entered by the user.

Along with Connect to the SQL Server to obtain default settings for the

additional configuration options:

Login ID: XXXXX

Password: XXXXX

I always get the error:

Microsoft SQL Server Login

Connection failed:

SQLState: '28000'

SQL Server Error: 18456

Re: Can't connect CF 9 to MSSQL 2008 R2[ODBC SQL Server Driver][SQL Server]Login failed for user

'Developer'

Re-added data source back into cf admin and still won't verify

I thought it best to reset the password to be sure the right combo was

being used so I opened MS SQL Server Manager Studio, connected to database

-> Security -> Logins -> (found the user account) right clicked ->

properties and changed password and restarted server.

Re-added data source back into cf admin after server reboot and still won't

verify.

Port was confirmed this morning to be using the default 1433 and dynamic

port blanked to make sure 1433 for TCP is the only one it can come through.

Where the website and database have be given to us to take over is it

possible there is some kind of credentials I'm missing from the previous

developers or just something I'm doing wrong?

Figured it out, issue resolved after working with the previous developers.

Seems the source names were a bit different and the instance name had to be

changed to the ip of the data box the sql server was installed on. I

believe the permissions were fine with the new user setup as were the

previous ones. SQL authentications had to be changed as Windows NT mix mode

authentication wasn't doing it, still not quite sure why though. The port

was fine and the firewall settings were fine. Looks like I had looked for

the datasource names kind of in the wrong place but it was pointed out for

our project.

Thanks everyone for the assistance though.