By
Published
Mar 13 2019 06:48 PM
28.2K
Views
First published on MSDN on Sep 12, 2017
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 .
It is important to note that this same behavior can be experienced with Azure DB for MySQL and Azure DB for PostgreSQL instances as well as they use the same gateway process as Azure SQL DB. In those situations, you will see an error like so for MySQL:
The server name you tried cannot be found. Please use the correct name and retry. Please check your server name mydomain.com.
For PostgreSQL:
FATAL: Cannot connect to the server mydomain.com
The same mitigation should apply by adding the username with @server at the end. This is already a requirement for connections but need consideration with usernames including the @ symbol.
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 .
It is important to note that this same behavior can be experienced with Azure DB for MySQL and Azure DB for PostgreSQL instances as well as they use the same gateway process as Azure SQL DB. In those situations, you will see an error like so for MySQL:
The server name you tried cannot be found. Please use the correct name and retry. Please check your server name mydomain.com.
For PostgreSQL:
FATAL: Cannot connect to the server mydomain.com
The same mitigation should apply by adding the username with @server at the end. This is already a requirement for connections but need consideration with usernames including the @ symbol.
2 Comments