Create SQL Express 2008 database in C # code but connection fails when trying to connect to sysadmin
I have a piece of code that builds SQL Server Express 2008 at runtime and then tries to connect to it to perform a database initialization script in Transact-SQL. The code that creates the database is as follows:
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 was created successfully. After that, I am trying to connect to the database to run the initialization script 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 does not work, throwing the following exception:
Unable to open database "TestData" requested by login. Login failed. Login failed for user 'MYDOMAIN \ MyUserName'.
I am completely puzzled by this error because the account I am trying to connect to has sysadmin privileges, which should allow me to connect to any database (note that I am using the main database connection to create the database in the first place).
a source to share
Is there a reason why you are specifying User Instance = True when creating it but not when trying to connect to it?
When you create it after connecting with a user instance, it will create the database files, but not bind it to your actual instance. You either don't need to specify User Instance = True in the first connection string, or add it to the second and specify the database file to use.
a source to share
You will need to issue the CREATE USER command (see http://msdn.microsoft.com/en-us/library/ms173463.aspx ) after creating a database, but before trying to open a connection to that database.
For instance:
CREATE USER 'MYDOMAIN\myusername' FOR LOGIN 'MYDOMAIN\myusername'
a source to share