C# code is unable to connect to Azure SQL database

PSzk
January 25, 2023
189 views
0 votes
2 Answers

I am trying to connect to a sample database I have created in Azure using C# (.NET Core 3.1)
I have enabled my IP address within Azure’s Firewall rules.
I am able to use VS2019’s SQL Server Object Explorer to connect and view the database within with no problems.

However, when I run a simple C# app on the same PC to execute a query to count the number of records in a table, it throws the following exception at the point where the connection is opened (conn.Open());

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 – The requested address is not valid in its context.)

The C# code;

using System;
using System.Data.SqlClient;

namespace AzureSql2
{
  class Program
  {
    static void Main(string[] args)
    {
      string connStr = " Server=tcp:beaconsqlsql.database.windows.net,1433;Initial Catalog=MRP2;Persist Security Info=False;User ID=beaconadmin;Password=********;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
      Console.WriteLine("Building connection");
      try
      {
        using (var conn = new SqlConnection(connStr))
        {
          Console.WriteLine("Creating command");
          using (var command = conn.CreateCommand())
          {
            command.CommandText = "SELECT COUNT(*) FROM [dbo].[Table]";

            Console.WriteLine("Opening connection");
            conn.Open();

            Console.WriteLine("Reading database");
            using (var reader = command.ExecuteReader())
            {
              while (reader.Read())
              {
                Console.WriteLine("Record count: {0}", reader.GetInt32(0));
              }
            }
          }
        }
      }
      catch (Exception ex)
      {
        Console.WriteLine("Exception: " + ex.Message);
      }

      Console.WriteLine("Press Enter to exit");
      Console.ReadLine();
    }
  }
}

I’ve tried temporarily turning off the firewall on my PC, but that made no difference.
The fact that SQL Server Object Explorer can connect but the C# code cannot makes it sound like there’s a problem with the C# code, but I can’t see any differences between it and the samples I’ve looked at.

Tags: azure c#database

Answers

Chosen as BEST ANSWER
- PSzk
- February 9, 2023 at 6:43 am
- 0 votes
0
I ended up taking a copy of the project home and running it on my home PC, and it worked correctly and reliably (after telling Azure to allow that IP address as well)

It turned out the answer was embarrassingly obvious - in addition to the standard Windows 10 firewall, my work PC is running another virus protection/firewall software, and that also needed to be told to allow the app thru.

Definitely one to remember for next time... Although I am kind of intrigued that on two occasions (once mentioned above, once afterwards) out of a few hundred attempts the app did manage to get thru and connect.

Thank you everyone for your answers and help.

(Edit)

I created one Azure SQL database and allowed my client IP like below :-

I created one .Net Console application and ran your code, I replaced

using System.Data.SqlClient

with

using Microsoft.Data.SqlClient

You can use any of the above packages.

Copied connection string from Azure Portal > Azure SQL server > Connection string refer below :-

C# Code:-

using System;
using System.Linq.Expressions;
using Microsoft.Data.SqlClient;

namespace AzureSql2
{
    class Program
    {
        static void Main(string[] args)
        {
            string connStr = "Server=tcp:sqlservername.database.windows.net,1433;Initial Catalog=sqldbname;Persist Security Info=False;User ID=username;Password=password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
            Console.WriteLine("Building connection");
            try
            {
                using (var conn = new SqlConnection(connStr))
                {
                    Console.WriteLine("Creating command");
                    using (var command = conn.CreateCommand())
                    {
                        command.CommandText = "SELECT * FROM Products";

                        Console.WriteLine("Opening connection");
                        conn.Open();

                        Console.WriteLine("Reading database");
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine("Record count: {0}", reader.GetInt32(0));
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
            }

            Console.WriteLine("Press Enter to exit");
            Console.ReadLine();
        }
    }
}

Output :-

I tried to run the code with the connection string format you mentioned in the comments :-

Data Source=azuresqlservername.database.windows.net;Initial Catalog=databasename;User ID=siliconuser;Password=password;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False

And I was able to run the same code above and got the desired output:-