MS SQL Server, ask password before drop specific DB
You should, as the comment says, rely on permissions for this.

Strictly speaking, is not possible to "ask password". Where would the password dialog even be displayed?? SQL Server is a client server product that accept commands from any client that uses the TDS protocol. That may be Management Studio, sqlcmd.exe, a PowerShell script, a ADO.Net SqlClient application. Anything.

If you must, then you can deploy, as a last resort, a DDL trigger. A DDL trigger will not replace permissions, but it can prevent an accidental drop.

CREATE TRIGGER safety 
ON SERVER
FOR DROP_DATABASE 
AS 
   PRINT 'You must disable Trigger "safety" to drop databases!' 
   ROLLBACK;


You can enhance the logic to check for database name, my purpose is not to give you a copy-paste working code.