Providing the server name explicitly in user names for Azure SQL DB


Often, users will want to create their SQL Logins in the user@mydomain.com format.  While a perfectly valid format, this can lead to login failures if not accounted for.

Cannot open server "mydomain.com" requested by the login.  The login failed. (Microsoft SQL Server, Error: 40532)

Below you can see that my server has been entered as myserver.database.windows.net but the error is trying to login to mydomain.com.

To correct this, add the @myserver to the end of the user name, user@mydomain.com@myserver.  It is important to note this is specific to SQL Logins and AAD authentication methods are not impacted by this.

Now why this happens? In Azure SQL DB, each region shares a common main endpoint or gateway to start your connection process.  When your connection is initially started, it will resolve your full server name, in this case, myserver.database.windows.net. This gateway is then able to review the connection details to determine the specifics of your server and where it resides in that region.  It will then provide that information to the client to proceed to connect to your specific instance.  In a scenario such as with SSMS, this is determined by looking at the user name and then if necessary, the server name.

As my user is user@mydomain.com, it will initially speak to the gateway which is resolved from myserver.database.windows.net and ask for the details of mydomain.com as that is part of the user name provided after the @ symbol.  By adding the @server to the end of my overall user, it is able to essentially 'override' what SSMS thinks is the server provided in the user name by explicitly stating the server as well. You will notice that even the provided ODBC connection strings in the portal for a database use the user@server syntax by default for the user due this same of behavior.

Another scenario where you may find this explicit syntax necessary is when using the IP that is resolved from your full server FQDN rather than the FQDN of your server itself.  While not suggested as the IP is possible to be changed, following the same logic, even with a normal SQL Login such "user" you will need to explicitly provide the server name after the user.  The reason being the IP will allow you to access gateway but there is no identification of the server needing to be accessed otherwise. Below is the South Central US gateway IP but as I have no server provided at any point, it is not able to determine how to connect. To remedy this I would need to have my user as user@myserver.

 


Comments (0)

Skip to main content