0

I'm having hard time deploying my website from my local machine to my VPS, here is the problem, I'm using AttachDBFileName in my web config in my local project and this is the ONLY connection string I have in my web application

<connectionStrings>
<add name="myConnectionString" 
  connectionString="Data Source=.\SQLEXPRESS; 
    AttachDBFileName=|DataDirectory|\AssignmentDB.mdf; 
    Integrated Security=True; User Instance=True; 
    Database=AssignmentDB.mdf" providerName="System.Data.SqlClient" />

This connection string lies in the web.config at the root of my web app.

And I deploy this by hand every time because I dont know how to use msdeploy, And here is what I've tried so far:

  • 1- changing the connection string gizillion times and no luck..
  • 2- attaching the db to my sql express on the VPS using SQL management studio and giving permissions to all logins.
  • 3- restarting the website in iis after each change.

Every time I run the website and try to use any feature that requires a connection to the DB I get error 500.

The thing is, I've googled this problem and no one seems to suggest any clear configuration for all 3 applications involved to run this database.

Update:

this is the connection string I tried after attaching the DB to the sql server.

<add name="myConnectionString" connectionString="Data Source=.\SQLEXPRESS; 
    Initial Catalog=AssignmentDB; Integrated Security=True" 
    providerName="System.Data.SqlClient" />

I'm not getting any specific error message, it only redirects me to the error 500 page and that's all.

Update - 2

Just to clear it for future users, the default AppPool had its identity set to DefaultAppPoolIdentity, I changed that to localSystem and it gained access to my DB :)

ymg
  • 1,500
  • 2
  • 22
  • 39
  • 1
    Edit your question to include the connection strings you have used, and the location of it within your project. – p.campbell Mar 04 '12 at 22:01
  • Also please specify the error message. You need to disable friendly error messages and determine what the real cause of the error is (500 is a generic catch all and is absolutely useless). – Aaron Bertrand Mar 04 '12 at 22:19
  • I'm not getting any specific error, it redirects me to the custom error 500 page I have and that's it – ymg Mar 04 '12 at 22:39
  • If you can't figure out how to disable friendly error messages then you'll need to debug within the application. If you can't get the real error message then you may as well just tell us "it's broke" because that's about as specific as 500 gets. Which in turn means we can't give you any further advice how to fix it. – Aaron Bertrand Mar 04 '12 at 22:41
  • Also have you set your IIS application pool to run as you? If it's running as a built-in Windows account, it doesn't magically get access to your database unless you are also forcing Windows auth at the web server layer. You need to set up permissions correctly in order to connect. Without specifics on the error message, that's about my best guess at this point. – Aaron Bertrand Mar 04 '12 at 22:42
  • Apologies, but I totally forgot to disable it :S here is a picture of the error I'm getting now http://i.imgur.com/MMMqn.png – ymg Mar 04 '12 at 22:48

1 Answers1

3

When you deploy to your host, you should no longer be using AttachDbFileName, this is for local development. On your web server there should be one and only one copy of the database, and you should be referencing the database name by Initial Catalog and not the physical path to an MDF file. Also User Instance should not be true.

The connection string should be starting out with:

<add name="myConnectionString" 
  connectionString="Data Source=.\SQLEXPRESS;
  Initial Catalog=AssignmentDB;
  providerName="System.Data.SqlClient" />

Then you will either need to add User ID and Password parameters, or use the Integrated Security = True that you already had. In a hosting environment it is most likely that you've been given SQL auth credentials, not Windows, but I could be wrong. You may also need to specify a server name other than .\SQLEXPRESS since in a hosted environment you're not always dealing with a named instance, it's not always Express Edition, and it's usually not on the same machine as the web server.

Since you are using Windows Auth, please make sure that IIS is capable of connecting to SQL Server. You can do this by specifying an identity for an application pool / web site or by configuring Windows Authentication.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • I've tried attaching the db to the server and then using the initial catalog, I'm not sure if that's what you were referring to? – ymg Mar 04 '12 at 22:12
  • Yes, if you attach the DB to the server (which is what you should be doing), then you use Initial Catalog to identify the database. Does your database have a name? Did your connection string reference it? Can you show the various connection strings you have tried, the error messages you've received, and perhaps a screen shot of the server/database in Object Explorer in Management Studio (obviously blurring out any secret info). – Aaron Bertrand Mar 04 '12 at 22:16
  • I just tried and the same error came up, here is a pic of my management studio – ymg Mar 04 '12 at 22:24
  • And the web server is on the same machine as SQL Server? Are you connecting in Management Studio using a username and password, or as your Windows account? – Aaron Bertrand Mar 04 '12 at 22:27
  • yes its on the same machine, and I'm using my windows account for the management studio (not using any id/pass) – ymg Mar 04 '12 at 22:29
  • Ok, so please add the connection string you've tried, without the user instance and attachdbfilename nonsense, to your question. Also see http://stackoverflow.com/questions/360141/how-to-connect-to-local-instance-of-sql-server-2008-express for general Express connectivity issues. – Aaron Bertrand Mar 04 '12 at 22:30
  • I fixed it ty sooooooo much mr bertrand much love <3 – ymg Mar 04 '12 at 23:01