Standard: SqlConnection in .net standard 2.0 can't access SQL Server

[xiedongweo]

I'm using "System.Data.SqlClient" namespace relevant APIs in .net standard 2.0 to access data from my remote sql server. I'm using these APIs in UWP app.

This is my sql connection string:
String connsql = @"Data Source=10.1xx.xxx.xxx;Initial Catalog=UWPTest;User Id=sa;Password=Password";

I used this connection string to connect to my sql server, but I got the following exception:

`System.Data.SqlClient.SqlException (0x80131904): 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) ---> System.AggregateException: One or more errors occurred. (An attempt was made to access a socket in a way forbidden by its access permissions 10.157.12.153:1433) ---> System.Net.Internals.SocketExceptionFactory+ExtendedSocketException: An attempt was made to access a socket in a way forbidden by its access permissions 10.157.12.153:1433
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Net.Sockets.Socket.EndConnect(IAsyncResult asyncResult)
at System.Net.Sockets.Socket.<>c.b__267_0(IAsyncResult iar)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Data.SqlClient.SNI.SNITCPHandle.d__22.MoveNext()
--- End of inner exception stack trace ---
at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
at System.Threading.Tasks.Task.Wait(TimeSpan timeout)
at System.Data.SqlClient.SNI.SNITCPHandle..ctor(String serverName, Int32 port, Int64 timerExpire, Object callbackObject, Boolean parallel)
---> (Inner Exception #0) System.Net.Internals.SocketExceptionFactory+ExtendedSocketException (0x80004005): An attempt was made to access a socket in a way forbidden by its access permissions 10.157.12.153:1433
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Net.Sockets.Socket.EndConnect(IAsyncResult asyncResult)
at System.Net.Sockets.Socket.<>c.b__267_0(IAsyncResult iar)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Data.SqlClient.SNI.SNITCPHandle.d__22.MoveNext()<---

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, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at SQLServerSample.MainPage.OnNavigatedTo(NavigationEventArgs e) ClientConnectionId:00000000-0000-0000-0000-000000000000

But, the same code worked well in windows console application:

` String connsql = @"Data Source=10.1xx.xx.xxx;Initial Catalog=UWPTest;User Id=sa;Password=Password";

        try
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = connsql;
                conn.Open(); 
                String sql = "select * from dbo.Table_1";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Debug.WriteLine(dr["ID"] + ": " + dr["Name"]);
                }
                conn.Close();
            }
        }
        catch (Exception ex)
        {
            Debug.WriteLine(ex);
        }`

So, I do not know where the issue is.
Are there any different things between UWP and classic win32 application in .net standard 2.0?
How to solve this issue?

[donchak]

Hi,
UWP applications have restrictions with access to network. Try to add Internet (Client & Server), Internet (Client) and Private Networks (Client & Server) capabilities in the project settings.

[xiedongweo]

@donchak It worked like a charm. Thank you so much.

[joperezr]

Closing since this looks to be resolved now. Thanks @donchak !

[Prasanna-Sridharan]

Hi,
UWP applications have restrictions with access to network. Try to add Internet (Client & Server), Internet (Client) and Private Networks (Client & Server) capabilities in the project settings.

Thank you so much @donchak :+1: . After so much struggle by changing and checking on SQL server details, found that a simple .net core/framework console app worked well in remote but not the UWP. Finally narrowed down the search to UWP-SQLEXPRESS and got this beauty of simple solution.
That said, Microsoft highly sucks in pointing these details in relevant pages like : https://docs.microsoft.com/en-us/ef/core/get-started/uwp/getting-started
Thanks to members of gitHub and SO