Migrating SQL database to Azure using SSMS fails with "login failed"

Question

In SSMS, I'm trying to use the "Deploy Database to SQL Azure Database" utility.

I'm moving a small local database in SQL 12 to Azure for hosting. But after entering the host database server connection parameters I always get a "Login Failed for user,...." error, referencing the name of the local database to be created on the server.

Why this would fail is unclear, since the database does not exist on the cloud server yet. Here is what I have done:

• Set up a firewall exception for my local IP address.
• Confirmed my server administrator user name and password 

So now I am left guessing... Locally I use Windows Authentication. If a database is to be moved to the server, does it have to have SQL authentication, as is required on Azure host?

---

Karl

Answer 1

What is error code or rest of message you get?

It can be many different things and error code should tell you more about problem.

---

Mustafa Toroman, Azure MVP

Answer 2

Hi Karl. Your hypothesis is correct. if you are moving a database from on-premise to Azure, you will need to use SQL Server Authentication or Azure Active Directory (AAD) authentication.

Answer 3

you should have the ability to create the database on existing server

so you need to provide the Azure SQL DB admin account name and password

(this can be found on the SQL Server blade in the Azure portal)

Answer 4

So to clarify - to move a local database to SQL Server on Azure I need to change my LOCAL authentication to SQL?

---

Karl

Answer 5

I am not trying to create the database on Azure. If I do that it will not let me "migrate" an existing database there by the same name.

---

Karl

Answer 6

The root error is:

"Cannot open database ** requested by the login. Login failed for user ****. Error 4060.

This is a poorly-implemented error message. It does not clarify whether the problem is with the local database being migrated (which does not use SQL authentication anyway), or whether the problem is with a migrated database trying to be established on Azure that expects SQL authentication and doesn't find it.

The higher level error is:

"The connection is broken and recovery is not possible. The client driver attempted to recover the connection one or more times and all attempts failed. Increase the value of ConnectRetryCount to increase the number of recovery attempts."

---

Karl

Answer 7

Yes, or use Azure Active Directory Authentication

Answer 8

Is AADA even an option for local SQL Server instances?

---

Karl

Answer 9

It's not possible to use Active Directory Active Authentication for local SQL Server instances. However, it is possible (with the blessing of your IT department of course) to federate your on-premises local identity to Azure. Hope this helps.

Answer 10

I had this issue too, but it was caused by an incorrect naming of my target Azure database.  I used an underscore character which is not allowed in Azure SQL DB naming.  Once I changed that underscore to a dash things worked very well.  Be sure you're following Azure's naming rules for your target DB name.

Answer 11

Hi Karl,

As you have mentioned you are migrating the SQL Server database to Azure,i just wanted to know that  is it SQL AZURE (PAAS) or VM in Azure with SQL installed On that VM (IAAS)

If you are using SQL Azure,Can you please let me know if you have tried the below steps.

1. Export the database to a BACPAC file.
2. Import the BACPAC file into an Azure SQL Database.

Sorry if i have mistaken your question as even i am trying to still learn SQL Azure.

Regards

Kiran

Answer 12

I don't understand all this.  I first created the database and then in SQL 2016 I used the Deploy to azure but it seems the database should not be created in Azure first as name conflict occurs.  SO I deleted the database on Azure and retried and again using the Deploy database to Azure it asks for the connection which succeed all right and when clicking Next I receive this message :

From what I see is that the process is asking to connect to azure sql server but it tries to connecto the local database with the provided credentials of Azure which doesn't make any sense  Either I don't know the real process of porting a local database to Azure or this is broken in SQL 2016

TITLE: Microsoft SQL Server Management Studio

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
ADDITIONAL INFORMATION:

The connection is broken and recovery is not possible.  The client driver attempted to recover the connection one or more times and all attempts failed.  Increase the value of ConnectRetryCount to increase the number of recovery attempts. (Microsoft SQL Server, Error: 0)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

------------------------------

Cannot open database "XXXX" requested by the login. The login failed.
Login failed for user 'XXXX'. (Microsoft SQL Server, Error: 4060)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=4060&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Answer 13

I'm having the same issue, just change the database name "new database name" to some other alternate name, and it will work

Answer 14

Justin you are a life saver.  How stupid is it that you can't maintain the same name on Azure as you have locally?!

Well, you trick worked like a charm.  Now, I just need to figure out how to rename the DB on Azure.

Answer 15

Did you ever figure out what the problem was?

Answer 16

You are a god.

Answer 17

Working on Azure student service plan and took me ages to get this sorted, cannot believe it was a simple rename of the db when importing!!! How frustrating error msg is not more meaningful.