SSL validation to connect Azure SQL database, to skip adding IP in firewall of SQL server

Question

We had a Desktop based application, and data is on the Azure SQL server.

By SQL authentication it requires first providing user cred in the connection string and then the IP of the user needs to be added in the firewall rule of the SQL server.

But for dynamic IP it require adding IP every time to connect the application.

We require SSL validation/ AD authentication so that the IP addition can be skipped and we can connect the application to the database by SSL certificate validation.

Please provide some solution

Answer 1

Hi
Raunak Sharma,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you want to connect to Azure SQL Database through SSL validation/ AD authentication.

Please check the Authentication method selected for the Azure SQL Server:

If Azure AD needs to be configured, please follow the steps below:

Azure Active Directory authentication requires that database users are created as contained. A contained database user maps to an identity in the Azure AD directory associated with the database and has no login in the master database. The Azure AD identity can either be for an individual user or a group. For more information, see Contained database users, make your database portable and review the Azure AD tutorial on how to authenticate using Azure AD.

Note:

Database users (excluding administrators) cannot be created using the Azure portal. Azure roles do not propagate to SQL servers, databases, or data warehouses. They are only used to manage Azure resources and do not apply to database permissions.

For example, the SQL Server Contributor role does not grant access to connect to a database or data warehouse. This permission must be granted within the database using T-SQL statements.

To add a user with Azure AD authentication:

Connect to your server in Azure using an Azure AD account with at least the ALTER ANY USER permission.

In Object Explorer, right-click the database and select New Query.

1. In the query window, enter the following command and modify <Azure_AD_principal_name> to the principal name of the Azure AD user or the display name of the Azure AD group:

CREATE USER [<Azure_AD_principal_name>] FROM EXTERNAL PROVIDER;

Using Secure connection strings:

To ensure a secure, encrypted connection between the client application and SQL Database, a connection string must be configured to:

• Request an encrypted connection
• Not trust the server certificate

The connection is established using Transport Layer Security (TLS) and reduces the risk of a man-in-the-middle attack. Connection strings are available per database and are pre-configured to support client drivers such as ADO.NET, JDBC, ODBC, and PHP. For information about TLS and connectivity, see TLS considerations.

To copy a secure connection string:

In the Azure portal, select SQL databases from the left-hand menu, and select your database on the SQL databases page.

On the Overview page, select Show database connection strings.

1. Select a driver tab and copy the complete connection string.

Each server in Azure (which hosts SQL Database or Azure Synapse) starts with a single server administrator account that is the administrator of the entire server. Create a second administrator account as an Azure AD account. This principal is created as a contained database user in the master database of the server. Administrator accounts are members of the db_owner role in every user database, and enter each user database as the dbo user. For more information about administrator accounts, see Managing Databases and Logins.

When using Azure Active Directory with geo-replication, the Azure Active Directory administrator must be configured for both the primary and the secondary servers. If a server does not have an Azure Active Directory administrator, then Azure Active Directory logins and users receive a Cannot connect to server error.

Note

Users that are not based on an Azure AD account (including the server administrator account) cannot create Azure AD-based users, because they do not have permission to validate proposed database users with the Azure AD.

Reference Link: Manage Database Access

Use Azure Active Directory authentication

Azure AD admin with a server in SQL Database

Hope this helps. Let us know if you have further questions. Thank you.