Why attaching database programmatically using C# fails?

Question

I am new to SQL Server and C#. I have Computer1, Windows 7. I use Windows Forms and SQL Server 2008 R2 Express.

I developed a software which will be running later in other Windows computers and those computers will only have SQL Server database engine (no management studio installed).

Instead of attaching the database manually I want to attach it programmatically when the programs starts.

I created a folder (D:\MyFolder) and I placed the mydb.mdf and mydb.ldf files in it (re-created database).

Then I used the following code to attach mydb database to the server and it was successful done and I can read from the database. I have to give MyFolder Full control permission or otherwise the program will throws database login failure.

Also I have Computer2 which has exactly same windows, same SQL Server version as in computer1. I copied both mdf and ldf files from computer1 and pasted them in D:\MyFolder in computer2 and I run the program the database attached successfully.

Until now everything works fine and both computers attached the database fine. Now I go to computer2, to D:\MyFolder and right click on the database files and delete them and I go to computer1 and copy the database files and paste them into computer2 (in same directory D:\MyFolder) and when i run the program it says:

Cannot open database "mydb" requested by login. the login failed. Login failed for user 'Computer name/ username'

Even after I gave permission for everyone on MyFolder, it still throws the same error.

I searched all day and I checked these answers here, here, here, here and here but nothing helped. Anyone knows what can I do to fix it? any suggestion will be helpful.

private void Attach_DB_Click(object sender, EventArgs e)
{
    try
    {
        // This is to give permissions to folder
        string folderPath = @"D:\MyFolder";
        var directoryInfo = new DirectoryInfo(folderPath);
        var directorySecurity = directoryInfo.GetAccessControl();
        var currentUserIdentity = WindowsIdentity.GetCurrent();
        var fileSystemRule = new FileSystemAccessRule(currentUserIdentity.Name, FileSystemRights.FullControl, InheritanceFlags.ObjectInherit | InheritanceFlags.ContainerInherit, PropagationFlags.None, AccessControlType.Allow);

        directorySecurity.AddAccessRule(fileSystemRule);
        directoryInfo.SetAccessControl(directorySecurity);

        // This is to connect to the attached database
        SqlConnection MyConnection = new SqlConnection(@"Data Source=localhost ; AttachDbFilename=D:\MyFolder\mydb.mdf; Initial Catalog =mydb; Integrated Security=true");

        SqlCommand MyCommand = new SqlCommand();
        DataTable DataTable = new DataTable();

        SqlDataAdapter Sql_Data_Adapter = new SqlDataAdapter();
        DataTable.Rows.Clear();

        MyConnection.Open();
        MyCommand.CommandText = "SELECT * FROM Table_Customers ";
        MyCommand.Connection = MyConnection;

        Sql_Data_Adapter.SelectCommand = MyCommand;
        Sql_Data_Adapter.Fill(DataTable);

        dataGridView1.DataSource = DataTable;

        MyCommand.Parameters.Clear();
        Sql_Data_Adapter.Dispose();
        MyConnection.Close();
    }
    catch (System.Exception excep)
    {
        MessageBox.Show(excep.Message);
    }
}

How can you do anything manually in C#? It's a programming language so everything must be programmatic. — Enigmativity, Jan 26 '19 at 13:38
Enigmativity- it can be done via MS SQL management studio but I do not want to. — Toni, Jan 26 '19 at 13:43
@Toni - Don't forget to use the `@` notification system when replying to someone. — Enigmativity, Jan 26 '19 at 22:43

score 0 · Answer 1 · answered Jan 26 '19 at 13:19

0

Use SQL Server's instance name in connection string.

e.g.

if your are using Express Edition and your instance name is SQLEXPRESS, then try localhost\SQLEXPRESS or (local)\SQLEXPRESS instead of just localhost.

answered Jan 26 '19 at 13:19

Waqas Shabbir

755
1
14
34

score 0 · Answer 2 · answered Jan 26 '19 at 13:33

0

The answer lies in your connection.

Integrated Security=true

According to the documentation,

When true, the current Windows account credentials are used for authentication.

The Windows account credentials rarely work from one computer to another. If you cannot use a DDL (the preferred way) then you need to use Username and Password in your database before exporting the file.

answered Jan 26 '19 at 13:33

Leonardo Herrera

8,388
5
36
66

ok but Before adding a User you must first create a global Login, under Security -> Logins.you can add them as a User under a particular database. but I do not want to create global user on each machine that the program will be installed in. any other idea? – Toni Jan 26 '19 at 15:26
I thought you said you were using the Express version. You don't need to create a user in each machine. – Leonardo Herrera Jan 28 '19 at 14:33

Why attaching database programmatically using C# fails?

2 Answers2