locked
ODBC connection to Azure SQL using SQL Native Client 10 fails RRS feed

  • Question


  • 2 days; I'm stumped. 

    I created 2 Azure database servers, each with a single database.

    "k7lyvky04f" was created with the classic portal.  It contains a database called "asdf"

    dataserver was created with the preview portal. It contains a database called "data"


    I am able to migrate an on-premise database up into both servers, using SQLAzureMW v3.15.6.   I was joyfully able to connect with ODBC to the master database of the "k7lyvky04f" server on the first try.  I could not, however access "asdf", which contains the migrated data.

    I tried forums etc, and tried many things to try to resolve this, but now, a more serious issue has popped up, I can't connect with ODBC at all.  Not even the master. I built a variety of different vms to see if the problem stemmed from the development machine's config, but not even the most current machine will connect to either of my two databases.  This leads me to believe the problem stems from the Azure side, but I made no changes there other than to allow more ip addresses in the azure-sql-configuration tab.   Also, the migration wizard *still* connects and uploads to both servers.

    Here is an example ODBC and result: 

    Microsoft SQL Server Native Client Version 10.50.1600

    Data Source Name: data on dataserver
    Data Source Description: data on dataserver
    Server: Adminsa@dataserver.database.windows.net
    Use Integrated Security: Yes
    Database: (Default)
    Language: (Default)
    Data Encryption: No
    Trust Server Certificate: No
    Multiple Active Result Sets(MARS): No
    Translate Character Data: Yes
    Log Long Running Queries: No
    Log Driver Statistics: No
    Use Regional Settings: No
    Use ANSI Quoted Identifiers: Yes
    Use ANSI Null, Paddings and Warnings: Yes
    Server Service Principle Name(SPN): 
    Mirror Server Service Principle Name(SPN): 

    Microsoft SQL Server Native Client Version 10.50.1600

    Running connectivity tests...

    Attempting connection
    [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [53]. 
    [Microsoft][SQL Server Native Client 10.0]Login timeout expired
    [Microsoft][SQL Server Native Client 10.0]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!
    In Sql Server Configuration manager, Protocols for MSSQLServer TCP-IP is enabled, and Named pipes are disabled.

    Any ideas?

    ..update: I noticed the SQL Native Client config had Named pipes enabled.  I disabled it, leaving only TCP/IP and Shared Memory enabled.  Now the error reads:

    Attempting connection
    [Microsoft][SQL Server Native Client 10.0]TCP Provider: No such host is known.
    [Microsoft][SQL Server Native Client 10.0]Login timeout expired

    ...


    Thanks! Dan



    • Edited by Dannym_asdf Friday, August 28, 2015 9:06 PM
    Friday, August 28, 2015 8:40 PM

Answers


  • Works now!

    Needed SSMS from SQL 2008 R2.   Just the Native Client (sqlncli.msi) wasn't enough 


    plus, in ODBC manager, need the entire server url, not just the server name - 

    Which Server to connect:    k7lyvky04f.database.windows.net  
    LoginID: Adminsa@k7lyvky04f.database.windows.net     (not just Adminsa@k7lywku04f)

    Cheers :-)


    Thanks! Dan

    • Marked as answer by Dannym_asdf Saturday, August 29, 2015 12:26 PM
    Saturday, August 29, 2015 12:26 PM

All replies

  • Hi Dannym_asdf,

    Can you check that your connection string is enforcing TCP/IP? It looks like you're attempting a connection through Named Pipes. For TCP/IP to work properly, you will have to configure port 1433 outgoing firewall rule. More information can be found here.

    Would you be able to provide more details about your version of SQL Server? Are you running SQL Server 2014? I want to make sure that the SQL Database Migration Wizard you're using is compatible with the version of SQL Server you're using.

    You'll also need to ensure that your on-prem is enabled for remote connections.

    Hope this helps.

    Thanks,

    Andrea




    Saturday, August 29, 2015 12:42 AM
  • Hi Andrea,

    Thanks for your message.

    The issue is with connecting to SQL Azure with ODBC.  
    I'm trying to connect to data already located in SQL Azure. 
    The Migration Wizard successfully transferred the data up. 

    How can I check that the connection string is enforcing TCP/IP? Can I manipulate the connection string ?


    Thanks for your help!

    Danny


    • Edited by Dannym_asdf Saturday, August 29, 2015 11:25 AM readability
    Saturday, August 29, 2015 1:44 AM

  • Works now!

    Needed SSMS from SQL 2008 R2.   Just the Native Client (sqlncli.msi) wasn't enough 


    plus, in ODBC manager, need the entire server url, not just the server name - 

    Which Server to connect:    k7lyvky04f.database.windows.net  
    LoginID: Adminsa@k7lyvky04f.database.windows.net     (not just Adminsa@k7lywku04f)

    Cheers :-)


    Thanks! Dan

    • Marked as answer by Dannym_asdf Saturday, August 29, 2015 12:26 PM
    Saturday, August 29, 2015 12:26 PM