Security for small business

Question

I have setup a sql server database in Azure connecting via a VB.net application and am confused on the best way for a small group of users to connect. They also travel so they need a secure and convenient way to connect. I have been told to use Entra ID however this seems complex…

Regards,

Ken

Answer 1

Hi Kenneth Robinson,

Welcome to Microsoft Q&A forum.

As I understand, you want to setup Azure SQL Server to connect by group of users securely.

In Azure SQL Database, the server is a logical concept and permissions can't be granted at the server level. To simplify permission management, Azure SQL Database provides a set of fixed server-level roles to help you manage the permissions on a logical server. Roles are security principals that group logins.

These special fixed server-level roles use the prefix ##MS_ and the suffix ## to distinguish from other regular user-created principals.

Like SQL Server on-premises, server permissions are organized hierarchically. The permissions that are held by these server-level roles can propagate to database permissions. For the permissions to be effectively useful at the database level, a login needs to either be a member of the server-level role ##MS_DatabaseConnector##, which grants CONNECT to all databases, or have a user account in individual databases. This also applies to the virtual master database.

For example, the server-level role ##MS_ServerStateReader## holds the permission VIEW SERVER STATE. If a login who is member of this role has a user account in the databases master and WideWorldImporters, this user has the permission VIEW DATABASE STATE in those two databases.

For more information on Azure SQL Database logins and users, see Authorize database access to SQL Database, SQL Managed Instance, and Azure Synapse Analytics.

Refer: https://learn.microsoft.com/en-us/azure/azure-sql/database/security-server-roles?view=azuresql&source=recommendations

This should help, let us know if you have a different ask.

Thanks

Answer 2

You can use an app service to place your code, and the app service connects to the DB via RBAC, your users would connect to the application using their login ID using an App Registration.

if this answer helped you, please consider accepting it.

Answer 3

Hi Kenneth Robinson,

Thanks for the reply.

Could you please raise support case for deeper analysis of your scenario by dedicated engineer so that they can help out.

Let us know if you face any issue in doing the same.

Thanks