Clarification on ms sql connection string

Question

0.00/5 (No votes)

See more:

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.

C#

<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.

Posted 21-Feb-16 4:04am

Jones Aaron

Updated 21-Feb-16 4:23am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2016-02-21T04:23:00

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:

HTML

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