Skip to main content
A
alecken
Known Participant
April 16, 2022
Question

How to create datasource in CF administrator 2011 to local SQL Server

  • April 16, 2022
  • 2 replies
  • 735 views

Hello all, I just downloaded the free CF2011 developer version and also SQL Server 2019 developer version both to my laptop. My Laptop is running windows 10. I tested my CF 2011 is working and also my MSSMS is working, created a test database, tables.

When launching MSSMS I think by default the Connect to Server Windows is set to Windows Authentication. All I need is to click the connect button and I'm connected to the SQL SERVER. Now that I need to create a datasource from my CF administrator I have an issue. 

After I fill in the CF Datasource name, Server name, Database name and the default port 1433 error show up in red after I clicked the submit button:

Connection verification failed for data source: TestDB
java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]Error establishing socket to host and port: MYOWNPC:1433. Reason: Connection refused: connect

 

Do I have to uninstall SQL server and re-install it as I wasn't sure if I have to provide a username and password in the begining of installation. Maybe that's the reason Microsoft by default chose Windows Authentication for me? What do I need in order for me to be able to create the datasource, thank you

 

    This topic has been closed for replies.

    2 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    April 16, 2022

    2011 is a typing error. You very likely mean 2021.

    A
    aleckenAuthor
    Known Participant
    April 20, 2022

    Yes, I mistakenly tyoed 2011 instead of 2021. Thank you

    Charlie Arehart
    Community Expert
    Charlie ArehartCommunity Expert
    Community Expert
    April 16, 2022

    This is a classic problem that happens to so many that it's the most popular blog post I have. To be clear, it's not a cf problem but a sql server one--and not one that Cf can in way control, which is why people so often hit it.

     

    The connection refused means that Cf could not reach that port. Since you say it's on the same machine, then perhaps that port is not being exposed by sql server. How could that happen? It could happen of you'd not yet exposed a tcpip connection for sql server. 

     

    But you can connect to it with ssms, you'd say. Well, ssms (or mssms, as you call it) is not connecting via tcpip. So sure, it works.

     

    So you'd need to enable tcp in sql server. Sadly, you can't do it in ssms. Instead, you need to use sql server's configuration manager. And that's what my post goes on to elaborate--tracking also how things have changed over time, as I've updated in the post:

     

    https://www.carehart.org/blog/2006/7/8/sql2k5_Error_establishing_socket

     

    Note how I outline at the top a tldr version of the answer, then far more detail for those who may want it. 

     

    Let us know if that does (or does NOT) help. There could be still other possibilities, but again this is the most common cause and solution. 

    /Charlie (troubleshooter, carehart. org)
    A
    aleckenAuthor
    Known Participant
    April 20, 2022

    Hi Charlie, Thank you for helping. I've been googling this past few days to find the answer with no success.

    I actually followed your instruction to enabled TCP\IP. It was disabled but now it has been enabled. Then I restarted SQL Server service and tried to create datasource again, Still unsuccessfull. 

    I logged in to my SQL Server with Windows Authentication. sa is still disabled by default.

    When creating the datasource I entered :

    Cf Data Source Name, Database, Servver Name and Port. I tried Port 1433 and 1434 and leaving the username and password blank. I don't know what is the username and password since I did not enter any when installing sql server. 

    The error I got no matter what I Port I used is the same :

    Connection verification failed for data source: MyTestDB
    java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]Error establishing socket to host and port: MYOWNPC:1433. Reason: Connection refused: connect

     

    When I right Clicked the TCP/IP and selected Properties and than selected on IP Address tab I see:

    IP1:

    Active                        : Yes

    Enabled                      : No

    IP Address                  : fe:80::75...etc

    TCP Dynamic Ports   : Blank Not even 0

    TCP Port                     : 1433

     

    IP10:

    Active                        : Yes

    Enabled                      : No

    IP Address                  : 127.0.0.1

    TCP Dynamic Ports   : Blank Not even 0

    TCP Port                     : 1433

    The values of IP for the rest look the same except the IP Address

     

    I also found how to Identify Port used by SQL Server Database Engine Using Application Event Viewer. Not sure if this is even necessary as I'm no sql server guru. But the result looks like this:

    In the Even properties - Event 26022, MSSQLSERVER Box:

    General tab:

    Server is listening on [127.0.0.1 <ipv4> 1434].

    Log Name              : Application

    Source                    : MSSQLSERVER              Logged           : 4/17/2022 1:37:07 PM

    Event ID                : 26022                                 Task Category: Server

    Level                     : Information                         Keyword       : Classic

    User                      : N/A                                      Computer      : MYOWNPC     

    OpCode                : Info

    More information : Event Log Online Help

     

    Yes, it was a type when I submitted my question. I'm using CF 2021. Had CF can use access I can just use access for my small program it is much easier but it is no longer supported.

    Should I enabled sa login for the SQL Server in order to make this work?

     

     

     

     

     

     

     

     

     

    Charlie Arehart
    Community Expert
    Charlie ArehartCommunity Expert
    Community Expert
    April 20, 2022

    Your event viewer log entry says its listening on 1434. Have you tried that in the cf admin? Does it then give a NEW error on verifying? 

    /Charlie (troubleshooter, carehart. org)
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices