AZURE SQL Database User

Question

AZURE SQL Database User

Sandeep Tadishetty 1

Hi,

I created SQL account for an application but how do I restrict or deny the same account not to connect the database using SSMS or Azure data studio by the developers since the developers can view the user information in web.config file.

Thanks,
Sandeep

1 answer

Your answer

Answer 1

Vaibhav Chaudhari 38,916 Volunteer Moderator

Instead of hardcoding SQL user name and password, store them in Azure key vault and use key vault reference in config files

Users should not have access to see these credentials (secret values) in the key vault

https://learn.microsoft.com/en-us/azure/azure-app-configuration/use-key-vault-references-dotnet-core?tabs=cmd%2Ccore2x
https://learn.microsoft.com/en-us/azure/key-vault/general/tutorial-net-create-vault-azure-web-app

Edit #1:

You can try using Managed Identity of app to access Azure SQL DB. This way, you won't use SQL user name and password.
This thread might help - https://stackoverflow.com/questions/56943539/limit-sql-azure-access-from-an-azure-web-app

----------

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Navtej Singh Saini 4,226 Reputation points Microsoft Employee Moderator

2020-10-29T23:01:01.23+00:00

@Sandeep Tadishetty

Please let us know if you need any further help or have a question regarding this.

Thanks
Navtej S
Sandeep Tadishetty 1 Reputation point

2020-10-30T05:37:03.693+00:00
Hi Vaibhav,

Thanks for your reply and I will look in to the option you suggested but is there a way that I can restrict through SSMS. I found the below code from online where it blocks or does not allow user to connect to database server but the same code is not working in AZURE and I am looking for solution from SQL side.

Working code on premises:

CREATE TRIGGER strRejectSSMSConnectionForSQLLogin1
ON ALL SERVER FOR LOGON
AS
BEGIN

IF ORIGINAL_LOGIN() = N'TESTUSER' AND PROGRAM_NAME() LIKE N'Microsoft SQL Server Management Studio%' BEGIN RAISERROR('Direct connection by SSMS refused.', 16, 1) ROLLBACK END

END

Thanks,
Sandeep
Sandeep Tadishetty 1 Reputation point

2020-10-30T05:39:59.827+00:00

Yes. I am trying to restrict SQL user not to make connection using SSMS. I found trigger in online that works in on premises SQL version but the same trigger doesn't work in azure SQL database.

Please let me know if there is any other different way to restrict SQL account so that it cannot connect to the server using SSMS or ADS or any third party tool.

Thanks,
Sandeep
Vaibhav Chaudhari 38,916 Reputation points Volunteer Moderator

2020-10-30T05:52:40.88+00:00

See Edit #1 in my above response

Share via

AZURE SQL Database User

1 answer

Your answer