Attach database programmatically

Question

I have a login form in my project and I write below code to attach my database( there is in d:\ ) when this login form loads:

try
{ 
    SqlConnection con = new SqlConnection(@"Data Source=.;Initial Catalog=master;Integrated Security=True");
    con.Open();

    SqlDataAdapter da = new SqlDataAdapter("select name from sys.databases", con);
    DataTable dt = new DataTable();
    da.Fill(dt);

    string[] array = dt
        .AsEnumerable()
        .Select(row => row.Field<string>("Name"))
        .ToArray();

    if (!array.Contains("cstmrDB", StringComparer.OrdinalIgnoreCase))
    {
        SqlCommand cmd = new SqlCommand("sp_attach_db");
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@dbname", "Apdb");
        cmd.Parameters.AddWithValue("@filename1", @"d:\Apdb.mdf");
        cmd.ExecuteNonQuery();

    }
}
catch (exception ex) 
{ 
    messagebox.show(ex.message); 
}

It works fine in my laptop. I publish my project (using c# publish) and install SQL Server and my project and copy my database in d:\ in another PC. but when i run my project, the database won't attach! I don't know why this problem occurrs... but I think maybe the reason is that I don't write any code to define *.ldf file (but i put both mdf and ldf file in d:\ )

ERROR: A network-related or instance-specific error occurred while establishing a connection to SQL Server.

The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Well, yeah, basic debugging skills are really hard to get. Start with catching the exception and looking at the SQL Server log to see what happens. I am quite sure the call does not magically just do nothing. — TomTom, Feb 29 '16 at 12:59
@Ali Can you connect to the SQL server on that machine using SSMS? — CodingGorilla, Feb 29 '16 at 17:59
Now kick all the lines from your code that are not relevant to a "can not connect to server". Hint: the only thing we care about is the connection string, the rest is irrelevant. And I would bet that "." is not a valid server name. — TomTom, Feb 29 '16 at 18:00
@TomTom Yes, "." connects you to the local instance, you usually see it with SQL Server express in the form of: `.\MYINSTANCE`. — CodingGorilla, Feb 29 '16 at 18:03
Please refrain from putting tags directly in the title. For more information, see [Should questions include tags in the title](https://meta.stackexchange.com/questions/19190/should-questions-include-tags-in-their-titles) — , Feb 29 '16 at 18:04
@CodingGorilla when i try to manually attach my database, i got something like that in sql server Unable to open the physical file "G:\AP.mdf". Operating system error 5... — Ali, Feb 29 '16 at 18:04
@Ali, see my answer, but no that would only apply if you installed the SQL Server with an instance name which is not the default instance name. — CodingGorilla, Feb 29 '16 at 18:07
@TomTom what should i write in my connection string? im beginner — Ali, Feb 29 '16 at 18:07

score 3 · Accepted Answer · answered Feb 29 '16 at 18:06

3

I would venture to guess this is a permissions issue, usually a freshly installed SQL Server is running a NETWORK_SERVICE which is a pretty low privilege account. It probably does not have access to the root of your G drive (or any part of it for that matter).

You can test this very quickly by changing the servie to run as LocalSystem. Once you've confirmed this as an issue I would recommend changing the log on use back to NETWORK_SERVICE and then applying the appropriate permissions to the folder/files that need it.

answered Feb 29 '16 at 18:06

CodingGorilla

19,612
4
45
65

how can i "changing the log on use back to NETWORK_SERVICE and then applying the appropriate permissions to the folder/files that need it" – Ali Feb 29 '16 at 18:11
See this link about configuring a service to run as `NETWORK SERVICE`: https://technet.microsoft.com/en-us/library/cc755249.aspx. Once you've got it running as `NETWORK SERVICE` again then you can simply add that user to the file/folder permissions as necessary (I usually add it to the ACL as NETWORK_SERVICE). – CodingGorilla Feb 29 '16 at 18:14
how can i do it programmatically?! – Ali Feb 29 '16 at 18:17
Do what programmatically? – CodingGorilla Feb 29 '16 at 18:23
"changing the log on use back to NETWORK_SERVICE and then applying the appropriate permissions to the folder/files that need it" i dont want user do this steps... – Ali Feb 29 '16 at 18:26
You probably cannot do this programmatically, and if you can you should not. This involves the modifying the permissions and services on the destination system; if you cannot depend on someone setting the proper permissions then have who ever installs the SQL Server run it as local system. – CodingGorilla Feb 29 '16 at 18:48
ok thanks. but Is there another way for attach or create database that dont have this problems? – Ali Mar 01 '16 at 05:42
Yes, make sure that your database file is in the folder designated as the "Data" folder for the SQL server, it will have permissions to this folder already. Here's an SO post on how to find that location: http://stackoverflow.com/questions/1883071/how-do-i-find-the-data-directory-for-a-sql-server-instance. Alternatively you can change the default location when SQL Server is installed. – CodingGorilla Mar 01 '16 at 12:17
thanks, I'm sorry that I ask a lot of questions. according to SO post, should i run this script on destination computer??! – Ali Mar 01 '16 at 17:47
Yes, it would be wherever you are planning on running the code. – CodingGorilla Mar 01 '16 at 17:48

Attach database programmatically

1 Answers1

Linked