System.Data.SqlClient.SqlConnection unexpected result in Open() Method with incomplete ConnectionString

Question

When calling the Open() method on a SqlConnection instance that got passed an incomplete connection string, the method does not throw an exception.

I have created the following example to showcase my problem.

var success = true;

var connectionStrings = new[]
        {
            "Integrated Security=SSPI;",
            "Initial Catalog=awdemo;Integrated Security=SSPI;",
            "Data Source=.\\sql2016;Initial Catalog=awdemo;Integrated Security=SSPI;"
};

foreach (var connectionString in connectionStrings)
{
    var conn = new SqlConnection(connectionString);

    try
    {
         conn.Open();
    }
    catch (Exception)
    {
        success = false;
    }
    finally
    {
        conn.Close();
        Console.WriteLine($"{connectionString} - Success = {success}");
    }
}

Result:

Integrated Security=SSPI; - Success = True
Initial Catalog=awdemo;Integrated Security=SSPI; - Success = True
Data Source=.\sql2016;Initial Catalog=awdemo;Integrated Security=SSPI; - Success = True

I would expect conn.Open() to throw an exception for the first two connection strings, since both of them are incomplete and not valid.

Why is there no exception thrown?

EDIT: as pointed out by Steve in the comments an exception is not thrown because the component connects to my default instance of SQL Server when not providing the server information.

Is there a way to force the SqlConnection component to throw an error on incomplete connection strings? In the application it is possible and allowed to create incomplete connection strings.

Sql server settings? I got FALSE: Integrated Security=SSPI; - Success = True Initial Catalog=awdemo;Integrated Security=SSPI; - Success = False Data Source=.\sql2016;Initial Catalog=awdemo;Integrated Security=SSPI; - Success = False — Leszek P, Dec 04 '18 at 16:36
@LeszekRepie change the .\sql2016 to an existent sql server instance (and the last one change to an existent database in the target server) — Steve, Dec 04 '18 at 16:39
You could use `DbConnectionStringBuilder`, see [here](https://stackoverflow.com/questions/434864/how-to-check-if-connection-string-is-valid) — Andy G, Dec 04 '18 at 16:40
There are defaults at work here. The first connection open on a local machine host and open the first database in that instance, the second open on the specified machine again with the first database. The third is as expected. Try to add a _DataTable dt = conn.GetSchema("TABLES")_ after the open connection to understand to which database you get connected. — Steve, Dec 04 '18 at 16:41
@Steve This! I get connected to the master database of my default instance (as it seems). Now i understand how the component works. Your answer is very helpful. — Takeshi, Dec 04 '18 at 16:52
@AndyG I tried using the DBConnectionStringBuilder. I get the same results as in my question. But now I understand why, which is why i updated the question with this information. I'm still getting connected to my default instance. — Takeshi, Dec 04 '18 at 17:03

Scott Hannen · Accepted Answer · 2018-12-04T17:07:06.653

There's no practical way to fully validate this. You could have a connection string that has all of the components you expect, including a valid server and database name, but it's the wrong server or wrong database.

So if the expectation is that the class should fail on conn.Open() if the connection string is incorrect (not invalid, but incorrect), you can't completely achieve that.

Does it matter? Imagine a few scenarios, all of which include incorrect connection strings:

The connection string is "blarg!" and so attempting to open the connection throws an exception.
The connection string doesn't contain a server or database name. You can open the connection but when you try to execute some command it fails.
The connection string contains a server and database name, but it's the wrong server or database. It fails for the same reason as 2.

In each of the scenarios, what's the first thing you're going to do? You're going to look at the exception. Regardless of which line throws it, you're going to quickly deduce that you have the wrong connection string.

So a "validation" which can't actually validate the connection string before you open it is just going to add work. If the connection string is wrong, that truly is an "exceptional" condition so it's probably better to just let the code throw an exception where it does. You'll find the problem quickly.

All of that aside, suppose you just really want to be sure that your connection contains a server name and a database name before you try to open it. You could write an extension method like this:

// Maybe give it a better name.
public static void ValidateThatConnectionHasDataSourceAndDatabase(this SqlConnection connection)
{
    if (string.IsNullOrEmpty(connection.DataSource)) 
        throw new Exception("The connection has no datasource");
    if (string.IsNullOrEmpty(connection.Database)) 
        throw new Exception("The connection has no database");
}

After you create the connection and before you open it, call conn.ValidateThatConnectionHasDataSourceAndDatabase();

I agree with your argumentation. Your suggestion made me realize that I have to redesign my connection-module to avoid passing incomplete connection strings since we probably will run into the same problem with other data base technologies the module supports. — Takeshi, Dec 04 '18 at 17:17

System.Data.SqlClient.SqlConnection unexpected result in Open() Method with incomplete ConnectionString

1 Answers1