Publish ASP.NET project with .mdf database

Question

I have an ASP.NET project and it uses a local .mdf database that is stored in the App_Data folder. I normally publish the website locally to my Desktop then take all files and upload them to asp.net server.

after uploading successfully , for someone reasons accessing the database is not possible.

I have included in my web.config the connection string for the database

    <connectionStrings> 
    <clear />
      <add name="LocalSqlServer" connectionString="Data Source=(LocalDB)\v11.0;AttachDBFilename=|DataDirectory|database.mdf;database=database;Integrated Security=True;User Instance=False;Context Connection=False;" providerName="System.Data.SqlClient"/>  
      </connectionStrings>

not that I have seen couple of online solution that stated changing LocalDB)\v11.0 to .\SQLEXPRESS; in the connection string but this is not working.

Please advise on how properly uploading the database with the project and set proper access to it.

when I try to open database connection, I get the following error message

System.Security.SecurityException: Request failed. at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.CodeAccessSecurityEngine.Check(PermissionSet permSet, StackCrawlMark& stackMark) at System.Security.PermissionSet.Demand() at System.Data.LocalDBAPI.DemandLocalDBPermissions() at System.Data.LocalDBAPI.CreateLocalDBInstance(String instance) at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at Database.establishConnection() The action that failed was: Demand The type of the first permission that failed was: System.Security.PermissionSet The Zone of the assembly that failed was: MyComputer

Answer 1

why not publish your database as script Instead of .mdf it's more Stable than using .mdf !

using SQL management studio image

Answer 2

SQLExpress is a Limited SQL Server Edition (Storage capacity is 10GB after 2008R2, before R2 it is 2-4 gb as its version)

And since it is Server for the security requirements you need to set password, user, roles etc. Addition to these requirements, for ASP.NET or generally on WWW solutions so many host providers doesn't support auto-attach your db solution to their DB servers..

So when you choose to use SQLExpress in your Web Project,

either you can copy Create scripts ( create database, tables, security requirements, Stored Procedures, views and much more as your project needs ) to server side and run these scripts on server side -something like thealghabban suggested-

or create all requirements by hand from server side (providers has settings pages for these implementations )

or ask to your provider that they allow Attach or not, to decide your way

{

By Attaching from code like you already try in your connection string -with AttachDBFileName statement- you need to add to your connection string the "UserInstance" statement and set it to "true" not false!

Since "UserInstance" statement deprecated from Connection strings, There may be your host provider doesn't allow to use this statement. So, at least for this deprecation you need to ask to your provider

}

if you still want to use SQLExpress here you are an example of WWW connection string :

Server=SQLServerNameorIPAddressofYourHostProvider\SQLServerInstanceNameofYourHostProvider;Database=yourDatabaseName;User Id=yourUserName;Password=yourPassword;

If we suppose our Hosting Provider is Stackoverflow then our connection string could be so:

// there demonstrated that stackoverflow.com provider provide DB servers under publicservers subdomain and under that subdomain MSSQL2008R2 directory

Server=publicservers.stackoverflow.com/MSSQL2008R2\stackExpress;Database=myDB;User Id=theUser;
Password=WhateverPassword;

Local V11 is almost a new solution (if i'm not wrong since 4rd Quarter of 2011) which is embedded like SQLCE (.sdf extensioned database files) or as an older fashion access db or other embedded solutions but with a great improvement on its storage capacity (Access is max 2 gb, .sdf db files are max 1 gb) : It is as SQL Server Express => 10GB.

To work with Local DB, If your VS edition is not +2012 You need to copy required Dlls which you can find under your ProgramFiles directory or where you installed LocalDB in your OS. in VS2012 and 2013 Local DB installed with VS. But anyway check your project's bin file for the local copied dlls of Local Express..

And for Local DB its a runtime requirement that your target framework should be at least v 4.0.2.. check for this also..

Answer 3

Found similar question http://www.codeproject.com/Questions/677651/How-to-publish-mdf-database-file-into-a-asp-net-we it has one solution,hope it helps.

Answer 4

you can try this.

   string constr=@"DataSource=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\yourdatabase.mdf;Integrated Security=True;User Instance=True;";
   public SqlConnection myConnection = new SqlConnection(constr);

or try this

<connectionStrings>

<add name="myConnection" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\yourdatabse.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>

</connectionStrings>

i hope to help!!

Answer 5

Your current data source (LocalDB)\v11.0 is a special, isolated instance of SQLServer 2012 that is named V11.0 strangely enough. The (localDb) is not the same instance as the the instance of SqlExpresss, or any other SqlServer, on your machine. However they can both be present on the same system without conflict, even with different versions numbers of SqlServer. When you install SQLExpress, or regular SS or change any features, you have the option of enabling LocalDb. You can also download it as a separate package from MS. A problem that can occur is a version mismatch. I have seen recommendations to use (LocalDb)\MSSQLLocalDB as a universal name instead of the version number. With respect to SqlExpress the name string might be like JPLWIN7\SQLEXPRESS where JPLWIN7 is my own windows machine or 192.168.1.13\sqlexpress on a machine on your local LAN, etc.