Cannot connect to SQL Server 2014 from Visual Studio

Question

I have installed SQL Server 2014 as well as Management Studio. Than I have imported database from .bkp file and try to make connection. My connection string looks like this.

<connectionStrings>
    <add name="Name" 
         connectionString="server=PCNAME\SQLEXPRESS;database=DatabaseInMSSQL;User ID=sa;Password=password" 
         providerName="System.Data.SqlClient" />
</connectionStrings>

And this is error:

Cannot open database \"DatabaseName\" requested by the login. The login failed.
Login failed for user 'sa'

Here is the code that I am using to connect

public void OpenConnection()
{
    if (connection == null)
    {
        connection = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["Name"].ConnectionString);
    }

    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }
}

I can login normally as well as windows auth, and SQL Server Authentication in SQL Server Management Studio with that password and user. My firewall is temporarily off!

I am using VS 2013 Community edition, and Windows 8.1 64bit

Answer 1

It would appear that the user sa (which you should never ever use for any kind of work anyways!) has a default database set as DatabaseName which doesn't exist.

How to fix it?

• Login as sa or any other SQL Server system admin
• Go to Object Explorer > Security > Logins > sa and right-click and select Properties

• Change the default database for that user to something that does exist