Problems connecting to SQL 2005 DB on server

Hi - I have Web Developer, SQL Express 2005 running locally on my pc, and on my remote server.

Everything is fine locally, but when I copy my files over to my server (have setup a virtual dir, and ensured it is running .net2) I get the following message when trying to connect:

An attempt to attach an auto-named database for file C:\websites\sickness\App_Data\Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I have tried attaching and detaching the database from within SSME on the server - my connection string is:

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

I've tried it with and without the User Instance = True set

Has anyone got an actual fix for this?

Thanks, Mark

Hi - I'm sorry for bumping this, but I'm sure I can't be the only person with this problem - is there a more appropriate place I should look for an answer?

Kind regards,

Mark|||

mtait wrote:

is there a more appropriate place I should look for an answer?

Yes, I've now moved this question from the Getting Started forum to this SQL Server forum.|||I am pretty sure the problem is that you are missing the Database attribute for your connection string. I always usehttp://www.connectionstrings.com/ as a connection string reference. Drill down under SQL Server 2005 and look at the 2 SQL Server Express connection string examples. I think this will help you.|||

Hi - thank you - I was using connectionstrings.com - but each one fails.

I've now reinstalled SQL Express, and allowed Mixed Mode.

From SSME I've set the sa password to 'Bob99' and use this connection string:

<connectionStrings>

<add name="SicknessConnectionString" connectionString="Server=.\SQLExpress;Database=sickdb;User ID=sa;Password=Bob99;Trusted_Connection=False"

providerName="System.Data.SqlClient" />
</connectionStrings>

...and now the error is:

System.Data.SqlClient.SqlException: Cannot open database "sickdb" requested by the login. The login failed.
Login failed for user 'sa'.

I've checked, and rechecked the sa password - again, thanks for any help on this.

Mark

|||

Try detaching the database. You've permanently attached it, or you already have a database named 'database' on the other server. I notice that now you are calling it 'sickdb'. You could try renaming the database.mdf to sickdb.mdf as well. Then go back to your original connection string (of course you will need to change the reference from database.mdf to sickdb.mdf).

Just an FYI - your last error message is saying the userid/password is fine, but that user has no access to the 'sickdb' database. You can of course at this point go forwards and assign sa rights to the database, or you can go back and fix the original problem and not worry about permissions at all.

|||

Hi - thanks again for the help.

I think I've messed things up somewhere - I'm trying to change too many things to make this work, and I still end up with the same error messages.

If I uninstall SQL Express 2005, and do a clean install, with mixed mode authentication, is there a step by step guide anywhere which will advise how to:

a) setup a new user from within SSME
b) whether to attach the database file or not
c) which section of SSME to change to allow the user to connect to the local (database is on the same remote server as IIS) database - whether in Security/Logins or Database/Properties
d) advise a connection string which would work given the above steps#

Sorry - I know people are trying to help me, but I've just got myself completely mixed up here.

Thanks, Mark

sql