DNS configuration of Azure SQL MI

Santhamoorthy M 161 Reputation points
2022-04-12T13:52:24.627+00:00

Application is using DNS (ex: DNS1) to connect the sql server(ex:SQL01) now and we are the migrating databases from on-premise sql server to azure sql MI(ex: testmi.database.windows.net). There are 5 SQL MI s are there in one virtual cluster and when we configure the DNS (changing it from SQL01 to testmi.database.windows.net), its resolving to some other name instead of actual MI name. Unable to connect the application because of this issue and we can able to connect only if one of the below approach s followed.

  1. Using FQDN instead of DNS name
  2. Using app username with instance name (ex: test@testmi)

but we dont want to do any changes in the application and we want to use the same DNS name and user name for application connectivity. Please advise how to achieve this.

Azure SQL Database
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,886 Reputation points MVP
    2022-04-12T14:42:43.267+00:00

    You can try what is called a Custom DNS for what you want to accomplish however you are encouraged to use a fully qualified domain name for the Custom DNS because using SQL01 may not be resolved correctly. On this documentation you will have more details about this topic.

    If the application will still be on-premise and only the database will be migrated to Azure, then cliconfg may be an option on this scenario.

  2. GeethaThatipatri-MSFT 27,017 Reputation points Microsoft Employee
    2022-04-13T22:22:17.927+00:00

    Hi, @Santhamoorthy M Thanks for the question and for using the MS Q&A platform.
    Thanks, @Alberto Morillo for your continued support.

    Currently, there are two ways to achieve this and the third is tracking to be published in preview (end of May)

    1. Set up local machines with aliases via clicfg.exe to repoint testmi.database.windows.net to sql01.0123abcd.database.windows.net (FQDN as shown on the SQL MI Connection Strings blade). This works for some drivers and will not require app updates, but does require that the corresponding settings be applied across all computers and compliant drivers be used.
      DNS Alias refer to this document
      clicfg alias could just help with getting the DNS suffix like in the example below
      192871-image.png
    2. Use the username@sql01 login as you noted. SQL Server authentication only and the SQL loginusername@sql01 must exist or a manage identity.

    Please let us know if the information helps.

    Regards
    Geetha