How to fix SqlConnectionString.Open() exception, login failed

Question

I'm trying to establish a SqlConnection in my C# application, the application was working fine with Oracle DB but I wish to connect it to MS SQL now. I've made the necessary code changes which are mentioned in the code section below. The issue is SqlConnectionString,Open() throws exception

In web.config file I've added the CONNECTIONSTRING value as "Server=TCP:myServerName,Port_No\myInstanceName;Database=myDataBase;User Id=Username;Password=Password;"

private DataSet FireQuery(SqlCommand command, String tablename)
    {
        SqlConnection conn = new SqlConnection(CONNECTIONSTRING);
        SqlDataAdapter adapter = new SqlDataAdapter();
        conn.Open();    //Code throws exception here

        command.CommandType = CommandType.Text;
        command.Connection = conn;

        adapter.SelectCommand = command;

        DataSet ds = new DataSet();
        adapter.Fill(ds, tablename);
        conn.Close();
        return ds;
    }

Exception thrown is System.Data.SqlClient.SqlException:

Cannot open database "mydataBase" requested by the login. The login failed. Login failed for user 'Username'.

Answer 1

The "cannot open database" error means the authentication succeeded but the database context could not be set. This indicates either database "myDataBase" does not exist on the instance (or is offline) or the user does not have permissions to use it. In the latter case, the script example below will allow the user to use the database and objects.

USE mydataBase;
--create a database user mapped to login of same name
CREATE USER Username; 
--also GRANT permissions on objects used directly by application
GRANT SELECT ON TABLE dbo.YourTable TO Username;