Publish ASP.NET project with .mdf database

2018-06-20 03:51:39

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 redirectedUserInstan ce, 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, DbConne ctionPoolKey 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, TaskCompletionSource 1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource 1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory , TaskCompletionSource 1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource 1 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

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

using SQL management studio image

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=SQLServerNameorIPAddressofYourHostProviderSQLServerInstanceNameofYourHostProvider;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/MSSQL2008R2stackExpress;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..

找到类似的问题http://www.codeproject.com/Questions/677651/How-to-publish-mdf-database-file-into-a-asp-net-我们有一个解决方案，希望它有帮助。

链接地址: http://www.djcxy.com/p/56708.html

上一篇: C＃Windows服务SQL Server连接问题

下一篇: 使用.mdf数据库发布ASP.NET项目