January 15, 2008 at 3:16 pm
I am developing a web site with asp.net 2.0 and c#, using Visual Studio 2005 and Microsoft SQL Server 2005 ( I am still learning about these technologies and languages ).
I transferred my web site files to a new computer.
I created a empty SQL database called “SiteData.mdf”, I stopped the SQL server and then I replaced this database with a previous one with the same name and already populated with data.
I used this maneuver in order to avoid having to insert all data again.
The web site works fine when running inside Visual Studio, however when I try to run it in its published format it gives this error message:
~~~~~~~~~~
Server Error in '/MA' Application.
Cannot open database "SiteData" requested by the login. The login failed.
Login failed for user 'CENTAURUS\ASPNET'.
~~~~~~~~~~~
I also observed that inside the SQL Server Management Studio, in the database properties, it shows “ETHER\ASPNET” as User or Roles ( however “ETHER\ASPNET” was created using the old computer’s name [ or should I say domain ? ]) but the permissions list correctly displays the new name “CENTAURUS\ASPNET”.
I think that I, probably, should create a new user using the name “CENTAURUS\ASPNET” because it would use the name of the new computer ( or domain ) and then I should delete the old user name. Actually I tried to do that but I get the message “An object named ‘CENTAURUS\ASPNET’ cannot be found”.
So,
1) What should I do to get the database being connected with the published web site ( note: I am publishing it in my own computer for awhile ) ?
2) How do I create the new user name “CENTAURUS\ASPNET” ?
3) I also would like to know why the name of the database in the Server Explorer panel of VS appears as “centaurus.SiteData.dbo” –while- inside the Object Explorer panel of the SQL Server Management Studio it appears simply as “SiteData” ?
January 15, 2008 at 8:35 pm
1. You should change security settings on IIS server. Right click on your website, go to properties and then Directory security. Edit and define windows user in Anonymous access, check the Integrated windows authentication option. This windows user should have access to the Db Server
2. The "ASPNET" user account is created in Windows XP by Microsoft .NET Framework. It is an automatic machine account created to limit access rights of .NET applications. You can change security context to any valid windows account.
3. centaurus.SiteData.dbo - Centaurus is your machine name, SiteData is databasename.
January 16, 2008 at 3:21 am
The process you used to transfer the data is not proper. The current stature can be corrected, but may fail at any point. So, please follow one of the following processes in future.
1> Detach the database in the actual location.
2> Copy the .mdf and .ldf files to the new server
3> Attach the .mdf file with the same name or a new name
yuor new server is ready, with everything intact.
Second process...
1>Take a full backup of your database into a .bak file
2> Copy the .bak file to the destination server.
3> Restore the database to an existing database or a new database
Make sure the path for the file name exists, or else you will receive an error.
In the second process, the IDs will change, but in the first process, it is indeed an exact replica of the parent.
Other conventional processes are there which are time taking but still effective -
1> Using copy database wizard
Now, let us fix the current situation--
first check if the user exists in the security, as this might be the case of an orphan user.....use the following script
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1
AND sid is not null
AND sid <> 0x0
AND suser_sname(sid) is null
ORDER BY name
if you find out that your user is orphan......use the following script
EXEC sp_change_users_login 'update_one', 'username', 'username'
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
January 16, 2008 at 3:37 am
The situation of orphaned user will or may occur in the prescribed first two options but will not cur if you specify to copy users while copying the database using copy database wizard. It entirely depends what is of priority to you.
well...if the is not solved with orphaned user resolution or if there are no orphaned user,
try logging in to the instance using the credentials through microsoft sql server management studio....if you cannot.....check ur password, or reset it....if you are logged in check whether you have proper access rights on the database for the user....if ok.....there is no problem with the database instance.....
you have to check your connection string......
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
January 17, 2008 at 1:15 pm
Thank you, chandrachurhghosh.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply