CODEWITHSUNDEEP
sql-serveruwpentity-framework-coredatabase-connectionef-core-3.0
Massimo Savazzi
Massimo Savazzi

Reputation: 159

Error on UWP trying to connect to SQL Server Express via EFCore

Team, this is driving me crazy. I always receive this error when I start the UWP application:

Microsoft.Data.SqlClient.SqlException: '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: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server)'

Inner Exception:

Microsoft.Data.SqlClient.SqlException HResult=0x80131904 Message=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: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server) Source=Core Microsoft SqlClient Data Provider StackTrace: at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool, SqlAuthenticationProviderManager sqlAuthProviderManager) at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at Microsoft.Data.SqlClient.SqlConnection.Open() at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected) at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.InitializeReader(DbContext _, Boolean result) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.MoveNext() at System.Collections.Generic.List1.AddEnumerable(IEnumerable1 enumerable) at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at UWP_EF_i9.MainPage.Page_Loaded(Object sender, RoutedEventArgs e) in E:\Users\Massimo\OneDrive\Development\Source\UWP EF i9\UWP EF i9\MainPage.xaml.cs:line 35

Inner Exception 1: Win32Exception: Access is denied

As EFCore in UWP is not able to dbscaffold the database, following the suggestions found here I did an EF.Core WPF application, scaffolded the database creating the classes... Imported the generated classes in the UWP application (renamed the namespace where applicable).

In the UWP application I've added the Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.SQLServer and added the Enterprise Capability: Enterprise Authentication

So everything should be fine... but.. it does not work. Exactly the same LINQ query that works fine in the WPF gives me the error in the UWP

Any idea? what am I missing?

--- Edit 1

I've even added the code from this page:

Use a SQL Server database in a UWP app connectionString = "Data Source=YourServerName\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=SSPI";

so to my case:

            try
        {
            using (SqlConnection conn = new SqlConnection((App.Current as App).ConnectionString))
            {
                conn.Open();
                if (conn.State == System.Data.ConnectionState.Open)
                {
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "Select * from tAPP";
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                            }
                        }
                    }
                }
            }
        }

but the conn.Open() fails with the same message

Upvotes: 1

Views: 452

Answers (2)

Massimo Savazzi
Massimo Savazzi

Reputation: 159

I found the issue :( it's explained here: Use a SQL Server database in a UWP app

in the section: Trouble connecting to your database?

The TCP/IP protocol is disabled by default and must be enabled. Now how is it possible that the same app in WPF works, that VS2019 is able to connect (not to mention the SSMS...) and only the UWP does not connect is a mystery for me... but now it works!

Upvotes: 2

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88852

UWP apps don't have network access by default. See:

Capabilities

In order to use networking, you must add appropriate capability elements to your app manifest. If no network capability is specified in your app's manifest, your app will have no networking capability, and any attempt to connect to the network will fail.

Networking basics - UWP applications

Upvotes: 0

Related Questions