Question 407210 in Azure Question posted in
The official documentation to get you started can be found here.

What SQL Server connection string works with Entra in Azure?

I have installed a .Net Framework 4.5.2 Web app into Azure, which is set up to use Entra authentication. The app needs to connect to a SQL Server database in the Azure account. The Azure UI shows a variety of connection strings to use with the database. But none of them work. I put the connection strings (modified to include server, db, uid, and pwd as needed) in a Connection String environmental variable. It does not allow specifying a provider, by the way. How do I know what providers are present in Azure? Or do I need to add a new one to my application? It currently references System.Data.

Connection strings suggested by Azure

ADO.NET (Microsoft Entra passwordless authentication)

Server=tcp:MyDBServer.database.windows.net,1433;Initial Catalog=MyDB;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication="Active Directory Default";

Error: Invalid value for key ‘authentication’.

Plain SQL authentication

Server=tcp:MyDBServer.database.windows.net,1433;Initial Catalog=MyDB;Persist Security Info=False;User ID=MyUID;Password=MyPwd;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Error: Cannot open server ‘xxx’ requested by the login. Client with IP address ‘xx.x.xxx.x’ is not allowed to access the server. To enable access, use the Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.

ADO.NET (Microsoft Entra password authentication)

Server=tcp:MyDBServer.database.windows.net,1433;Initial Catalog=MyDB;Persist Security Info=False;User ID=MyUID;Password=MyPwd;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory Password";

Error: One or more errors occurred.

ADO.NET (Microsoft Entra integrated authentication)

Server=tcp:MyDBServer.database.windows.net,1433;Initial Catalog=MyDB;Persist Security Info=False;User ID=MyUID;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory Integrated";

Error: Cannot use ‘Authentication=Active Directory Integrated’ with ‘User ID’, ‘UID’, ‘Password’ or ‘PWD’ connection string keywords.

ADO.NET (Microsoft Entra integrated authentication)

Server=tcp:MyDBServer.database.windows.net,1433;Initial Catalog=MyDB;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory Integrated";

Error: One or more errors occurred.

Tags:

2

Answers


  1. 0

    The default provider in Azure for connection strings should be System when you are working with them in a.NET Framework 4.5.2 application. SQL Server databases can be used using Data.SqlClient.

    You indicated that you are utilizing Azure AD (previously known as Entra authentication), so it is imperative that you verify that the authentication techniques and connection string format are valid.

    Measures to Recognise and Fix:

    Examine the format of the connection string. Make sure Azure SQL Server has the connection string formatted appropriately. A small format difference exists between SQL authentication and Entra (Azure AD) authentication.

    For Authentication

    Data Source=<your_server>.database.windows.net;Initial Catalog=<your_database>;User ID=<your_username>;Password=<your_password>;

    Example of Entra (Azure AD) Authentication: If your database connections are made with Entra authentication, then:

    Data Source=<your_server>.database.windows.net;Initial Catalog=<your_database>;Authentication=Active Directory Integrated;

    Alternatively, if you’re authenticating with a particular client ID (Service Principal):

    Data Source=<your_server>.database.windows.net;Initial Catalog=<your_database>;Authentication=Active Directory Service Principal;User Id=<client_id>;Password=<client_secret>;
    Login or Signup to reply.
  2. 0

    Error: Cannot open server ‘xxx’ requested by the login. Client with IP address ‘xx.x.xxx.x’ is not allowed to access the server. To enable access, use the Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.

    This is an error you get when you did not allow the client to access the server using the firewall, see the docs on how to do that.

    Now, this

    ADO.NET (Microsoft Entra integrated authentication)

    Server=tcp:MyDBServer.database.windows.net,1433;Initial Catalog=MyDB;Persist Security Info=False;User ID=MyUID;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory Integrated";

    Error: Cannot use ‘Authentication=Active Directory Integrated’ with ‘User ID’, ‘UID’, ‘Password’ or ‘PWD’ connection string keywords.

    indicates that you should not supply an user id when using "Active Directory Integrated" authentication

    If you want "Active Directory Integrated" authentication against your Azure Database you will need to switch to the Microsoft.Data.SqlClient namespace, see this blogpost. There is a guide on how to do that.

    That also means you need to update to at least .Net Framework 4.6.2 as seen here

    So, if you don’t want to, or can’t do use a connection string with username and password and make sure network access is allowed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search