Why won't my application connect to SQL Server database when I can connect using SQL Server Management Studio with the same login?

Question

I realize this may be a difficult (impossible?) question to answer since I can't provide access to the actual server involved, but maybe there's something obvious I'm missing.

I have a C# Windows Forms application (.Net 4.5) that needs to connect to a SQL Server 2012 database. The connection string in App.config is as follows:

<connectionStrings>
  <add name="MyConnStr"
    connectionString="server=SERVERNAME.ads.lcl;database=MyDatabase;uid=MyUser;password=abcdefg1234567"
    providerName="AspNetSqlProvider" />

I am able to connect to and read from the database with SQL Server Management Studio using the same login and password (with SQL Server Authentication), so I know I have the server, database, uid, and password correctly specified in the connection string. But when I debug my program, I get an SQLException saying Cannot open database "MyDatabase" requested by the login. The login failed. Login failed for user 'MyUser'.

I've done this a thousand times before, so I'm at a loss.

Here are the relevant sections of code, if it helps at all:

public static string MyConnStr
{
  get { return ConfigurationManager.ConnectionStrings["MyConnStr"].ConnectionString; }
}

public static int[] GetIntArrayFromDB(string query)
{
  DataTable dt = new DataTable();

  using (SqlConnection conn = new SqlConnection(MyConnStr))
  {
    using (SqlCommand cmd = new SqlCommand(query, conn))
    {
      using (SqlDataAdapter da = new SqlDataAdapter(cmd))
      {
        try
        {
          conn.Open();
          da.Fill(dt);
          conn.Close();
        }
        catch (SqlException)
        {
          return null;
        }
        finally
        {
          if (conn != null)
            conn.Dispose();
        }
      }
    }
  }

  int numRows = dt.Rows.Count;
  int[] retInt = new int[numRows];
  for (int i = 0; i < numRows; i++)
  {
    retInt[i] = Convert.ToInt32(dt.Rows[i][0]);
  }

  return retInt;
}

The query being passed to GetIntArrayFromDB() is "SELECT myID FROM myTable". Any ideas?

UPDATE: The approach above with the named connection string is perfectly valid and works, if you don't misspell the connection string name in the config file. Sorry for this, I'm working on a sleep deficit. Thanks to everyone who offered suggestions!

Do you need to specify the initial catalog in your connection string? I.e. the name of the database on the database server. — MaxRev17
Do you have enabled the TCP protocol in the Server Configuration Manager? — Steve
A Web Application project in the same solution is using the exact same connection string format with no problems. — timbck2

timbck2 timbck2 · Accepted Answer · 2013-12-12T19:02:17

I fixed it by changing the connection string specified in the App.config file to the following (note that the format of the connection string itself is the same, only the name is different):

<connectionStrings>
  <remove name="LocalSqlServer" />
  <add name="LocalSqlServer" connectionString="server=MyServer.ads.lcl;database=MyDatabase;uid=MyUser;password=abcdefg1234567"
    providerName="AspNetSqlProvider" />
</connectionStrings>

and by referencing the connection string in my C# code this way:

get
{
  return ConfigurationManager.ConnectionStrings[0].ConnectionString;
}

I do NOT understand why this made any difference! It seems quite bizarre.

Why won't my application connect to SQL Server database when I can connect using SQL Server Management Studio with the same login?

3 Answers