Create an SQL Express 2008 database in C# code, but login fails when trying to connect with a sysadmin

Question

I have a piece of code that creates an SQL Server Express 2008 in runtime, and then tries to connect to it to execute a database initialization script in Transact-SQL. The code that creates the database is the following:

private void CreateDatabase()
    {
        using (var connection = new SqlConnection(
            "Data Source=.\\sqlexpress;Initial Catalog=master;" +
            "Integrated Security=true;User Instance=True;"))
        {
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText =
                "CREATE DATABASE " + m_databaseFilename +
                " ON PRIMARY (NAME=" + m_databaseFilename +
                ", FILENAME='" + this.m_basePath + m_databaseFilename + ".mdf')";
                command.ExecuteNonQuery();
            }
        }


    }

The database is created successfully. After that, I try to connect to the database to run the initialization script, by using the following code:

private void ExecuteQueryFromFile(string filename)
    {
        string queryContent = File.ReadAllText(m_filePath + filename);
        this.m_connectionString = string.Format(
            @"Server=.\SQLExpress; Integrated Security=true;Initial Catalog={0};", m_databaseFilename);
        using (var connection = new SqlConnection(m_connectionString))
        {
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText = queryContent;
                command.CommandTimeout = 0;
                command.ExecuteNonQuery();
            }
        }
    }

However, the connection.Open() statement fails, throwing the following exception:

Cannot open database "TestData" requested by the login. The login failed. Login failed for user 'MYDOMAIN\myusername'.

I am completely puzzled by this error because the account I am trying to connect with has sysadmin privileges, which should allow me to connect any database (notice that I use a connection to the master database to create the database in the first place).

Answer 1

Is there a reason you specify User Instance=True when you create it but not when you try to connect to it?

When you create it after connecting with User Instance, it will create the database files but does not attach it to your actual instance. You'll either have to not specify User Instance=True in the first connection string or add it to the second and specify the database file to use.