none
Unable to create an ODBC User data source on Win 7 32/64 bit clients

    Question

  • SQl Server newbie, so please be gentle.

    New install of SQL Server 2008 Express on a Windows Server 2008 R2 box with Remote Connection enabled, working as expected.

    No mods to firewall.

    Logged on as Administrator with Guest account enabled for a shared folder on drive C:\, no other roles defined.

    Attempting to logon to server SERVER2008R2 using 'With Windows NT authentication using the network login ID'.

    Error message for both 32 and 64 bit clients almost identical:-

    Connection failed:

    SQLState:  '01000'

    SQL Server Error:2

    [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen

    (Connect()).

    Connection failed:

    SQLState: '08001'

    SQL Server Error:17

    [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

    Thanks in advance

    Friday, February 10, 2012 5:30 PM

Answers

All replies

  • Hello Chris,

    Which "Server name" did you used for logon to SQL Server?

    By default SQL Server Express Edition is installed as named instance "SQLEXPRESS", so you have to add the instance name to the server name, like:

    YourMachineName\SQLEXPRESS


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Friday, February 10, 2012 5:34 PM
  • Olaf - thanks for your reply.

    Tried that option previously but with slightly different error messages.

    32 bit:-

    SQLState:  '01000'

    SQL Server Error:11001

    [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen

    (Connect()).

    Connection failed:

    SQLState: '08001'

    SQL Server Error:6

    [Microsoft][ODBC SQL Server Driver][DBNETLIB]Specified SQL Server not found.

    64 bit:-

    SQLState:  '01000'

    SQL Server Error:67

    [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen

    (Connect()).

    Connection failed:

    SQLState: '08001'

    SQL Server Error:17

    [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
    Friday, February 10, 2012 6:12 PM
  • Hi Chris,

    Please open your SQL Server configuration manager: click "start"--"All programs"-- "SQL Server 2008"-- "Configuration tools" --"SQL Server configuration manager"

    In "SQL Server 2008 Services" tab, you will see some SQL Server services. Ensure they are started. There should be some services like "SQL Server(SQLEXPRESS)". The name in the bracket is the instance name. You can connect to your SQL Server using the format:

    computername\instancename

    For example: your machine name is Mycomputer(you can find the machine name in the computer property), you instance name(in the bracket) is SQLEXPRESS. So you can connect to your SQL Server using "Mycomputer\SQLEXPRESS" as the server name.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Sunday, February 12, 2012 9:14 AM
    Moderator
  • Iric

    Thanks for your reply.

    SQL Server (SQLEXPRESS) is running

    SQL Server Agent (SQLEXPRESS) is stopped

    SQL Server Browser is running

    I've also open the firewall port as advised here.


    Sunday, February 12, 2012 10:58 AM
  • Since tried adding SQL Server user 'chris' plus password and can connect using 'SQl Server Authentication' option on the Windows Server.

    Tried the same through the ODBC client dialogs using 'SQl Server Authentication' without success.


    Sunday, February 12, 2012 12:15 PM
  • Seems the firewall is the problem.

    With the firewall OFF, access through 'SQl Server Authentication' is enabled.

    The script suggested by Microsoft:-

    @echo =========  SQL Server Ports  ===================
    @echo Enabling SQLServer default instance port 1433
    netsh firewall set portopening TCP 1433 "SQLServer" 
    @echo Enabling Dedicated Admin Connection port 1434
    netsh firewall set portopening TCP 1434 "SQL Admin Connection" 
    @echo Enabling conventional SQL Server Service Broker port 4022  
    netsh firewall set portopening TCP 4022 "SQL Service Broker" 
    @echo Enabling Transact-SQL Debugger/RPC port 135 
    netsh firewall set portopening TCP 135 "SQL Debugger/RPC" 
    @echo =========  Analysis Services Ports  ==============
    @echo Enabling SSAS Default Instance port 2383
    netsh firewall set portopening TCP 2383 "Analysis Services" 
    @echo Enabling SQL Server Browser Service port 2382
    netsh firewall set portopening TCP 2382 "SQL Browser" 
    @echo =========  Misc Applications  ==============
    @echo Enabling HTTP port 80 
    netsh firewall set portopening TCP 80 "HTTP" 
    @echo Enabling SSL port 443
    netsh firewall set portopening TCP 443 "SSL" 
    @echo Enabling port for SQL Server Browser Service's 'Browse' Button
    netsh firewall set portopening UDP 1434 "SQL Browser" 
    @echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
    netsh firewall set multicastbroadcastresponse ENABLE 

    ...does not allow access.

    Any suggestions as to how to configure the firewall to allow access?

    Sunday, February 12, 2012 5:04 PM
  • Chris,

    Please configure your SQL Server and fire wall by following this blog:

    http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx

    Waiting for your response.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 13, 2012 1:21 AM
    Moderator
  • Iric

    Many thanks - hard coding port 1433 resolved the issue.

    Best wishes

    Chris

    Monday, February 13, 2012 8:52 AM