Connection string

[abthomas]

Hello,

I'm having trouble connecting to my database. I'm using SQL Server Management
Studio 2008, Visual Web Developer 2008 & running them all as administrator.

I created a database with VWD and tried this:
conn = new SqlConnection("Server=localhost\\SqlExpress;Database=TestDB;" +
"Integrated Security=True");
It fails with this error (on conn.open())
Cannot open database "TestDB" requested by the login. The login failed.
Login failed for user 'Ash-PC\Ash'.

So I went into SQL MS & the database wasnt there,
so I attached it (as TestDB). I went into security, users and saw the dbo username
with a login name of 'Ash-PC\Ash' and a default schema of dbo. I now get this error:
Cannot open database "TestDB" requested by the login. The login failed.
Login failed for user 'Ash-PC\Ash'.

I changed the connection string to:
conn = new SqlConnection("Server=localhost\\SqlExpress;Database=D:\\Files\\Work\\Asp.Net\\Test\\App_Data\\TestDB.mdf;" +
"Integrated Security=True");
And that fails with:
Cannot open database "D:\Files\Work\Asp.Net\Test\App_Data\TestDB.mdf"
requested by the login. The login failed. Login failed for user 'Ash-PC\Ash'.

I then wondered if I should have used SMS to create it, so I created a new database
in SMS called 'TestDB2'. I then did 'add existing item' in VWD and found the database
and added it which all worked with connection string 'Database=TestDB2;'.

I noticed that it didnt add it, it added a copy of it - there now appears to be two
databases with different data in them. So VWD just takes a copy! So any editing in
VWD does nothing as you're editing the copy of the database in the App_Data folder,
not the one that it connects to.
I also have to reboot to 'add the existing item', even with SMS closed as I get 'The file can not
be opened because it is being used by another process' which is very annoying.

(I want to stick to windows authentication as that's the route my book is going down)

So my questions:
1. Why do the logins fail?
2. How do you connect to a database created in VWD in your App_Data folder?
3. Why does VWD copy the database? I know I don't want to go down this route, preferring
to stick to databases in the App_Data folder, but I'm curious.

Thanks!

[dglienna]

Google is your friend!

http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/

[abthomas]

Google is not my friend. I've done dozens of google searches & it never showed that page.

As I said, I want to stick to windows authentication as that's the route my book is going down. This solution makes me change that. However, I followed it despite this - but it answered none of my questions nor fixed any of my problems sadly enough.

I still cannot connect to the database in my App_Data folder with the same error. I've tried all the steps & possibilities.

I will now undo the steps in the page you suggested.

Can anyone else help?

[dglienna]

Here's an old connection string that used Windows Authentication.

Data Source=***;Initial Catalog=Depts;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;

Connected to SQL SERVER 2005. I used the same string (almost) to test the app in SQL Express on my laptop (from app.config)

connectionString="Data Source=ASUSVISTA\sqlexpress;Initial Catalog=Depts;Integrated Security=True"
providerName="System.Data.SqlClient" />