How to lock a Azure SQL login after N unsuccessful login attempts?

Question

How to lock a Azure SQL login after N unsuccessful login attempts?

Leon Leung 1

Jul 7, 2021, 11:24 PM

Hi, Is there a way to lock a Azure SQL login after N unsuccessful login attempts for security purpose.

Thank you.

Anurag Sharma 17,621 Reputation points

Jul 12, 2021, 7:14 AM

Hi @Leon Leung , just wanted to check if you need more details on this. You can mark the answer provided as 'Accepted Answer' if it helped your query.

1 answer

Your answer

Anurag Sharma 17,621 Reputation points

Jul 12, 2021, 7:14 AM

Hi @Leon Leung , just wanted to check if you need more details on this. You can mark the answer provided as 'Accepted Answer' if it helped your query.

Answer 1

Alberto Morillo 34,566 MVP

Jul 8, 2021, 1:47 AM

Azure SQL logins associated with Azure AD can be configured with smart lockout as explained here.

A possible solution for SQL logins (non-Azure AD logins) could be to configure Azure SQL auditing as explained here. You can read the Audit entries from Azure Storage Account as explained here and then issue ALTER LOGIN DISABLE for those logins exceeding X failed login attempts. You can automate the process using Azure Automation.

Leon Leung 1 Reputation point

Jul 8, 2021, 2:07 PM

Hi Alberto, Thank you for the workaround solution, but this does not really lock out the Azure SQL logins after a certain unsuccessful login. This is a security risk. Oracle database server has a settings that we can set to lockout account after N unsuccessful logins and then Database Administrators must unlock the login in order for the login to be able to log into the Oracle database again. Oracle database server also has a settings that we can set to ignore if we want to.
Alberto Morillo 34,566 Reputation points MVP

Jul 8, 2021, 7:08 PM

We have that with SQL Server on-premises and IaaS as you can see here, but Azure SQL Database is different on that topic. If you want that level of security with Azure SQL (PaaS), then use SQL logins that are attached to Azure Active Directory that has all the security controls you are looking for and more (like MFA).

AI Skills Fest

Share via

How to lock a Azure SQL login after N unsuccessful login attempts?

1 answer

Your answer