Login problem

Hello,
I have two servers where the SQL Server DB provides the data for a Web site.
One server is the live Web server, the other one will serve as a backup.
I use an ODBC connection to conect to SQL Server, and there are a number of
asp pages that connect the SQL Server using SQL Server authentication.
Recently I restored the website DB on the backup server with a full backup
file from the live server and now the asp pages can not connect any more.
When I try to configure the ODBC connection I also get a message that
connection failed.
If I switch to Windows authentication in the ODBC manager I can connect and
the DB tests out successfully.
I tried editing the password but when I try to save it I get the message
that the userid already exists.
I also tried deleting the userid and adding it back in, but I get the same
message, that userid already exists.
I have just transferred another backup from the live server and it includes
a backup of the master, model and msdb DB's. I am hoping that restoring all
from this backup will correct the problem, but I am not an experienced DBA,
so I am not sure
Any suggestions as to how I could fix this problem would be appreciated, our
live server is acting up so we may need to switch.
RagnarSounds like you have switched the LoginMode for this Server or the password
of the user you are connecting with.
Try connecting to the server with the SQL login, if that wont work use
integrated authentication and check wheter the loginmode is WIndows or Mixed
Mode. If its mixed mode, you gotta reset your password because it doesnt
fit to that you you have. If you already tried that, there can be a
difference between the accounts that you imported. A user is created on the
server side on granted access to something on the db on the database level.
If you got a back restore on the backup server, these accounts wont fit
togehter, so oyu gotta delete the user on the db level and recreate him with
the appopiate security Script.
If you dont know how, just raise a hand (or write a mail) and ill help
you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> schrieb im Newsbeitrag
news:O5d0%23g3SFHA.2096@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have two servers where the SQL Server DB provides the data for a Web
> site.
> One server is the live Web server, the other one will serve as a backup.
> I use an ODBC connection to conect to SQL Server, and there are a number
> of asp pages that connect the SQL Server using SQL Server authentication.
> Recently I restored the website DB on the backup server with a full backup
> file from the live server and now the asp pages can not connect any more.
> When I try to configure the ODBC connection I also get a message that
> connection failed.
> If I switch to Windows authentication in the ODBC manager I can connect
> and the DB tests out successfully.
> I tried editing the password but when I try to save it I get the message
> that the userid already exists.
> I also tried deleting the userid and adding it back in, but I get the same
> message, that userid already exists.
> I have just transferred another backup from the live server and it
> includes a backup of the master, model and msdb DB's. I am hoping that
> restoring all from this backup will correct the problem, but I am not an
> experienced DBA, so I am not sure
> Any suggestions as to how I could fix this problem would be appreciated,
> our live server is acting up so we may need to switch.
> Ragnar
>|||Thank you Jens,
I fixed the problem by adding a new userid with the appropriate privileges,
but I just restored an up to date backup and now the new userid doesn't
work.
This clearly has something to do with the restore, but from my understanding
of SQL Server it should not happen, since the userids and the security data
is stored in the Master database.
This time I was able to delete and add back the userid, so I am back in
business, but I need to find out why this is happening so I can prevent it.
We will be doing regular restores in order to keep the backup server ready
to take over if the live machine fails.
I guess I could run a script to delete and add back the user after every
restore, but I would rather avoid that.
Ragnar|||You are stucked in with orphaned users, you should look here:
http://support.microsoft.com/kb/274188/en-us
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> schrieb im Newsbeitrag
news:Ow0VLwPTFHA.2768@.tk2msftngp13.phx.gbl...
> Thank you Jens,
> I fixed the problem by adding a new userid with the appropriate
> privileges, but I just restored an up to date backup and now the new
> userid doesn't work.
> This clearly has something to do with the restore, but from my
> understanding of SQL Server it should not happen, since the userids and
> the security data is stored in the Master database.
> This time I was able to delete and add back the userid, so I am back in
> business, but I need to find out why this is happening so I can prevent
> it.
> We will be doing regular restores in order to keep the backup server ready
> to take over if the live machine fails.
> I guess I could run a script to delete and add back the user after every
> restore, but I would rather avoid that.
> Ragnar
>|||Thank you Jens,
You hit the nail on the head!
I did not notice it, but the database username I was having problems with
does not have a login name, but there is a login with that name, so I
thought everything was OK.
And, sure enough, when I run the sp_change_users_login, the user can log in
to the DB again.
Of course, the acid test of the fix will come when I do the next restore.
I am puzzled how this happened though, I did not set up the DB on the backup
server, but I know it was created initially by restoring a backup from the
live server.
And the user could log in until I did a restore with recent data..
Thank you, made my day!
Ragnar|||Hello Jens,
I had a similar problem, and sp_change_users_login fixed most logins,
but for some of the logins the sp_change_users_login procedure returns
the error:
"The login already has an account under a different user name."
Isn't this exactly the situation that sp_change_users_login is supposed
to fix? What are your thoughts?
-Steve Reich
Ragnar Midtskogen wrote:
> *Thank you Jens,
> You hit the nail on the head!
> I did not notice it, but the database username I was having problems
> with
> does not have a login name, but there is a login with that name, so
> I
> thought everything was OK.
> And, sure enough, when I run the sp_change_users_login, the user can
> log in
> to the DB again.
> Of course, the acid test of the fix will come when I do the next
> restore.
> I am puzzled how this happened though, I did not set up the DB on the
> backup
> server, but I know it was created initially by restoring a backup
> from the
> live server.
> And the user could log in until I did a restore with recent data..
> Thank you, made my day!
> Ragnar *
sreich
---
Posted via http://www.codecomments.com
---