16 Replies Latest reply on Aug 17, 2015 5:39 PM by larrym78096085

    Can't connect CF 9 to MSSQL 2008 R2

    didgeralien

      I hope some help is out there. I will post the error message at the end of this post. I can't verify a datasource in Administrator. this is the error:

      Connection verification failed for data source: DHDataWeb_Dev

       

      java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer  JDBC Driver]Error establishing socket to host and port: 127.0.0.1:1433.  Reason: Connection refused: connect

      The root cause was that:  java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC  Driver]Error establishing socket to host and port: 127.0.0.1:1433.  Reason: Connection refused: connect

       

      Any help would be greatly appreciated. I know this should be very easy, but I can't get it to work. I am running Windows 7 SP1, My SQL database, web server (IIS 7) and CF server are all running on the same box.

       

      Thanks in advance.....

      Didger (who is most confused)

        • 1. Re: Can't connect CF 9 to MSSQL 2008 R2
          carl type3 Level 4

          Hi didger,

           

          Firewall is not blocking port 1433?

           

          The tcp protocol is enabled in "SQL Server Configuration Manager" > "Network Configuration"?

           

          HTH, Carl.

          • 2. Re: Can't connect CF 9 to MSSQL 2008 R2
            Gary1 Level 1

            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

            • 3. Re: Can't connect CF 9 to MSSQL 2008 R2
              kayolhope Level 1

              I'm having the exact same issue, does anyone have an answer to this?

              • 4. Re: Can't connect CF 9 to MSSQL 2008 R2
                Owain North Level 4

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

                • 5. Re: Can't connect CF 9 to MSSQL 2008 R2
                  kayolhope Level 1

                  D:\>telnet XXX.XXX.XXX.XXX 1433

                  Connecting To  XXX.XXX.XXX.XXX ...Could not open connection to the host, on

                  port 1433: Connect failed

                   

                  Nope, but I can get to the CF9 Admin Panel and had enabled TCP within

                  Windows firewall which wasn't enabled before:

                   

                  From Documentation Notes:

                   

                  - After researching changing the the instance name back to the default port

                  of 1433 for ms sql 2008 r2, had a feeling TCP wasn't turned on as it is not

                  by default along with not being able to telnet to it prior. Enabled TCP

                  through remoting into sql box, Start -> All Programs -> Microsoft SQL

                  Server 2008 R2 -> Configuration Tools -> SQL Server Configuration Manager

                  then, on folder tree - SQL Server Network Configuration -> Protocols for

                  SQLEXPRESS  (source: https://www.youtube.com/watch?v=KD8PI72MK6w) finally,

                  restarted server for changes to take effect

                  - Data Source still given same error and will need to check port being

                  listen on.

                  - Next, allowed sql through allows programs of Windows firewall through Dev

                  Data Server by going to Start -> Control Panel -> System and Security ->

                  Windows Firewall -> Allow a Program through Windows Firewall. This is to

                  allow packets to travel across the network without getting blocked. Went

                  into allow another program and Browsed to and selcted "D:\Program

                  Files\Mircosoft SQL Server\MSSQL.(name of

                  instance)\MSSQL\Binn\sqlservr.exe" and  added. SQL Browser was already

                  opened.

                  - Headed over to MS Windows Firewall Advanced Security and right clicked on

                  "Inbound Rules", "New Role", choose Port from the radio button, next,

                  choose TCP as port type and specified local port 1433, next, allow

                  connection. Checked Domains and private, with Public unchecked to apply to.

                  Next and names MS SQL Server 2008 R2. With this now linked and server

                  restarted should connect to ms sql 2008.

                  - Now getting logon error msg when verifying datasource as follows:

                   

                  Connection verification failed for data source: DATABASESOURCENAME

                  java.sql.SQLInvalidAuthorizationSpecException: [SQLServer JDBC

                  Driver][SQLServer]Login failed for user ' MSSQLBOX2008\Administrator'.

                  The root cause was that: java.sql.SQLInvalidAuthorizationSpecException:

                  [SQLServer JDBC Driver][SQLServer]Login failed for user

                  'MSSQLBOX2008\Administrator'.

                   

                  - The port is now listening and connected according to port scan

                  - Not getting any connect refused error anymore when verifying data source

                  in CF9 Admin

                  - Not sure if Server is setup to not respond to Telnet requests

                  - Using MS Server 2008 R2 w/ MS SQL Server 2008 R2 and CF 9 Development

                  Edition

                  - Server is connected remote

                  - Web box uses IIS7

                  • 6. Re: Can't connect CF 9 to MSSQL 2008 R2
                    Owain North Level 4

                    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.

                    • 7. Re: Can't connect CF 9 to MSSQL 2008 R2
                      kayolhope Level 1

                      I believe  SQL Server and Windows Authentication mode is setup to allow

                      proper authentication credentials for our development environment. We have

                      a "Web Box" running CF9 Developer Edition and Windows Server 2008 R2 with

                      IIS7 along with a "Data Box" that communicates with it running MS SQL

                      Server 2008 R2.

                       

                      My client computer is setup with a remote connection to both boxes with

                      admin permissions.

                      • 8. Re: Can't connect CF 9 to MSSQL 2008 R2
                        Owain North Level 4

                        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

                        • 9. Re: Can't connect CF 9 to MSSQL 2008 R2
                          kayolhope Level 1

                          Yeah, tried that earlier too with a User named "Developer" and got the same

                          error message about it not recognizing login.

                          • 10. Re: Can't connect CF 9 to MSSQL 2008 R2
                            kayolhope Level 1

                            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;)

                            • 11. Re: Can't connect CF 9 to MSSQL 2008 R2
                              Owain North Level 4

                              If you ignore CFAdmin verification and just try adding the DSN then putting the credentials into a CFQuery, does that work? If not, maybe run SQL Profiler to see what's going on?

                              • 12. Re: Can't connect CF 9 to MSSQL 2008 R2
                                kayolhope Level 1

                                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

                                [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?

                                • 13. Re: Can't connect CF 9 to MSSQL 2008 R2
                                  kayolhope Level 1

                                  Aww, looks like the output didn't display on the forum, I'll attempt

                                  to re-post it as it most likely tried to be executed as a command:

                                   

                                  cfscript[closetag]

                                  writeDump(server);

                                  cfscript[closetag]

                                   

                                  CFQUERY NAME="myQueryName" DATASOURCE="OURDATASOURCE"

                                  USERNAME="DATABOXNAME\Developer" PASSWORD="null"[openetag]/CFQUERY[closetag]

                                   

                                  The web site you are accessing has experienced an unexpected

                                  error.

                                  Please Contact your Administrator.

                                   

                                  The following information is meant for the website developer for debugging

                                  purposes.

                                  Error Occurred while Processing Request

                                   

                                  Error Executing Database Query.

                                  [SQLServer JDBC Driver][SQLServer]login failed for user

                                  "DATABOXNAME\Developer'.

                                  • 14. Re: Can't connect CF 9 to MSSQL 2008 R2
                                    kayolhope Level 1

                                    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.

                                    • 15. Re: Can't connect CF 9 to MSSQL 2008 R2
                                      steven.reiser

                                      Most of the time I have had the issue of not being able to set up a datasource for SQL Server it's because the Security Settings on the SQL Server are set to Window Authentication Mode which allows me access via my netork login.  More than a few times I have had to reset it to SQL Server and Windows Authentication Mode, by right clicking on the server name in SQL Server Management Studio and select Properties (bottom of menu) and click on Security in the popup window.  At the top is Server Authentication.  If it's Windows only, switch it to allow both and restart SQL Server in Services (Control Panel Admin Tools), and your SQL Login Access will be activated. If you are using a restored dateabase from another server, you must set the orphaned logins to the new server master tables  USE (your tablename) EXEC sp_change_users_login 'Report' to check for orphaned logins. Use EXEC sp_change_users_login 'Auto_Fix' username to move the authentication to new master tables, then query USER Master SELECT * FROM sys.server_principals WHERE name = yourusername and SELECT * FROM sys.syslogins WHERE name = yourusername to verify your SQL login has been recovered from your database to the master tables in your new location.

                                      • 16. Re: Can't connect CF 9 to MSSQL 2008 R2
                                        larrym78096085

                                        Hey, sorry for the late answer.

                                        All you had to do was to create a new login with the Management Studio for ColdFusion.

                                         

                                        see here a good explanation: http://www.fusioncube.net/index.php/coldfusion-sql-server-express