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

...

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

• Edited by Andrea LamMicrosoft employee 6 hours 22 minutes ago

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

• Edited by Andrea LamMicrosoft employee 6 hours 11 minutes ago

Hi Andrea,

Thanks for your message.

I'm not sure about is meant by enabling remote connections.
I'm trying to connect to data already located in SQL Azure.  The MW successfully transferred the data up. Plus I can repeatedly do so.  MW has no problem connecting to the Azure database servers.  It's connecting with ODBC that is the issue.
I'm also unsure about the firewall suggestion. Is configuring port 1433 necessary on the client machine trying to access the data? 

I had noticed the SQL Native Client config had Named pipes enabled.  I since disabled it, leaving only TCP/IP and Shared Memory enabled.  How can I check that the connection string is enforcing TCP/IP? Can I manipulate the connection string ?

Thanks so much for your help, this is ageing me ;-)

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

• Edited by Andrea LamMicrosoft employee Saturday, August 29, 2015 12:58 AM

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

• Edited by Andrea LamMicrosoft employee Saturday, August 29, 2015 12:58 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 19 hours 43 minutes ago readability

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 19 hours 53 minutes ago readability

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

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

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 :-)