Blog Post

Azure Database Support Blog
2 MIN READ

Lesson Learned #33: How to make "cliconfg" to work with SQL Alias (on-premises) to Azure SQL Azure (PaaS)

Jose_Manuel_Jurado's avatar
Jose_Manuel_Jurado
Icon for Microsoft rankMicrosoft
Mar 14, 2019
First published on MSDN on Mar 24, 2018
I worked on a new situation, when we need to connect to a Azure SQL Database using SQL Alias in the same way that we have SQL Server on-premise. Unfortunately, there is not supported , but we are going to test and make it successfully.

As the alias is not supported in Azure SQL Database let me share with you 3 alternatives:



ALTERNATIVE 1: Using SQL SERVER Management Studio and cliconfg 64 bits - “C:\windows\syswow64\cliconfg.exe”

  • First, lets try to open the cliconfg for 64 bits, we provided the name of the alias, our server name and TCP/IP protocol.





  • Second, we are going to connect to our Azure SQL Database using the alias name.





  • Third, I need to specify in the user name username@servername, because working with Azure SQL Database, all connections will go through a Proxy/Firewall, (you don’t have a physical server you have a logical entity). This firewall/proxy receive the incoming connection and reroute the connection to the server that is running your database (each database that we have in Azure is running in different virtual machines, except, elastic pool that are running multiple databases in the same SQL Instance and hence in same virtual machine)




ALTERNATIVE 2: Using SQL SERVER Management Studio and modify the C:\Windows\System32\drivers\etc\host



  • First, I identified the current IP for proxy/firewall for the server (unfortunately, this IP could change without notice and we cannot provide these IPs for this proxy/firewall but normally it might not change too often.




  • Change the file C:\Windows\System32\drivers\etc\host adding a new entry.





  • Using SQL Server Management Studio, connect to the server, like this:





ALTERNATIVE 3: Using SQL SERVER Management Studio and cliconfg 32 bits - “C:\windows\system32\cliconfg.exe”

  • Unfortunately, I don’t have any operating system in 32 bits, but, following the details of Alternative 1 it should be work.


Enjoy!
Updated Mar 14, 2019
Version 2.0
Alias
azure sql database
cliconfg
host
sql alias
  • juanandmsft's avatar
    juanandmsft
    Icon for Microsoft rankMicrosoft
    Mar 08, 2022

    A few notes on top of the great article:

     

    • On 64-bit OS, you have 64-bit CliConfg (C:\windows\system32\cliconfg.exe) and 32-bit CliConfg (C:\windows\syswow64\cliconfg.exe). Both persist aliases in the registry so can use Group Policy Preferences or scripting to add aliases at scale to "HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo" and "HKLM\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\ConnectTo" respectively. Depending on "bitness" of the client application, it will use one registry location or the other, so it is always good to define aliases in both registry locations.
    • The use of @servername in the login is not needed if the alias matches the Azure SQL DB server name. In the example above, using "yg43q8cjy" as alias instead of "MyDB" removes the need to use @yg43q8cjy in the login dialog.
    • Using CNAME record in DNS pointing, following the example, "yg43q8cjy" to "yg43q8cjy.database.windows.net" also makes the trick, again without @yg43q8cjy in the login, only required if CNAME uses a different hostname.
    • When using Azure AD logins, the custom alias and @servername does not work, so name has to match, and if using CNAME alias, also need TrustServerCertificate=TRUE.

     

    2cts.