CODEWITHSUNDEEP
c#.netsql-serverentity-frameworkazure
Burning Hippo
Burning Hippo

Reputation: 805

How to connect .Net Core app to Azure SQL database

I have created an empty Azure SQL database.

I have added my IP to the firewall setting on the server.

I want EF Core to create the database for me with the command dotnet ef database update.

I can successfully connect to and query the Azure database via the mssql ext for Visual Studio Code.

I am have tried to connect the app to the database with connection strings as well as placing the connection string directly into the Startup.cs file.

appsettings.json

{
"Logging": {
    "IncludeScopes": false,
    "LogLevel": {
    "Default": "Warning"
}
},
"ConnectionStrings": {
    "Development": "Server=tcp:appName.database.windows.net,1433;Initial Catalog=AppName;Persist Security Info=False;User ID=User;Password=Password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
}
}

Startup.cs

public void ConfigureServices(IServiceCollection services)
    {
        // Add framework services.
        services.AddMvc();

        // Add database services.
        services.AddDbContext<ApplicationContext>(options => 
            options.UseSqlServer(Configuration.GetConnectionString("Development")));
    }

I run dotnet restore then dotnet ef database update

Finally, I get the following error:

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: 35 - An internal exception was caught)

Am I missing something? What else can I try?

EDIT : I have even tried from a different location (different IP address). I added it to the firewall settings in Azure and got the same results. I can connect via the mssql extension in VSCode but not when I run the dotnet ef database update command.

Upvotes: 4

Views: 7843

Answers (2)

Burning Hippo
Burning Hippo

Reputation: 805

Running dotnet ef database update --verbose revealed that the app was trying to connect to the default blogging.db database. I found that it was still listed in the OnConfiguring overridden function inside ApplicationContext.cs. 

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Data Source=blogging.db");
    }

After setting the proper database here everything started working.

Upvotes: 0

RAS
RAS

Reputation: 3395

You can try a couple of things:

  1. Make sure firewall on your machine allows outbound connections to 1433

  2. Are you sure you used correct IP address in Azure Firewall rules? As a test, you can create a rule with ip range from 1.1.1.1 to 255.255.255.255 and see if it works. If it does, it means that you provided incorrect IP in Azure firewall

Upvotes: 1

Related Questions