Runtime: Cannot connect to SQL Server on OSX

[giuseppemag]

Hi, I am trying to port a web api that acts as a front-end for an SQL Server database. The web api works fine under Windows and when deployed to IIS, but gives this error when ran from within OSX and Visual Studio Core.

Unhandled Exception: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a conn
ection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is co
nfigured to allow remote connections. (provider: TCP Provider, error: 35 - An internal exception was caught) ---> System.AggregateExcept
ion: One or more errors occurred. (Device not configured) ---> System.Net.Internals.SocketExceptionFactory+ExtendedSocketException: Devi
ce not configured                                                                                                                       
   at System.Net.Dns.HostResolutionEndHelper(IAsyncResult asyncResult)                                                                  
   at System.Net.Dns.EndGetHostAddresses(IAsyncResult asyncResult)                                                                      
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, 
Boolean requiresSynchronization)                                                                                                        
--- End of stack trace from previous location where exception was thrown ---                                                            
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)                                                         
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)                                    
   at System.Data.SqlClient.SNI.SNITCPHandle.<ConnectAsync>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 para
llel)                                                                                                                                   
   --- End of inner exception stack trace ---                                                                                           
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Obj
ect providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean a
pplyTransientFaultHandling)                                                                                                             
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolG
roupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)                                
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnection
Options options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)                                                          
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInt
ernal oldConnection)                                                                                                                    
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnecti
onInternal oldConnection)                                                                                                               
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolea
n allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)                       
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOp
tions userOptions, DbConnectionInternal& connection)                                                                                    
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConne
ctionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)                                         
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connecti
onFactory, 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.Query.Internal.QueryingEnumerable.Enumerator.MoveNext()                                             
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext()                                           
   at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)                                                                       
   at lambda_method(Closure , QueryContext )               
  at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass19_1`1.<CompileQuery>b__1(QueryContext qc)            
   at System.Linq.Queryable.First[TSource](IQueryable`1 source)                                                                         
   at sql_server_ef_connector.Program.Main(String[] args) 

The web.config connection string was

connectionString="data source=A\B;initial catalog=C;persist security info=True;user id=X;password=Y;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient"

which is then given to method UseSQLServer in the form:

Server=A\B;Database=C;user id=X;password=Y;

The project compiles fine, but crashes at the first query.

Have I done anything terribly wrong or did I run into something that is not working properly?

[jlebensold]

@giuseppemag I'm experiencing the same issue on OS X El Capitan. Did you end up resolving it?

[giuseppemag]

I ended up solving it. It was mostly an issue with permissions in the network, so nothing to do with .Net core itself.

We ended up managing to make the connection, but we still had to take an odd step. We had to specify the port and not the database instance, which is quite different behaviour from .Net core inside Windows within the same network and the same permissions.

Still, better error messages might be possible, but we are quite happy now with both performance and reliability.