Can't get SQL Server database connected through CFADMIN
Cecil Champenois
I am trying to get the OWS sample database set up. I've taken the script (SQL code) from the OWS.TXT file and created the OWS database in SQL Server, then a couple of the tables and used the already prepared INSERT statement to fill a couple tables.
I've been reading the first WACK book, which is good stuff, but there is little instruction on how to set up SQL Server to work with CFADMIN, nothing in the book anyway, and little online. Everyone wants to use only MySQL with CF. I am coming from a Microsoft background, so I'd like to use SQL Server 2008.
I put in only the following:
CF Data Source Name: ows
Database: ows
Server 127.0.0.1
Port: 1433
and then hit the Submit button.
Charlie Griefer
What error are you getting? It's always helpful to post the error
message rather than just stating that it doesn't work.
Make sure that you have TCP/IP enabled on port 1433. It's been a
while since I've used SQL Server, but if memory serves, I believe that
more recent versions of SQL Server didn't default to port 1433?
> --
> You received this message because you are subscribed to the Google Groups
> "IECFUG" group.
> To post to this group, send email to iec...@googlegroups.com
> To unsubscribe from this group, send email to
> iecfug-un...@googlegroups.com
> For more options, visit this group at
> http://groups-beta.google.com/group/iecfug?hl=en
--
Charlie Griefer
http://charlie.griefer.com/
I have failed as much as I have succeeded. But I love my life. I love
my wife. And I wish you my kind of success.
Cecil Champenois
From: Charlie Griefer <charlie...@gmail.com>
To: iec...@googlegroups.com
Cc: Arizona ColdFusion User Group <azc...@yahoogroups.com>
Sent: Sun, January 2, 2011 10:31:04 AM
Subject: Re: [IECFUG] Can't get SQL Server database connected through CFADMIN
Cecil Champenois
Now, I'll see if I can get CFADMIN to let me set up the ows data source for WACK Book 1.
From: Charlie Griefer <charlie...@gmail.com>
To: iec...@googlegroups.com
Cc: Arizona ColdFusion User Group <azc...@yahoogroups.com>
Sent: Sun, January 2, 2011 10:31:04 AM
Subject: Re: [IECFUG] Can't get SQL Server database connected through CFADMIN
Charlie Griefer
On Sun, Jan 2, 2011 at 11:41 AM, Cecil Champenois
Cecil Champenois
Connection verification failed for data source: OWS
java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot open database "OWS" requested by the login. The login failed.
The root cause was that: java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot open database "OWS" requested by the login. The login failed.
From: Charlie Griefer <charlie...@gmail.com>
To: iec...@googlegroups.com
Cc: Arizona ColdFusion User Group <azc...@yahoogroups.com>
Cecil Champenois
I have SQL Server 2008 loaded to my local machine and I reference it as 127.0.0.1 for the server name.
Is this a Microsoft conspiracy, or did I only miss something? Maybe one step somewhere?
From: Cecil Champenois <cecilch...@yahoo.com>
To: iec...@googlegroups.com
Sent: Sun, January 2, 2011 11:31:14 AM
Charlie Griefer
is failing. Are you sure you're entering the correct username and
password in the CF Admin?
On Sun, Jan 2, 2011 at 12:31 PM, Cecil Champenois
Cecil Champenois
From: Charlie Griefer <charlie...@gmail.com>
To: iec...@googlegroups.com
Cc: Arizona ColdFusion User Group <azc...@yahoogroups.com>
Mujeebur Rahmansaher
If you are having trouble establishing a connection to SQL Server, review the following considerations:
- If you installed SQL Server using a server name other than the default, you must use your chosen domain\servername wherever there's a reference to (local).
The following situations can cause a Connection Refused error:
- If you specified authentication information in SQL Server, ensure that you have not defined a username and password in the ColdFusion data source.
- You are running a connection-limited version of SQL Server and the request exceeds the limit for TCP/IP connections.
You can prevent this exception by setting the Limit Connections and Restrict Connections To options in ColdFusion Administrator on the Advanced Settings page for the data sources, and specifying a number less than the SQL Server maximum.
- SQL Server does not enable the TCP/IP protocol. This problem can happen when SQL Server is on the same computer as ColdFusion. To fix this problem, perform the following steps:
- In SQL Server Enterprise Manager, right-click on the name of your SQL Server and click Properties.
- Click Network Configuration and the General Tab.
- Move TCP/IP from the Disabled Protocols section to the Enabled Protocols section.
- Click OK.
- Restart the SQL Server services.
- Verify your data source.
- Move TCP/IP from the Disabled Protocols section to the Enabled Protocols section.
Cecil Champenois
My server name is "TEXAS-PC". I have tried that, as well as 127.0.0.1, and localhost, also.
I made one login ID for the database, that being "cf" and a password.
I have set it to Windows Authentication and SQL Server Authentication (Mixed Mode).
The TCP/IP protocol has been enabled for SQL Server 2008 and port 1433 added to all IP addresses, except IP ALL, as that stopped SQL Server from connecting.
And I at first did not have a username or password for the data source.
I have restarted SQL Server several more times than I have children. :-)
I will go back and remove the username and password from the data source.
Now, the one thing I have not done is ti look at the limit connections in the CF Admin. I'll look at that now.
Thanks!
From: Mujeebur Rahmansaher <sah...@gmail.com>
To: iec...@googlegroups.com
Sent: Sun, January 2, 2011 11:57:43 AM
Cecil Champenois
The one thing I had forgotten about was to enable each individual TCP/IP or rather IP1, and IP2. I am restarting SQL Server to see if this makes any difference. I have literally spent a couple days on this. If it works, you might even hear me screaming out loud across the ocean.
Ezra Parker
I'm not sure if you've resolved this or not, but if not, then from what you've described I'm not sure if you've created a user for the DB in question. In newer versions of SQL Server, there is a distinction between a login, which is server-wide, and a user, which is specific to a particular DB. What you'd want to do is first create a login with the desired password, and then create a user for your DB that references that login (you enter it in the "Login name" field when creating a user).
HTH,
--
Ezra
Cecil Champenois
From: Ezra Parker <ez...@cfgrok.com>
To: iec...@googlegroups.com
Sent: Mon, January 3, 2011 12:05:43 PM
Ezra Parker
--
Ezra
rex
Like Charlie Griefer and other people have mentioned, It shows that you can connect, since the error you are getting is a login error.
A connection error would say something like "Error establishing socket to host and port" and/or "Connection refused"
Using SQL Server Management Studio, can you login with the same username/password? Use the "sa" user just to confirm that you can indeed login.
Also, try using another SQL client, like Query Express from http://www.albahari.com/queryexpress.aspx and I'm sure if you can't get CF to connect, that client wouldn't be able to connect as well. I don't think that it's a problem only with ColdFusion.
There is a service called SQL Server Browser. Is that running?
Are you on a named-instance installation? Something like LOCALHOST\INSTANCE1?
This conspiracy is out to get us. :-)
- Rex
Cecil Champenois
Here's one:
Connection verification failed for data source: ows
java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running.
The root cause was that: java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running.
My Server name that I put in is the name of the PC/SQL Server name. That doesn't work. "TEXAS-PC/SQLSERVER2008"
Then I tried "localhost" and "127.0.0.1" and neither of those worked.
Connection verification failed for data source: ows
java.sql.SQLInvalidAuthorizationSpecException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'cf'.
The root cause was that: java.sql.SQLInvalidAuthorizationSpecException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'cf'.
It doesn't like the username I guess. It didn't say anything about the Server name of localhost, so maybe that is good?
I will recheck that there is a user name in the database itself. There is one for sure in SQL Server 2008 that is called "cf"..
Cecil
Cecil Champenois
Connection verification failed for data source: ows
java.sql.SQLInvalidAuthorizationSpecException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'cf'.
rex
Cecil Champenois
BurrowOwl
Server. So for you I think it would be:
TEXAS-PC\SQLSERVER2008
-Robert
On Jan 4, 2:25 pm, Cecil Champenois <cecilchampen...@yahoo.com> wrote:
> Yes, I'll try that.
>
> Cecil
>
> ________________________________
> From: rex <li...@pgrworld.com>
> To: iec...@googlegroups.com
> Sent: Tue, January 4, 2011 2:19:55 PM
> Subject: Re: [IECFUG] Can't get SQL Server database connected through CFADMIN
>
> can you try the SA user please?
>
> On 1/4/2011 1:09 PM, Cecil Champenois wrote:
> I have just now assured that there is a login ID called "cf" and it
> pertains to both the SQL Server itself and also to the specific database
> called "OWS".
>
> >Connection verification failed for data source: ows
> >java.sql.SQLInvalidAuthorizationSpecException: [Macromedia][SQLServer
> >JDBC Driver][SQLServer]Login failed for user 'cf'.
> >The root cause was that:
> >java.sql.SQLInvalidAuthorizationSpecException: [Macromedia][SQLServer
> >JDBC Driver][SQLServer]Login failed for user 'cf'.
>
> Cecil
>
> ________________________________
> From: Ezra Parker <e...@cfgrok.com>
>
> >To: iec...@googlegroups.com
> >Sent: Tue, January 4, 2011 11:37:07 AM
> >Subject: Re: [IECFUG] Can't get SQL Server database connected
> >through CFADMIN
>
> >So what is the error you are currently seeing when you attempt to
> >verify the DSN in the CF admin?
>
> >--
> >Ezra
>
> >On Mon, Jan 3, 2011 at 4:22 PM, Cecil Champenois
> ><cecilchampen...@yahoo.com> wrote:
>
> >Yep, I considered that also, Ezra. I am beginnign to think
> >that there is a communsit conpsirtacy between Microsoft and
> >Adobe to make difficulties between SQL Server and the
> >CFADMIN. I spent all day with a very kind ColdFusion
> >developer today trying to get this up and running; although
> >we made some progress, we ran into one road block after
> >another. We still cannot get CFADMIN to connect to the
> >database.
>
> Cecil
>
> ________________________________
> From: Ezra Parker <e...@cfgrok.com>
> From: Mujeebur Rahmansaher <sahe...@gmail.com>
>
> >>>To: iec...@googlegroups.com
> >>>Sent: Sun, January 2, 2011 11:57:43 AM
>
> >>>Subject: Re: [IECFUG] Can't get SQL
> >>>Server database connected through
> >>>CFADMIN
>
> >>>I second Charlie Griefer on the
> >>>error. If it does not work with
> >>>the correct username and
> >>>password... Also make sure you
> >>>have Mixed Mode (Windows
> >>>Authentication and SQL Server
> >>>Authentication) enable on your
> >>>local sql server.
>
> >>>If you are having trouble
> >>>establishing a connection to
> >>>SQL Server, review the
> >>>following considerations:
> >>> 1. If you installed SQL
> >>>Server using a server name
> >>>other than the default,
> >>>you must use your chosen
> >>>domain\servername wherever
> >>>there's a reference to
> >>>(local).
>
> >>>The following situations can
> >>>cause a Connection Refused
> >>>error:
> >>> * If you specified
> >>>authentication information
> >>>in SQL Server, ensure that
> >>>you have not defined a
> >>>username and password in the
> >>>ColdFusion data source.
>
> >>> * You are running a
> >>>connection-limited version
> >>>of SQL Server and the
> >>>request exceeds the limit
> >>>for TCP/IP connections.
>
> >>>You can prevent this
> >>>exception by setting the
> >>>Limit Connections and
> >>>Restrict Connections To
> >>>options in ColdFusion
> >>>Administrator on the
> >>>Advanced Settings page for
> >>>the data sources, and
> >>>specifying a number less
> >>>than the SQL Server
> >>>maximum.
> >>> 1. SQL Server does not
> >>>enable the TCP/IP
> >>>protocol. This problem can
> >>>happen when SQL Server is
> >>>on the same computer as
> >>>ColdFusion. To fix this
> >>>problem, perform the
> >>>following steps:
>
> >>> 1. In SQL Server
> >>>Enterprise Manager,
> >>>right-click on the
> >>>name of your SQL
> >>>Server and click
> >>>Properties.
>
> >>> 2. Click
>
> ...
>
> read more »
Sami Hoda
If this is a new instance of SQL Server - you need to enable remote connections. By default, SQL Server will reject all remote connections. Then follow the earlier steps in enabling TCP/IP or named pipes.
Sami
Cecil Champenois
From: Sami Hoda <sa...@bytestopshere.com>
To: iec...@googlegroups.com
Sent: Thu, January 6, 2011 11:00:20 AM
Subject: Re: [IECFUG] Re: Can't get SQL Server database connected through CFADMIN