Is it possible to connect a local database to an Asp.NET application deployed on Azure?

Question

I was wondering if it is possible to connect a local database to a web application published on Azure.

Currently, I have modified my connection string by changing the instance name to the public IP provided by myip.opendns.com and adding port 1433 to the specification.

<add name="DB"
connectionString="metadata=res://*/Models.CNL.csdl|res://*/Models.CNL.ssdl|res://*/Models.CNL.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source={myip},1433;initial catalog=cnl;persist security info=True;user id=sa;password=1234;multipleactiveresultsets=True;application name=EntityFramework;Connect Timeout=60;&quot;"
providerName="System.Data.EntityClient" />

It throws the error "The wait operation timed out," and if I remove the port specification, it says "Access is denied." I have the inbound rule for port 1433, as well as the option to allow remote connections in SQL. If I leave the same name as my local instance, "server\SQLEXPRESS," it throws an error stating that the instance cannot be found; and if I use the local IP address of my computer directly and handle it the same way as in the cases of using the public one, it throws the same errors.

Answer 1

The EntityClient provider is used for accessing data based on an Entity Data Model (EDM). Unlike the other .NET Framework data providers, it does not interact directly with a data source. Instead, it uses Entity SQL to communicate with the underlying data provider. The EntityClient provider exposes generic interfaces for client code and acts as a bridge to other .NET Framework data providers, such as System.Data.SqlClient. If you have to use EntityClient directly, it is easy to code against because it follows the familiar programming patterns found in the other .NET Framework data providers

I used this reference DOC, to connect to an Azure SQL server using System.Data.EntityClient.

• The sample code below uses the Entity Framework model to manage artists, albums, and todos. It includes views for displaying lists of todos, artists, and albums.

• Code taken from John Atten's article demonstrates ASP. NET MVC .

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <!-- Other configuration settings -->

  <connectionStrings>
    <!-- Connection string for ASP.NET Membership -->
    <add name="DefaultConnection" 
         connectionString="Data Source=tcp:yourAzureServerName.database.windows.net,1433;
                           Initial Catalog=ModelFirstExampleDb;
                           User ID=yourDbUserName;
                           Password=YourPassword;"
         providerName="System.Data.SqlClient" />

    <!-- Connection string for Entity Framework Model-First -->
    <add name="ExampleDbEntities" 
         connectionString="metadata=res://*/Models.ExampleEntities.csdl|res://*/Models.ExampleEntities.ssdl|res://*/Models.ExampleEntities.msl;
                           provider=System.Data.SqlClient;
                           provider connection string=&quot;data source=tcp:MyAzureServer.database.windows.net;
                           initial catalog=ModelFirstExampleDb;
                           Persist Security Info=True;
                           User ID=MyUserLogin;
                           Password=MySuperSecretPassword&quot;"
         providerName="System.Data.EntityClient" />
  </connectionStrings>

  <!-- Other configurations, appSettings, etc. -->

</configuration>

• Replace data source=YourServerName and initial catalog=YourDatabaseName.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <!-- Other configuration settings -->

  <connectionStrings>
    <!-- Connection string for local SQL Server (change accordingly) -->
    <add name="DefaultConnection" 
         connectionString="Data Source=YourLocalServerName;Initial Catalog=YourDatabaseName;Integrated Security=True;"
         providerName="System.Data.SqlClient" />

    <!-- Connection string for Entity Framework Model-First -->
    <add name="ExampleDbEntities" 
         connectionString="metadata=res://*/Models.ExampleEntities.csdl|res://*/Models.ExampleEntities.ssdl|res://*/Models.ExampleEntities.msl;
                           provider=System.Data.SqlClient;
                           provider connection string=&quot;data source=YourLocalServerName;
                           initial catalog=YourDatabaseName;
                           Integrated Security=True;&quot;"
         providerName="System.Data.EntityClient" />
  </connectionStrings>

  <!-- Other configurations, appSettings, etc. -->

</configuration>

namespace YourNamespace.Models
{
    using System.Collections.Generic;

    public partial class Artist
    {
        public Artist()
        {
            this.Albums = new HashSet<Album>();
        }

        public int Id { get; set; }
        public string Artist1 { get; set; }

        public virtual ICollection<Album> Albums { get; set; }
    }
}

// ... (existing code)

public partial class Todo
{
    public int ID { get; set; }
    public string Description { get; set; }
    public DateTime CreatedDate { get; set; }
}

// ... (existing code)

namespace YourNamespace.Models
{
    public partial class Album
    {
        public int AlbumId { get; set; }
        public string AlbumName { get; set; }
        public int ArtistId { get; set; }

        public virtual Artist Artist { get; set; }
    }
}

• Connect to Azure SQL Server before publishing to Azure.

Azure: