SQL Server Express doesn't work
-
I try to open the SQL Server Express database file "sample.mdf" but it doesn't work. Here is the code: -------------------------------------------------------------------------- using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.OleDb; using System.Data.SqlClient; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { //OleDb-ACCESS //-------------- //OleDbConnection con = new OleDbConnection(); //string comPath = Server.MapPath("~" + @"\") + @"app_Data\webTest.mdb"; //string coS = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + comPath; //SqlClient-SQL server express //------------------------------ System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(); string comPath = Server.MapPath("~" + @"\") + @"app_Data\sample.mdf"; string coS = "Data Source=.\\SQLEXPRESS;AttachDbFilename=" + comPath + ";Integrated Security=True;User Instance=False;"; //OleDb-SQL server express //------------------------- //OleDbConnection con = new OleDbConnection(); //string comPath = Server.MapPath("~" + @"\") + @"app_Data\sample.mdf"; //string coS = "Provider=SQLOLEDB.1;" + "Data Source=" + comPath; con.ConnectionString = coS; con.Open(); } } -------------------------------------------------------------------------- 1. Code OleDb-ACCESS (commented) works OK. 2 . When I run the code in section: SqlClient-SQL Server express, I get error: Server Error in '/myWeb/trySqlServer' Application. -------------------------------------------------------------------------- CREATE DATABASE permission denied in database 'master'. An attempt to attach an auto-named database for file C:\MyPrograms\web\trySqlServer\app_Data\sample.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: CREATE DATABASE permission denied in database 'master'. An attempt to attach an auto-named database for file
-
I try to open the SQL Server Express database file "sample.mdf" but it doesn't work. Here is the code: -------------------------------------------------------------------------- using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.OleDb; using System.Data.SqlClient; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { //OleDb-ACCESS //-------------- //OleDbConnection con = new OleDbConnection(); //string comPath = Server.MapPath("~" + @"\") + @"app_Data\webTest.mdb"; //string coS = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + comPath; //SqlClient-SQL server express //------------------------------ System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(); string comPath = Server.MapPath("~" + @"\") + @"app_Data\sample.mdf"; string coS = "Data Source=.\\SQLEXPRESS;AttachDbFilename=" + comPath + ";Integrated Security=True;User Instance=False;"; //OleDb-SQL server express //------------------------- //OleDbConnection con = new OleDbConnection(); //string comPath = Server.MapPath("~" + @"\") + @"app_Data\sample.mdf"; //string coS = "Provider=SQLOLEDB.1;" + "Data Source=" + comPath; con.ConnectionString = coS; con.Open(); } } -------------------------------------------------------------------------- 1. Code OleDb-ACCESS (commented) works OK. 2 . When I run the code in section: SqlClient-SQL Server express, I get error: Server Error in '/myWeb/trySqlServer' Application. -------------------------------------------------------------------------- CREATE DATABASE permission denied in database 'master'. An attempt to attach an auto-named database for file C:\MyPrograms\web\trySqlServer\app_Data\sample.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: CREATE DATABASE permission denied in database 'master'. An attempt to attach an auto-named database for file
If you are using the the App_Data directory under a Web application then you should be using the
|DataDirectory|functionality. Then you don't need to do theServer.MapPathyourself. On first glance it looks like your SQLEXPRESS instance doesn't have permission to the data directory, the directory doesn't exist, or the file doesn't exist. But upon closer inspection, you want to remove the setting "User Instance=false" that is going to try to mount the database differently than what you are trying to accomplish. For more information read this. That being said, having your web app be able to create files, even database files in your sites directories, or even anywhere other than the temp directory is a huge security risk. Consequently, your "CREATE DATABASE" looks to be expected behavior. One final note: ASP.NET defines a connectionstring called "LocalSqlServer" in the machine.config, thus it is inherited by all asp.net websites, the default concection string isdata source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true
which is nearly identical to your connection string (once the user instance bit is corrected), and the structure of that particular database provides support for all the normal user, password, and permission templates that .NET provides.
-
If you are using the the App_Data directory under a Web application then you should be using the
|DataDirectory|functionality. Then you don't need to do theServer.MapPathyourself. On first glance it looks like your SQLEXPRESS instance doesn't have permission to the data directory, the directory doesn't exist, or the file doesn't exist. But upon closer inspection, you want to remove the setting "User Instance=false" that is going to try to mount the database differently than what you are trying to accomplish. For more information read this. That being said, having your web app be able to create files, even database files in your sites directories, or even anywhere other than the temp directory is a huge security risk. Consequently, your "CREATE DATABASE" looks to be expected behavior. One final note: ASP.NET defines a connectionstring called "LocalSqlServer" in the machine.config, thus it is inherited by all asp.net websites, the default concection string isdata source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true
which is nearly identical to your connection string (once the user instance bit is corrected), and the structure of that particular database provides support for all the normal user, password, and permission templates that .NET provides.
-
Thank you Alaric. I tried it and it works. Now I would like to switch to SQL SERVER 2005 instead of SQL Server Express. How should I change the connection string? Thanks, Hezi
There are lots of settings in the connectionstring for SQL server, I would visit connectionstrings.com or msdn.microsoft.com to get all the details. But GENERALLY you needs just a handful of settings. Data Source - this is your server, and instance name,
localhost,., and(local)are all aliases to your local machine. If you are using anything other than the default instance, like SQLExpress, you need to add it after the servername, with a backslash. User Instance - this is required if you are using SQLExpress and changes how the database is mounted. AttachDbFilename - is the database file you wish to attach. Initial Catalog - the name of the database you want to connect to. So your new connection string, assuming a database name of "cars" on a default instance on your local server using integrated security (so you don't have to store usernames and passwords). It would look like thisData Source=(local);Initial Catalog=cars; Integrated Security=SSPI;
Notice the AttachDbFilename and User Instance parameters have been removed.