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

    Întrebare

  • 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

    marți, 6 septembrie 2016 05:02

Răspunsuri

Toate mesajele

  • 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

    marți, 6 septembrie 2016 06:45
  • 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.
    marți, 6 septembrie 2016 20:32
  • 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)

    marți, 6 septembrie 2016 22:43
  • So to clarify - to move a local database to SQL Server on Azure I need to change my LOCAL authentication to SQL?

    Karl

    miercuri, 7 septembrie 2016 01:15
  • 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

    miercuri, 7 septembrie 2016 01:16
  • 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

    miercuri, 7 septembrie 2016 01:21
  • Yes, or use Azure Active Directory Authentication
    miercuri, 7 septembrie 2016 18:26
  • Is AADA even an option for local SQL Server instances?

    Karl

    miercuri, 7 septembrie 2016 20:43
  • 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.
    vineri, 9 septembrie 2016 17: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.
    joi, 6 iulie 2017 01:47
  • 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

    joi, 6 iulie 2017 07:56
  • 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
    ------------------------------

    miercuri, 4 octombrie 2017 13:40
  • I'm having the same issue, just change the database name "new database name" to some other alternate name, and it will work
    vineri, 20 octombrie 2017 05:50
  • 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.

    vineri, 9 februarie 2018 21:32
  • Did you ever figure out what the problem was?
    marți, 15 mai 2018 02:30