How to give access to Azure-sql server over p2s vpn for developers

I have successfully created a P2S Vpn connection and added the virtual network under firewall and virtual network of my SQL Server in Azure. I configured windows VPN client in my local machine and was able to establish a connection to Azure, but when I open SQL management studio and connect my database it still asks for login to azure and whitelist my public IP. I don't know where I have done mistakes.

Solution

I suggest you do the following:

Navigate to "Firewalls and virtual networks" of you SQL server and make sure to set "Deny public network access" to yes.
Create an Azure private endpoint. It will create endpoint for SQL server within your virtual network and it'll be assigned a private IP from within subnet's IP range. You use this private IP to connect to SQL server.
On you local machine, make sure you're connected to VPN and open SQL Server Management Studio:

Under "Server name" enter private IP address of Azure private endpoint created in step #2.
Login part can be a bit tricky. Under "Login" field, enter username in format "username@public_sql_server_name" (e.g. [email protected]). For password, just enter you password.
Last thing to do is to click on "Options" and navigate to "Connection properties". Make sure to check "Encrypt connection" and "Trust server certificate". This is required as server's certificate is issued to "my-sql-server.database.windows.net" and you're accessing it via private IP. If this wasn't checked, management studio wouldn't trust server's certificate and would refuse connection.