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.

try to replace Server=ip to Server=IP\Your SQL instance name — andy, Jul 26 '17 at 12:05
Are you absolutely, positively sure it throws on *that* line? The `SqlConnection` constructor shouldn't be doing *anything* with the `ServerVersion` property. This is exactly the error you get if you try to read `.ServerVersion` on a closed connection (`new SqlConnection("").ServerVersion`), which I'd expect to happen later. — Jeroen Mostert, Jul 26 '17 at 12:13
I agree with @JeroenMostert Simply creating a new SqlConnection object should not throw this error afaik. It has more to do with when you try to use the SqlConnection, possibly with Open(). — Jonathan Willcock, Jul 26 '17 at 12:18
I don't know the line, because no exception gets thrown. I can see this error only when i set breakpoints. But there is no line provided where it happened. :( — tedi, Jul 26 '17 at 13:32
You don't see the error in the *debugger*, by any chance? Because this is perfectly normal (and expected) if you're trying to view the `ServerVersion` property of a closed connection, as might happen if you're trying to show all public properties. In that case, it has absolutely nothing to do with whatever problem your application is running into. — Jeroen Mostert, Jul 26 '17 at 13:41
Yes i see this error in the debugger. So what then? I'm to square one i guess. I have no clue what the problem is. :D — tedi, Jul 26 '17 at 13:43
Rather than breakpoints, break into the application as it "hangs", then analyze where it's spending its time (if anywhere) with Parallel Stacks/Tasks. If there are no waits, you might be dealing with a background task that died without being observed -- to diagnose that, configure the debugger to break on all thrown exceptions (Debug -> Windows -> Exception Settings, then enable all Common Language Runtime Exceptions). You may get some spurious exceptions that are caught and handled and have nothing to do with the problem, but you might also see one that does. — Jeroen Mostert, Jul 26 '17 at 13:44
Looks like that the problem is on the next line, it hangs there. I have added the line to the question. — tedi, Jul 26 '17 at 13:59
Add `Connect Timeout=5` to the connection string to see if the connection isn't simply, well, timing out as it tries to connect to the server. Do a simple `databaseConnection.Open()` rather than a query. If this succeeds, then the problem is in the Dapper code that queries (somewhere, somehow). Perform the `SELECT` in Management Studio to see if things aren't just blocking at the database end. — Jeroen Mostert, Jul 26 '17 at 14:07
This is weird. I have set the timeout to 5 seconds but the application still hangs... Everything works fine in SQL Management Studio/DBeaver, the query also works with .NET Framework 4.5 with Dapper. Now that i have added `databaseConnection.Open()` it times out after 5 seconds. Now i get this error: `"System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."`. If i set the timeout to 15, then it hangs indefinitely again. — tedi, Jul 27 '17 at 06:38
Looks like it's random... I ran it again with 5 second timeout and now it hangs again. Doesn't throw the timed out exception. And the code is the same as before when it did throw the timed out exception. — tedi, Jul 27 '17 at 06:51
Can you please show the 10 or so lines of code before it hangs? In my experience problems like these are often "delayed reactions". Something is wrong earlier, which only later causes a hang, so it appears that the hang is caused by something relatively innocent. — Jonathan Willcock, Jul 29 '17 at 07:08
@JonathanWillcock there is literally no more code. Everything is in the question. — tedi, Jul 30 '17 at 06:57
https://stackoverflow.com/questions/670774/how-can-i-solve-a-connection-pool-problem-between-asp-net-and-sql-server https://blogs.msdn.microsoft.com/angelsb/2004/08/25/connection-pooling-and-the-timeout-expired-exception-faq/ https://blogs.msdn.microsoft.com/spike/2012/05/21/system-invalidoperationexception-timeout-expired-the-timeout-period-elapsed-prior-to-obtaining-a-connection-from-the-pool/ https://blogs.msdn.microsoft.com/spike/2008/08/25/timeout-expired-the-timeout-period-elapsed-prior-to-obtaining-a-connection-from-the-pool/ have a look at these — t1f, Jul 30 '17 at 09:45
To find the problem I recommend test the very basic sql operation like:1-create connection.2-open it. 3-call ADO.NET command like "SELECT * FROM myTable" by dataAdapter. 4-close connection. — Mostafa Vatanpour, Jul 30 '17 at 13:00
Please include all code where you are working with connection. line `var databaseConnection = Orm.DatabaseConnection.ConnectionFactory();` looks incorrect. It should be `using (var databaseConnection = Orm.DatabaseConnection.ConnectionFactory()) {...}` — Mikhail Lobanov, Jul 31 '17 at 11:14
@MikhailLobanov i tried with "using". But the result is still the same. — tedi, Aug 01 '17 at 07:29
@JedatKinports Have a looke a this link , it may help you https://stackoverflow.com/questions/37597300/net-core-dapper-connection-string — Eid Morsy, Aug 01 '17 at 10:02
What about using `Configuration["ConnectionStrings:RutarConnectionString"]` instead of `Configuration["ConnectionStrings:ConnectionString"]` in `ConfigureServices` method!? ;). — shA.t, Aug 01 '17 at 13:24
This was a typo when i tried to hide the credentials etc. :D This is not the issue, it's correct in my project. :) — tedi, Aug 01 '17 at 13:51
Just shooting: Will Data source & Initial Catalog instead of Server/Database help here ? — Arun Vinoth-Precog Tech - MVP, Aug 01 '17 at 20:59
Your connection string looks a bit suspect. You have your user id as xxx\user which looks like a windows login. SQL server will only accept user id/password logins for SQL Server accounts, you need to use a trusted login for windows logins. — Neil, Aug 04 '17 at 19:20
@Neil the strange thing which i don't get is, why does the same connectionString work with .NET Framework 4.5. — tedi, Aug 04 '17 at 21:59
I can't really explain how that connection string would work but one difference between using a connection string in an app.config or web.config vs appsettings.json is the need to escape the \ character in the appsetting.json file. Your user Id would need to be xxx\\user to get picked up correctly. Same goes if you have a \ for a named instance or any other reason. — Neil, Aug 04 '17 at 23:27
@Neil surprisingly, both seem to be ok, double and single backslash, i also tried with slashes but the outcome is the same still. :( — tedi, Aug 06 '17 at 16:12
Have you checked the SQL server logs for connection or login errors? — Neil, Aug 07 '17 at 19:59
@Neil i don't have access to it. But i will try to setup one locally. — tedi, Aug 08 '17 at 06:50

Mikhail Lobanov · Answer 1 · 2017-08-01T14:06:20.453

6

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

edited Aug 01 '17 at 14:06

answered Aug 01 '17 at 07:59

Mikhail Lobanov

2,976
9
24

Now it hangs at: `databaseConnection.Open();`. But before that if i set some breakpoints i get this error on `new SqlConnection`: `"error CS0119: 'SqlConnection' is a type, which is not valid in the given context"`. The program execution doesn't stop because of this error though. – tedi Aug 01 '17 at 08:25
Is this a compilation error in DatabaseConnection.ConnectionFactory()? I do not have any `new SqlConnection` rows. How it was compile before? – Mikhail Lobanov Aug 01 '17 at 08:51
@JedatKinports, I change connection string only, it cannot cause any compilation errors – Mikhail Lobanov Aug 01 '17 at 12:11
And you can connect successfully? Could you share the connectionSting that you use (hide the credentials of course)? Or if you could share how your connectionString looks in the appsettings.json file please. Appreciated. – tedi Aug 01 '17 at 12:34
@JedatKinports To be honest, I do not test my solution. I have an idea that adding `Pooling=false;` to connection string can help you with your connection pooling problems and write an answer. Code that you provide must compile. If you have compilation problems please update your question. You also should hardcode your connection string in your factory, because reading configs is not belongs your question – Mikhail Lobanov Aug 01 '17 at 13:29
@JedatKinports I mean you should harcode your connection string for stackoverflow only to make your example minimal. I've edited my answer – Mikhail Lobanov Aug 01 '17 at 13:36
When i hardcoded it, but the result is the same. :( Just though to give it a try you never know... Sometimes such things work. :D – tedi Aug 01 '17 at 13:53
Is error in this line: `using (var databaseConnection = new SqlConnection("Server=ip;Database=dbname;User Id=xxx\user;Password=pass;Pooling=false;"))`? – Mikhail Lobanov Aug 01 '17 at 13:55
Yes, it's the same. I still get this on mouse hover the "SqlConnection": "CS0119". – tedi Aug 01 '17 at 14:00
In my environment it compiles without any errors. I think you make some typo error... – Mikhail Lobanov Aug 01 '17 at 14:40
It compiles for me too (i can see the error only with debugger, if i don't set any breakpoints then no errors arise). But it hangs. :/ – tedi Aug 04 '17 at 21:53

score 2 · Answer 2 · answered Aug 02 '17 at 16:50

Try creating a self-contained deployment, this should eliminate and strange dependency stuff. If it works then at least you know that it's due to some assembly binding type stuff.

The exception "error CS0119: 'SqlConnection' is a type, which is not valid in the given context" smells like it is.

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

2 Answers2