1

I am working on asp.net membership website. I created the membership provider automatically, and now its stored in sql express database within the solution. I want to move all the tables to sql database instead of sql express. Last night I attached the sql express database to the sql server management studio, and I was able to see all the objects. Then, I run my solution in visual studio, and I got error: LOGIN FAILED for the sql express database, and the solution wasn't running. I have two questions. 1. Why is that happening? 2. If I create the same database in sql server with the same objects, and then point to the sql server database instead of sql express database, will I have problems with the membershop provider? Thanks in advance.

Laziale
  • 7,965
  • 46
  • 146
  • 262
  • Are you getting LOGIN FAILED when connecting to the new database or when the user is logging into your site – JeffO Dec 14 '10 at 21:01
  • I am getting login failed when I am running the asp.net application and I am trying to open connection to the database. – Laziale Dec 14 '10 at 21:57

2 Answers2

1

It sounds like you've moved a database from one host (running SQL Server Express) to another host (running a non-Express SKU). Is this correct?

Ensure that your membership connection string is pointing to the new location of the membership (the non-Express SQL Server). Ensure the logins and users are setup properly in the new SQL Server instance, as expected.

p.campbell
  • 98,673
  • 67
  • 256
  • 322
  • does attaching to the sql server management studio means moving? Because I just attached to create few more tables and SP. – Laziale Dec 14 '10 at 21:09
  • Could you clarify exactly what steps you took to "attach"? – Greg Dec 14 '10 at 21:13
  • I opened SSMS right click on the database folder -> attach database, and I selected the database from the app_data folder in my asp.net application, then I created few tables, some stored procedures and I run the asp.net application again and I got the error message: login failed for my windows user. I am using windows authentication. All that is done locally, nothing is remotely. Thanks for your help – Laziale Dec 14 '10 at 21:16
  • 1
    @laziale - same as p.campbell said: open up your web.config file and check the connection string, see what database it's pointing too. Sounds like you moved the database but didn't updated the connection string. – Peter Dec 14 '10 at 21:33
1

Attaching a database to a new server does not automatically create the necessary logins.

run exec sp_change_users_login 'Report' from within SSMS on your database.

If it comes back with anything in the result set, you'll need to run exec sp_change_users_login 'Auto_Fix', '<username>'

where is the username from the first result set.

NotMe
  • 87,343
  • 27
  • 171
  • 245