none
odbc data source from client to SQL Server Express 2012 on server

    Question

  • I have tried creating an odbc connection from my client Windows 7 computer to SQL Server 2012 Express on our small business server with no success.

    On the server:

    I've configured a fixed port in SQL Server Configuration Manager to allow connections on port 1433 (also getting rid of the '0' in dynamic connections)

    I've restarted the instance in SQL Server Management Studio.

    I've opened the Windows Firewall and created a new Inbound Rule to allow connections on port 1433.

    On the Windows 7 client:

    I've tried creating a odbc data source with both ODBC Driver 11 for SQL Server and also with SQL Server Native Client 11.0.  I can't get either of these to work.  At the end of the creation wizard, I use the "Test Data Source" button and receive the following:

    Microsoft ODBC Driver for SQL Server Version 11.00.2270

    Running connectivity tests...

    Attempting connection
    [Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. 
    [Microsoft][ODBC Driver 11 for SQL Server]Login timeout expired
    [Microsoft][ODBC Driver 11 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

    TESTS FAILED!

    There seems to be a lot of people giving solutions to this but they all seem to cover the same material.  There must be something that I'm missing.

    Saturday, April 06, 2013 10:15 PM

Answers

  • I appreciate everyone's input on this.

    However, I just shut off the Windows firewall on the server and was immediately able to get the client to connect.

    This tells me it's a problem with the firewall and that my inbound firewall rule for allowing connections on port 1433 is not working.

    It may help if I could find a firewall log that shows denied connections and what port the connections are trying to connect through.

    Sunday, April 07, 2013 7:02 PM

All replies

  • You may also need to start SQL Server Management Studio on the SBS computer and connect to the SQLExpress instance, right click on the server name in Server Explorer, open Properties , then check box to enable "allow remote connection" is on the bottom of the dialog.

     

     

     


    Regards, Dave Patrick ....
    Microsoft Certified Professional
    Microsoft MVP [Windows]

    Disclaimer: This posting is provided "AS IS" with no warranties or guarantees , and confers no rights.

    Saturday, April 06, 2013 10:39 PM
  • Hello,

    Just a little add-in to the good proposal of David Dave : SQL Server Express Editions don't accept remote connections on default. Check that the AutoClose property is set to False ( True is specific to the Express Edition and is an horror , same thing for User Instance and AutoShrink )

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Sunday, April 07, 2013 10:46 AM
    Moderator
  • Thanks Dave. 

    I believe what you are referring to is checked.

    On SQL Server 2012 Express it looks like this property is set in the Connections properties page under "Remote Server connections". 

    There is a check box there that says "Allow Remote connections to this server". 

    That checkbox is checked.

    Let me know if this is what you were referring to. 

    Sunday, April 07, 2013 5:24 PM
  • Papy-

    I wasn't able to find the AutoClose property in SQL Server 2012 Express while checking properties.

    I checked the properties by right-clicking on the instance and then selecting "Properties".

    Is the AutoClose property found somewhere else?

    Thanks,

    Darin

    Sunday, April 07, 2013 5:26 PM
  • Can you create successfully the DSN on the server? Can you telnet from Win7?

    example:

    telnet SBS 1433

    You might also check server side in SSMS|Management|SQL Server Logs for errors.

     

     

     


    Regards, Dave Patrick ....
    Microsoft Certified Professional
    Microsoft MVP [Windows]

    Disclaimer: This posting is provided "AS IS" with no warranties or guarantees , and confers no rights.

    Sunday, April 07, 2013 5:41 PM
  • Is the AutoClose property found somewhere else?

    Hello Darin,

    It's no a Server, it's a database Setting, see Setting Database Options


    Olaf Helper

    Blog Xing

    Sunday, April 07, 2013 6:51 PM
  • I appreciate everyone's input on this.

    However, I just shut off the Windows firewall on the server and was immediately able to get the client to connect.

    This tells me it's a problem with the firewall and that my inbound firewall rule for allowing connections on port 1433 is not working.

    It may help if I could find a firewall log that shows denied connections and what port the connections are trying to connect through.

    Sunday, April 07, 2013 7:02 PM
  • Hello DarinLW,

    The AutoClose and AutoShrink properties can be found in the Options TabPage ( 1st and 3rd properties ).Options is the 4th choice in the left panel ( you obtain it by a right-click on the name of the database and select Properties ). For new databases, don't forget to modify the Model ( System  ) database for both properties. Nearly every database is built from the settings of the Model database.

    I have not though about the Windows Firewall as it is so "classical" that it is the 1st thing to check ( according the SQL Server Protocols Blog ).

    For the last remark ( about the log firewall ) in your last post , don't be astonished : I have the same problem and I have never found how to read it and to discover the open ports . Maybe , someone knowing the solution could provide it : it would be useful for both  us and every person having the same problem.

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.


    Sunday, April 07, 2013 7:28 PM
    Moderator
  • Thanks.

    I'll find another forum for the windows firewall, post this dilema there.   If it gets resolved, I'll post the details here.

    Tuesday, April 09, 2013 9:46 PM