How does this user log in to the database server, when its login is deactivated on the database server?

Question

I've specifically disabled a login on the database server. This is an Azure hosted SQL-as-a-Service database.

alter login [********@hotmail.com] disable

Here's what the login looks like on the server. As you can see, the red X on the icon indicates that it is in fact disabled.

enter image description here

Yet, I'm able to log into the server and access the database for which this user is defined. How is this possible? What then does it mean to disable a login on the database server if it doesn't prevent that account from logging in to the database server?

enter image description here

I've tested this behavior with two different accounts, from two different authentication domains. They both exhibit the same behavior.

What I am trying to do is have the user defined in the database so that the user can access the database on the read-replica server, yet disable (or otherwise remove) the corresponding login from the primary server so that this user will not be able to access the the primary DB on the primary server. However I am finding that, with the Microsoft Entra (Azure AD) account defined as a user on a particular database, it makes absolutely no difference if there is a corresponding login defined on the server, or if there is no login defined on the server, or if there is a login defined on the server but it is specifically disabled. Is this a bug in how logins for external user accounts are handled? My understanding of the login / user relationship is that first there needs to be a login in good standing on the server and that this is the first thing which is checked when logging into a database server. Is this not correct?

New information:

I am seeing this behavior even with SQL Server users. I can simply create a SQL Server user within a database and then login to access that database WITHOUT creating a server login. This behavior is what we would expect from a contained database. (https://learn.microsoft.com/en-us/sql/relational-databases/databases/contained-databases?view=sql-server-ver16) Yet the database is not contained!

enter image description here

So the database is not contained, but connections exhibit all of the behavior we'd expect from containment. Is there partial containment going on here? How would I determine if this is the case? And if this is the case, how would I reconfigure the database so that connections follow the traditional Server login -> Database user model?

• Craig: I don't really know for sure, but maybe there's something slightly unusual about Entra logins on Azure SQL - perhaps you need to DENY SQL CONNECT?
• sepupic: >>>account defined as a user on a particular database<<< Maybe you are using contained database?
• Dan Guzman: Do you see the user in the database under Users?
• Yossi Geretz: @Craig But DENY SQL CONNECT would be at the database level, rather than the server. Issuing that to the database on the primary server would flow through to the database on the replica and now that user would not be able to connect to either of those databases. When working with security for a read-replica we have to be working with server level, rather than database level security. Because at the database level, the read-replica is an exact replica of the primary.
• Yossi Geretz: @DanGuzman Yes, the user is in the database. When working with read-replica security, permission to the database needs to be granted on the primary, rather than the read-replica, since the read-replica is read only. That permission flows through to the read replica. The trick is to then deny the user's login access as the SERVER level so that that user is denied access to the primary server, but permitted access to the read-replica server. This works for SQL Server logins but for some reason doesn't work for Azure AD logins.

Answer 1

Contained database users are always available in Azure SQL Database.

Create a user account

• Create a user account in the database to which a user needs access (also called a contained user).

With SQL Database, you can always create this type of user account. With SQL Managed Instance supporting Microsoft Entra server principals, you can create user accounts to authenticate to the SQL Managed Instance without requiring database users to be created as a contained database user.

https://learn.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage?view=azuresql#create-accounts-for-non-administrator-users

• Yossi Geretz: This is opposite behavior from what I am looking for. I specifically want my accounts to authenticate against a Server login so that I can have two copies of the database on different servers, one the primary database, the other is a read-replica, and control authentication at the server level (via Logins) so that users can connect to the database on the replica server, but not connect to the database on the primary server. Since the database (with its defined Users) is EXACTLY the same for the primary and the replica, the only way I can implement this is at the Server level. Is this possible?
• David Browne - Microsoft: Creating contained database users is optional.
• Yossi Geretz: How do I create a non-contained (i.e. traditional) User which will not authenticate unless a corresponding Login is defined on the Server? And can I do this for an Azure AD account as well as for a SQL Server account?
• David Browne - Microsoft: Just like in regular SQL Server. Create a login in master, and a user mapped to that login in the target database. In master run create login [[email protected]] from external provider then in your database run create user [[email protected]] for login [[email protected]]. Then if the login doesn't exist, the user is "orphaned" and cannot log in.
• Yossi Geretz: THANK YOU! That was my mistake. When I was creating the user I was omitting the capitalized clause: create user [[email protected]] FOR LOGIN [[email protected]]. Evidently, when you omit that clause, the User created is a contained user. It has nothing to do with the Login at the Server level. It is irrelevant whether that login exists, is disabled, or does not exist altogether. The authentication takes place exclusively (contained) in the database. If you want to edit your answer to include your explanation for how to create a non-contained user I'll gladly give you the credit for the answer.

Answer 2

My problem was that I was creating a Server Login and then I was creating a Database User, but I was not connecting the User to the Login. As such, the User was in fact a contained user, even though my database itself is not defined as contained.

The correct syntax (hat tip to David Browne - Microsoft) to create a non-contained (i.e. "conventional") User, tied to its associated Server Login is as follows:

Master: create login [[email protected]] from external provider
App DB: create user [[email protected]] for login [[email protected]]

Then, I deactivate (or delete) the Login in my primary server and create the Login in my replica server (using the first SQL statement, as shown above). This is exactly what I need! Alice can now connect to the database on the replica server, but is unable to connect to the database on the primary server. (Actually, for some strange reason Alice is able to connect to the primary server, can see all the database on the server, but is denied access to all objects within those databases. Not sure why this works in this manner, but the net effect is what I need - Alice is unable to query, or otherwise manipulate, any data from the database on the primary server.)

Here are some links to articles on database containment and connection authentication models.

https://learn.microsoft.com/en-us/sql/relational-databases/databases/contained-databases?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-user?view=sql-server-ver16

As the diagram shows, not every authentication flow requires a login on the server. Some user definitions are wholly contained in their database and authentication bypasses any server login.

enter image description here