Cannot connect to SQL server 2008 Express instance

Question

Hello,

i have an instance of SQL server 2008 and an instance of SQL server 2008 Express.

i have been mainly using the SQL server 2008 instance with the following connection string...

<add name="Local-DB-connection" connectionString="Data Source=JONATHAN-PC; Database=dbname; User ID=uname; Password=pword;" providerName="System.Data.SqlClient" />

this works fine.

now on the instance of express, i have a user with the same name and a identical database, but no matter what i always get this error

Cannot open database "dbName" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

now i have added - Trusted_Connection=True on the express connection string, hence the NT AUTHORITY\NETWORK SERVICE

i have found a forum post which i have used when fixing a similar issues on my live server

http://blog.sqlauthority.com/2009/08/20/sql-server-fix-error-cannot-open-database-requested-by-the-login-the-login-failed-login-failed-for-user-nt-authoritynetwork-service/

it basically talks about adding the NT AUTHORITY\NETWORK SERVICE’ user, but this user is not in the list to add.

im going nuts here, and im suspecting its to do with my two SQL server instances conflicting

can anyone shed some light on this ??

thanks

truegilly

Answer 1

Just to check, you're trying to access the SQL Express database with the correct instance name, i.e. JONATHAN-PC\SQLEXPRESS or similar?

NETWORK SERVICE should be in the list. If you go to

• Security, Logins, New Login, Search, Advanced
• Select your PC in the Locations box (should be default unless you're on a domain)
• Find now

then it should be in the list as NETWORK SERVICE. If you select that it'll then appear in NT AUTHORITY.

Once you've got it in your list you'll need to grant it access to your database. Your link suggests setting it as db_owner - that's dangerous for production, you should cut its permissions down to a more restricted set, but for development and proof-it's-working that's probably OK.