Can't connect to SQL 2008 database using .NET Core 2.0

Question

UPDATE

I could never make this work with a "Windows Authentication" (domain) user. But with a "SQL Server Authentication" user everything is working like it's supposed to.

ORIGINAL QUESTION

My connectionString: Server=ip;Database=dbname;User Id=xxx\user;Password=pass;

The connection string is located in appsettings.json like this:

{
  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "ConnectionStrings": {
    "ConnectionString": "Server=ip;Database=dbname;User Id=xxx\user;Password=pass;"
  }
}

Then i pass it to a static class from the "Startup.cs" file, like this:

public void ConfigureServices(IServiceCollection services)
{
    // Add framework services.
    services.AddMvc();

    Orm.DatabaseConnection.ConnectionString = Configuration["ConnectionStrings:ConnectionString"];
}

This is where I initiate the connection:

using System.Data.SqlClient;

namespace MyProject.Orm
{
    public static class DatabaseConnection
    {
        public static string ConnectionString { get; set; }

        public static SqlConnection ConnectionFactory()
        {
            return new SqlConnection(ConnectionString);
        }
    }
}

And this is my controller:

public string Get()
{
    using (var databaseConnection = Orm.DatabaseConnection.ConnectionFactory())
    {
        var sections = databaseConnection.Query("SELECT * FROM myTable").ToList();
        return sections.ToString();
    }
}

Where this line:

var databaseConnection = Orm.DatabaseConnection.ConnectionFactory();

returns:

ServerVersion: "'databaseConnection.ServerVersion' threw an exception of type 'System.InvalidOperationException'"
Message: "Invalid operation. The connection is closed."
Source: "System.Data.SqlClient"
StackTrace: "at 
System.Data.SqlClient.SqlConnection.GetOpenTdsConnection()\n   
at 
System.Data.SqlClient.SqlConnection.get_ServerVersion()"

And i get this error on new SqlConnection: "error CS0119: 'SqlConnection' is a type, which is not valid in the given context".

But the program execution doesn't stop because of these errors.

The application then hangs on the following line:

var sections = databaseConnection.Query("SELECT * FROM myTable").ToList();

I'm using Dapper as my ORM (not EntityFramework). In "myTable" sql table are only 17 rows and 5 columns so it should load fast.

I tried all kinds of different connectionStrings but it always fails. If i try the same with .NET Framework 4.5, everything works fine. The problem is .NET Core 2.0.

Any idea about fixing it is welcome. Because i spent too many hours on this already.

Answer 1

Try to add databaseConnection.Open().

public string Get()
{
    using (var databaseConnection = new SqlConnection(@"Server=ip;Database=dbname;User Id=xxx\user;Password=pass;Pooling=false;"))
    {
        databaseConnection.Open();
        var sections = databaseConnection.Query("SELECT * FROM myTable").ToList();
        return sections.ToString();
    }
}

To avoid problems with connection pool that described in comments you add Pooling=false; to connection string: Server=ip;Database=dbname;User Id=xxx\user;Password=pass;Pooling=false;

Edit: I hardcoded connection string and removed factory to make example smaller