6

Case

I want to work with Azure SQL Server, as noted in the best practices how to secure Azure SQL Server it is good to use AAD account for accessing your server/database instead of SQL Accounts.

I read several posts on the documentation site of Microsoft and blogs, but I cannot find that it is possible to disable/remove the Server Admin account, after you have the Azure SQL Server and an created AAD SQL Administrator and attached it to Azure SQL Server.

I know Server Admin Login and Password is mandatory, when you create the Azure SQL, but I hoped it was possible to delete after creation of server and AAD SQL Admin.

Why do I want this?

  • There are enough companies who have to rule (inherited from the on-prem) that accounts must be controlled by a centralized Identity Store, such as AAD.
  • Password rotation must be done, it is a lot easier when you have a centralized identity Store instead that you have to do it for a lot of Azure SQL Servers.

Question

Is it possible to disable the SQL Server Admin (sql account)?

Sven
  • 195
  • 2
  • 11

2 Answers2

5

I don't believe it is (or can be) possible to disable the SQL Server Admin.

The Azure Subscription Owner and the AAD SQL Admin identity have joint ownership of the Azure SQL Server. Either one needs to be able to re-acquire administrative access to the server.

And the mechanism that the Azure Subscription Owner uses to force their way into their own database is to reset the SQL Server Admin password from the Azure Portal.

This is roughly analogous to how a Windows Administrator can force sysadmin access to a SQL Server by starting the service in single-user mode.

I think the best you can do is to throw away the SQL Server Admin's password, so no one could log in using that account without first resetting the password in the portal. EG run:

declare @sql nvarchar(max) = concat(N'alter LOGIN [youradmin] WITH PASSWORD=N''',newid(), N'''')
exec (@sql)
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • 2
    Presumably this means an Azure SQL database cannot comply with a security policy that requires all access to be via an MFA secured account ? – PhillipH Mar 12 '19 at 11:32
  • You would need to make the case that running the above script has disabled the access of the non-MFA account. – David Browne - Microsoft Mar 12 '19 at 12:32
  • 4
    but its still open to dictionary attack, unless I'm mistaken, just because its a really long password, its still single-factor ? – PhillipH Mar 13 '19 at 16:05
2

Update 06/21: Microsoft released Azure Active Directory only authentication for Azure SQL which effectively disables the possibility to use any SQL user for authentication.

Note the caveats

  • Azure AD-only auth is supported at the Azure SQL server level
    • This means that when this mode is enabled, all databases that belong to this server can only be accessed using Azure AD authentication
  • Enabling Azure AD-only auth does not remove existing SQL logins or SQL users based on these logins. They continue being stored in SQL metadata, but cannot be used for SQL authentication
  • Even though the Azure AD-only auth is enabled, with proper SQL permissions for Azure AD users, SQL logins and SQL users can be created. However, the authentication process to connect to Azure SQL using SQL logins/users will fail
  • Azure AD users with proper permissions can impersonate existing SQL users
    • Impersonation continues working between SQL authentication users even though the Azure AD-only auth feature is enabled. This is consistent to the way impersonation works today, where even disabled users can be impersonated.

Microsoft is working on allowing so called "AAD-only authentication" which will effectively disable the server admin login, as the complete SQL authentiction is disabled.

Marvin Dickhaus
  • 785
  • 12
  • 27