澄清ms sql连接字符串 [英] Clarification on ms sql connection string

问题描述

我正在使用Visual Studio 2013使用MS SQL Server 2012后端编写我的Web应用程序。下面是我的连接字符串，它工作正常。

< connectionStrings> 
< add 
 name =   UserDetails 
 connectionString =  数据源=（LocalDB）\ MSSQLLocalDB; AttachDbFilename = | DataDirectory | \ UserDetails.mdf; Integrated Security = True ;连接超时= 30; 
 providerName =   System.Data.SqlClient /> 
 < /   connectionStrings  >  
 

但是在部署为Web部署包时，我可以生成.zip和deploy.cmd以及setparameters.xml /SourceManifest.xml文件生成成功。在deploy-readme.txt的指导下，我执行了以下命令，该命令成功。



App.deploy.cmd / T / L -allowUntrusted / M：http： // localhost / MSDeployAgentService



我试图用/ Y替换/ T，这里是我收到错误的地方。



错误代码：ERROR_EXCEPTION_WHILE_CREATING_OBJECT

更多信息：无法创建类型'Microsot.SqlServer.Dac.DacServices'。有关详细信息，请访问：http：//go.microsoft.com/fwlink/？LinkId = 221672 #ERROR_EXCEPTION_WHILE_CREATING_OBJECT。

错误：调用目标引发了异常。 ---> System.Exception：不支持连接字符串参数AttachDBFilename



在MSDN中提出了AttachDBFilename。我完全不明白这个错误的原因。


我尝试了什么：



我尝试在发布时不包含数据库，上面的命令成功，我能够启动托管的应用程序。但是，SQL连接稍后会失败。所以，我可以弄清楚我的连接字符串有问题，但我被困在这里一个星期。你的建议真有帮助，如果我遗漏了任何东西，请帮助我。

解决方案

首先，对于生产，你不应该使用 （localDB） - 您应该使用您的托管服务提供商为您设置的SQL服务器实例的实际名称。

其次，附加数据库是一个坏的在Web环境中的想法 - 你在SQL服务器实例中创建数据库并直接引用它要好得多。

见这里：坏习惯：使用AttachDBFileName - SQL Sentry [ ^ ]

第三，生产永远不应该使用集成安全性 - 您应该为用户配置具有足够权限来执行应用程序任务和没有了，而是用它来连接。



例如，我的连接字符串（显然有一些安全性更改）是：

 <   connectionStrings  >  
 <   add     name   =  MembershipDatabase    connectionString   = 数据源= www.palm.MyHostingService.com;初始目录= MyMembershipDatabase ; Persist Security Info = True;用户ID = SMUserName;密码= SMuserPassword  /  >  
 <   / connectionStrings  >  

I am using Visual studio 2013 to write my web app using MS SQL Server 2012 backend. Below is my connection string which works fine.

<connectionStrings>
  <add
    name="UserDetails"
    connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\UserDetails.mdf;Integrated Security=True;Connection Timeout=30;"
    providerName="System.Data.SqlClient"/>
</connectionStrings>

But while deploying as Web deploy package, I could generate .zip and deploy.cmd and setparameters.xml/SourceManifest.xml file gets generated successfully. As guided in deploy-readme.txt, I executed the below command which was successful.

App.deploy.cmd /T /L -allowUntrusted /M:http://localhost/MSDeployAgentService

I tried to replace /T with /Y and here is where I get the error as below.

Error Code: ERROR_EXCEPTION_WHILE_CREATING_OBJECT
More Information: Failed to create type 'Microsot.SqlServer.Dac.DacServices'. Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_EXCEPTION_WHILE_CREATING_OBJECT.
Error: Exception has been thrown by the target of an invocation. ---> System.Exception: The connection string argument AttachDBFilename is not supported

AttachDBFilename was proposed in MSDN. I exactly don't understand the reason for this error.

What I have tried:

I tried not to include the database while publishing and the above command succeeded and I was able to launch the application hosted. However, SQL connection fails at later point. So, I could figure out that I have got some issue with my connection string but I am stuck here for a week. You suggestions will be really helpful and please assist me if I have missed anything.

解决方案

First off, for production you shouldn't be using (localDB) - you shoudl be using the actual name of the SQL server instance that your hosting provider has set up for you.
And secondly, attaching a DB is a bad idea in a web environment - you are much, much better off creating the DB in the SQL server instance and referring to that directly.
See here: Bad habits : Using AttachDBFileName - SQL Sentry[^]
Thirdly, production should never use integrated security - you should configure a user with exactly sufficient rights to do you application task and no more, and use that to connect instead.

For example, my connection string (with some security changes, obviously) is:

<connectionStrings>
  <add name="MembershipDatabase" connectionString="Data Source=www.palm.MyHostingService.com;Initial Catalog=MyMembershipDatabase;Persist Security Info=True;User ID=SMUserName;Password=SMuserPassword"/>
</connectionStrings>

这篇关于澄清ms sql连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！