Problems connecting to Database

Question

Problems connecting to Database

horace-244264

Old Hand

Points: 326
More actions
January 21, 2008 at 9:08 am

#123392

Hi,
We are developing a home-grown Project . We have created a Database using SqlServer 2005 Express. We have a web application that connects to the database. Database and Web application are on the same server.
In the web.config file we have a connection string of
name="MidasConnectionString" connectionString="Data Source=DSVR005555\SQLEXPRESS;Initial Catalog=Midas;Persist Security Info=True;User ID=sa;Password=zzzzzz"
This connection works fine. I have created a new login 'serviceaccount' on the database, which has restricted permissions on the database tables. Trouble is, I get the following error when I try to connect to the database
Cannot open database "Midas" requested by the login. The login failed.
Login failed for user 'serviceaccount'.
I have modified the connection string....
name="MidasConnectionString" connectionString="Data Source=DSVR005555\SQLEXPRESS;Initial Catalog=Midas;Persist Security Info=True;User ID=serviceaccount;Password=xxxxxx"
I would appreciate any advice as to a solution

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

First I'd suggest you get Management Studio so you can more easily view things.

second, did you create another login or a user? A login has access to connect to the server. A user has rights to database tables. They are separate things and need to be linked together.

horace-244264 Old Hand Points: 326 More actions · Answer 2

Hi,

Progress (I think!)......

Attached is a screenshot of the error I'm now getting. It occurs when I try and Map the serviceaccount user to the Midas database (by ticking the checkbox and clicking OK).

I'm at a loss I must admit.

horace-244264 Old Hand Points: 326 More actions · Answer 3

Wonder if this is linked to the fact that we restored the database?

Rakshit Patel SSC Journeyman Points: 95 More actions · Answer 4

Can be a problem if you restored from other server or re-created after you took backup.

Try to find if its an Orphan

-- Find orphaned users

USE

GO

sp_change_users_login @Action='Report'

GO

-- Fix orphaned user

USE

GO

sp_change_users_login @Action='update_one', @UserNamePattern=' '

GO

-- Rakshit Patel

horace-244264 Old Hand Points: 326 More actions · Answer 5

Yup. Got to the bottom of it. It was orphaned users caused by backing up the Development database, and restoring it onto the 'Live' server.

Fix was....

sp_change_users_login @Action='update_one', @UserNamePattern='serviceaccount', @LoginName='serviceaccount';

Many thanks to all for their input