2

How do you unlock a login on Azure Sql?

I have a login which has become locked due to too many bad login attempts. I tried unlocking the user with the standard command.

ALTER LOGIN [lockedAccount] WITH PASSWORD = 'xxxx' unlock

This returns the error - Keyword or statement option 'unlock' is not supported in this version of SQL Server.

SSMS does not give me an option to unlock it either.

Is there any way to unlock the account?

Kritner
  • 13,557
  • 10
  • 46
  • 72
Paul
  • 653
  • 6
  • 15
  • 1
    Have you attempted resetting the master administrator password? I posted an answer how to do that, [here](http://stackoverflow.com/questions/13790752/password-reset-for-azure-database/13793925#13793925). Not sure if it'll help, but is worth a try. – David Makogon Nov 12 '15 at 03:57
  • The master account is not locked. I have a secondary account which my application uses to log in with which is what is locked. – Paul Nov 12 '15 at 06:04
  • I think it's ALTER LOGIN [lockedAccount] ENABLE – Ola Ekdahl Nov 12 '15 at 16:27
  • 1
    That does not work either. I did discover that the account does auto unlock after around 10 minutes. I don't have the exact timing down yet. It gets locked after 9 failed attempts. – Paul Nov 12 '15 at 17:53
  • It all depends on how you setup security. You can setup a contained database in which the user/password is in the database. Or you can have a login/password in [master] that redirects to the user in the database. – CRAFTY DBA Nov 16 '15 at 22:53

2 Answers2

2

This is by design - you aren't hitting password policy lock out, but you are hitting Azure SQL Databases denial of service functionality to help prevent brute force password attacks and unauthenticated denial of service attacks. See #6 on https://azure.microsoft.com/en-us/blog/sql-azure-connectivity-troubleshooting-guide/. The only solution is to wait as you discovered and try again.

Jack Richins
  • 548
  • 2
  • 5
1

There is a dynamic property under Server Parameters in Azure Database for PostgreSQL server that is called connection_throttling (it enables temporary connection throttling per IP for too many invalid password login failures).

It could be turned off in order to establish connection faster and turned back on afterwards.

Aleksandr Erokhin
  • 1,904
  • 3
  • 17
  • 32
  • 1
    Great answer! This worked for us. We turned it off, saved, tested, it worked, and then went back in right away and turned it back on, and it still worked. – Chris Jan 29 '21 at 03:04