ASP.NET Core with Entity Framework Core SqlException: cannot openserver

I am doing some ASP.NET Core self study and experienced this issue.  The thing is that my client IP changes from time to time and need to add it to the the firewall so that database connections are allowed to it.  This happens mostly when I am debugging or coding from my workstation while the database is hosted on Azure, #AzureSQL. The exception message is self-explanatory but wanted to jot it down for future reference.  Figure 1 shows the exception and the actual text you can find at the end of this message.

image

Figure 1, An error occurred while starting the application, asp.net core, entity framework core

You may want to look into the concepts known as Network Security Groups (NSGs).

To resolve the issue, access the Azure Portal and navigate to the Azure SQL database, as seen in Figure 2 and select the Set server firewall link.

image

Figure 2, add client workspace IP address to firewall for Azure SQL access

On the next blade you will see your client IP address and the link to Add client IP to the Firewall settings, Figure 3. Do that.

image

Figure 3, add client workspace IP address to firewall for Azure SQL access

Then Save the settings, Figure 4.

image

Figure 4, add client workspace IP address to firewall for Azure SQL access

Then retry the run the application and all is functional….until the next tome your client IP changes.

An error occurred while starting the application. SqlException: Cannot open server ‘healthandfitness’ requested by the login. Client with IP address ” is not allowed to access the server. To enable access, use the Windows 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. System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, object providerInfo, bool redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, bool applyTransientFaultHandling) SqlException: Cannot open server ‘healthandfitness’ requested by the login. Client with IP address ” is not allowed to access the server. To enable access, use the Windows 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. System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, object providerInfo, bool redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, bool applyTransientFaultHandling) System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, uint waitForMultipleObjectsTimeout, bool allowCreate, bool onlyOneCheckConnection, DbConnectionOptions userOptions, out DbConnectionInternal connection) System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource<DbConnectionInternal> retry, DbConnectionOptions userOptions, out DbConnectionInternal connection) System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource<DbConnectionInternal> retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, out DbConnectionInternal connection) System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource<DbConnectionInternal> retry, DbConnectionOptions userOptions) System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource<DbConnectionInternal> retry) System.Data.SqlClient.SqlConnection.Open() Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open() Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerDatabaseCreator+<>c__DisplayClass11_0.<Exists>b__0(DateTime giveUp) Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute<TState, TResult>(Func<TState, TResult> operation, Func<TState, ExecutionResult<TResult>> verifySucceeded, TState state) Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute<TState, TResult>(IExecutionStrategy strategy, Func<TState, TResult> operation, TState state) Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.EnsureCreated() HealthAndFitness.Data.DbInitializer.Initialize(HealthAndFitnessContext context) in DbInitializer.cs + context.Database.EnsureCreated(); HealthAndFitness.Startup.Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory, HealthAndFitnessContext context) in Startup.cs + DbInitializer.Initialize(context); System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() Microsoft.AspNetCore.Hosting.ConventionBasedStartup.Configure(IApplicationBuilder app) Microsoft.AspNetCore.Hosting.Internal.WebHost.BuildApplication() Show raw exception details System.Data.SqlClient.SqlException: Cannot open server ‘healthandfitness’ requested by the login. Client with IP address ‘85.181.129.201’ is not allowed to access the server. To enable access, use the Windows 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. at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open() at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass11_0.<Exists>b__0(DateTime giveUp) at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](Func`2 operation, Func`2 verifySucceeded, TState state) at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, Func`2 operation, TState state) at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.EnsureCreated() at HealthAndFitness.Data.DbInitializer.Initialize(HealthAndFitnessContext context) in C:\Users\benperk\OneDrive\My Writings\ASP.NET Core 2.0\ASP.NET Core 2.0\Source Code\HealthAndFitness\HealthAndFitness\Data\DbInitializer.cs:line 15 at HealthAndFitness.Startup.Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory, HealthAndFitnessContext context) in C:\Users\benperk\OneDrive\My Writings\ASP.NET Core 2.0\ASP.NET Core 2.0\Source Code\HealthAndFitness\HealthAndFitness\Startup.cs:line 68 — End of stack trace from previous location where exception was thrown — at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.AspNetCore.Hosting.ConventionBasedStartup.Configure(IApplicationBuilder app) at Microsoft.AspNetCore.Hosting.Internal.WebHost.BuildApplication() ClientConnectionId:997b4bb4-f6ea-42e0-a033-692a5aeccf2c Error Number:40615,State:1,Class:14