Providing the server name explicitly in user names Part 2: DNS alias for Azure SQL Database


In continuation of the previous blog on needing to provide the server name in your username when connecting, there are other scenarios and new features where this will need to be taken into consideration or make unnecessary!

Often times users will want to use custom domains for connecting to their servers, Example:

sqlprod01.database.windows.net is our Azure SQL DB Server

sql.mycompany.com is a CNAME record that points to the sqlprod01.database.windows.net record for our server.

This, like the previous blog scenario will result in an error when trying to connect if we do not provider our server name in the user.

As we are connecting to 'sql.mycompany.com' it is seen as 'sql' for the servername (highest level of the hostname provided). So while it can reach the gateway in Azure, we cannot find the server 'sql' as we want to connect to sqlprod01. Sure we can add @sqlprod01 to our username, myuser@sqlprod01, however this in many ways defeats the purpose of the hostname we are using in the first place.

With one of the new options available for Azure SQL DB Server, we are able to make a DNS Alias names for our server which can eliminate this need.

https://docs.microsoft.com/en-us/azure/sql-database/dns-alias-overview

We can create an easily mappable alias that can be both an easier to read server name but also allow for an application to have a hostname using the alias which will not require a change even if the corresponding database was moved to a new server.

Let's take a scenario where we have three servers:

eastusserver001.database.windows.net
eastusserver002.database.windows.net
eastusserver003.database.windows.net

These servers are hosting many databases for many different clients. We will often move the databases around to balance for our needs but also may not want to give this name to our clients who want to connect from tools like SSMS or their applications. Using Powershell, we are able to add and manage aliases for these servers in order to make custom aliases for each tenant. Here is an example script on this but will follow the cmdlets listed here.

#Login Process Here
New-AzureRmSqlServerDnsAlias -ResourceGroupname 'myresourcegroup' -ServerName eastusserver001 -DnsAliasName customer01
New-AzureRmSqlServerDnsAlias -ResourceGroupname 'myresourcegroup -ServerName eastusserver001 -DnsAliasName customer02
New-AzureRmSqlServerDnsAlias -ResourceGroupname 'myresourcegroup' -ServerName eastusserver001 -DnsAliasName customer03

We then list aliases for the server and see each one assigned to the eastusserver001 server.

Get-AzureRmSqlServerDnsAlias -ResourceGroupname 'nilop-eastus-rg' -ServerName eastusserver001 | Select DnsAliasName, ServerName, ResourceGroupName

DnsAliasName ServerName ResourceGroupName
------------ ---------- -----------------
customer01 eastusserver001 myresourcegroup
customer02 eastusserver001 myresourcegroup
customer03 eastusserver001 myresourcegroup

With this I can now have custom domain entries that are not only easier to read but can avoid having to put the server name in our connection string.

Now if for some reason we needed to move the customer's database to eastusserver002, the alias can be assigned to the eastusserver002 server using the Set-AzureRmSqlServerDnsAlias cmdlet. They can maintain the easy to read alias and do not need to update their connection strings.


Comments (0)

Skip to main content