CODEWITHSUNDEEP
c#azureazure-sql-database
Chris Hammond
Chris Hammond

Reputation: 2186

Cannot connect to Azure SQL DB using C# Console App (using System.Data)

OK ... I am tearing my hair out here (and i dont have much to start with)..and its one of those Developer Seal of Approval where "It works on my machine"

Basically, I have a C# Console app that needs to upload data to a SQL Database hosted in Azure. My Dev database is held in a Visual Studio subscription (the free one with MSDN) and the Prod database is in our corporate Azure subscription (which I have to rely on our Azure admin team to configure).

On my PC, under Visual Studio (or CMD line) I can connect to BOTH instances, however, on my PROD server, I CAN connect to the DEV database, but NOT the PROD DB... using exactly the same test app (see below).

Now, i AM behind a company firewall / proxy but the IP addresses have been added to the both database's firewall rules ... and its obviously configured properly because it can connect from by Dev PC.

To try and make testing simple, I pared everything back down to a simple test C# test app and ran it with the appropriate connect strings...

using System;
using System.Data.SqlClient;

namespace AzureConnectTest
{
    class AzureConnectTest
    {
        static void Main(string[] args)
        {
            string ConnectionString = @"data source=******.database.windows.net; ";
            ConnectionString += @"initial catalog=****; ";
            ConnectionString += @"persist security info=False; ";
            ConnectionString += @"MultipleActiveResultSets=True; ";
            ConnectionString += @"user id=""*****""; ";
            ConnectionString += @"password=""*****""; ";

            Console.WriteLine("Attempting to connect");
            Console.WriteLine(ConnectionString);
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    conn.Open();
                    Console.WriteLine(" - Success");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(" - Failed");
                Console.WriteLine(ex.Message);
            }
        }
    }
}

Now ... here's the outcome...

enter image description here

So ... what have I missed here ....

For the record, I have searched SO for similar questions but they either have not been answered or the responses provided still have not solved the issue ... and you can't "bump" questions to say "I'm having this problem"

Upvotes: 1

Views: 3018

Answers (1)

pim
pim

Reputation: 12567

To connect successfully to an Azure SQL Database, your connection string needs the following components:

Server=tcp:***server***.database.windows.net,1433;
Initial Catalog=***database***;
Persist Security Info=False;
User ID=***user id***;
Password=***password**
MultipleActiveResultSets=False;
Encrypt=True;
TrustServerCertificate=False;
Connection Timeout=30;

Please note that in order for a connection to be brokered the IP address of the connecting machine must be granted a firewall exception.

Upvotes: 2

Related Questions