none
Unable to add DSN for SQL Server 2008 R2

    Question

  • Hello and thank you for your time,

    This is all being attempted on the server in which SQL server resides.

    I am trying to add a DSN via ODBC Data Source Administrator.  Using SQL Server Native Client 10.0, the dropdown menu for servers does not have anything listed.  Do I use the actual server name (GEMWIN0001), the name of the SQL Server (GEMSQLSERVER), localhost or local?

    Going through SQL Server Configuration Manager, I made sure the TCP Port is set to 1433 (it was by default set to nothing).

    I tried telnet 192.168.217.12 1433, but it failed.  I have port 1433 open on Windows Firewall.  For testing purposes, I disabled the firewall and it still did not work.

    When I attempted to create the DSN (even though I know it is going to fail because the telnet test did not work), I get:

    SQLState: 08001

    SQL Server Error: 53

    Named Pipes Provider: Could not open a connection to SQL Server

    SQLState: HYT00

    SQL Server Error: 0

    Login Time Expired

    I do netstat -an and port 1433 is not displayed.

    Please advise.

    Have a great day,

    Don


    • Edited by DSDoyle Thursday, October 17, 2013 11:29 PM Update
    Thursday, October 17, 2013 11:26 PM

Answers

All replies

  • Hello and thank you for your time,

    This is all being attempted on the server in which SQL server resides.

    I am trying to add a DSN via ODBC Data Source Administrator.  Using SQL Server Native Client 10.0, the dropdown menu for servers does not have anything listed.  Do I use the actual server name (GEMWIN0001), the name of the SQL Server (GEMSQLSERVER), localhost or local?

     


    Hi Don,

    I noticed you had a question about what to use for the actual SQL server.  Would you happen to have network issues - Have you tried Port query tool by microsoft to test?

    http://www.microsoft.com/en-us/download/details.aspx?id=24009

    you can give this a try below:

    You probably have a prompt   "What SQL Server do you want to connect to?"

    Then  it should be:   GEMWIN0001\GEMSQLSERVER
           If your SQL Server is a "NAMED INSTANCE"  (which it sounds like it)

    Also you can try the following below for IP

        192.168.217.12,1433

    -----
    Good luck!
    -Norm

    If this helps, please vote as helpful or mark as correct answer if solves your problem

    Friday, October 18, 2013 2:11 AM
  • Hi Norm,

    I used the port query tool and it comes up with the same thing, 1433 is not open.

    Any ideas why the port isn't open?

    Thanks for your help,

    Don

    Friday, October 18, 2013 2:49 AM
  • Hello Don,

    Please check
    - if SQL Server is running
    - remote connections are enable for the SQL Server: Configure the remote access Server Configuration Option
    - if TCP protocol is enabled: Enable TCP/IP Network Protocol for SQL Server
    - FireWall settings: Configure the Windows Firewall to Allow SQL Server Access

    Can you logon to SQL Server on the server itself (local connection)?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 18, 2013 4:55 AM
    Moderator
  • Hello Olaf,

    Remote access and TCP are enabled.  The firewall is not relevant because this is all being done on the local server.  I really think the issue is port 1433 is not enabled for some reason.  I do a netstat -a and it is not listed.  I cannot telnet to it.

    Any ideas?

    Have a great day,

    Don

    Friday, October 18, 2013 5:06 AM
  • I really think the issue is port 1433 is not enabled for some reason. 

    Hello Don,

    Are you sure that your SQL Server is listening on port 1433? It's the default port, but may it your SQL Server uses a different one; see Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager) how to check the used port.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by DSDoyle Friday, October 18, 2013 10:56 PM
    Friday, October 18, 2013 6:19 AM
    Moderator
  • Hi Olaf,

    I found the problem!  I needed to scroll down to the very bottom of the list of IP address to IPAll and take out the Dynamic Port.  Now I can telnet successfully!

    Have a great weekend,

    Don

    • Marked as answer by DSDoyle Friday, October 18, 2013 10:56 PM
    Friday, October 18, 2013 10:56 PM
  • Just saw posting, that's great Olaf's suggestions solved your problem.  
    Sunday, October 20, 2013 8:27 AM