Migrated Servers, Now Windows Authentication Fails
-
A new one on me. We bought a new database server for use as a production box, and I spent last weekend doing the migration. Since then, members of our Windows 'Developers' domain group have been unable to connect to the server, even though the group is defined as a login and is linked as a user to the necessary databases, with all the appropriate permissions.
We have a work-around, but not one that I enjoy: We added the developers temporarily to the Windows 'Admin' domain group, and that solves their problem for the time being, even if it exacerbates mine (!). For some reason, the server recognizes members of the 'Admin' group, but not the 'Developer' group. Other than that, the conversion seems to have gone fine; we're running on our third work day here, with no other issues surfacing.
In our shop, we have strict borders between DBA and system/network admin tasks, therefore much of the conversion, frankly, was out of my control. The effort took a left turn right out of the box, and it took some doing to put it back on track. Here's how things went...
Before the conversion:
1. The system guys attached the server to our network domain, giving it a different name than the box it was to replace. Let's call the original box 'SQLBOX' and the new box 'SQLBOX_NEW'.
2. I installed SQL Server 2000 SP3 on the new box. We use mixed mode security here -- Windows and SQL Server authentication both need to work.
During the conversion:
1. We brought SQL down and I immediately ran database backups of everything. Then, I detached all the databases and ran an additional backup of 'master'.
2. The plan was then for the system guys to physically transplant the data drive from SQLBOX to SQLBOX_NEW, and then I would restore 'master'. The folder structure being the same, 'master' should then have seen the databases. We never got that far. The system guys moved the drive, but somehow during the hookup all my data got lost. DOHH!!!!!!!
3. Well, that's why we make backups, isn't it? I restored the 'detached databases' version of 'master' to the new server, and then created/restored the rest of the databases, system databases first and then the user databases.
4. When I was done, the system guys renamed the old server to 'SQLBOX_OLD' and then the new server to 'SQLBOX' and gave it the old server's former IP address. From this point, applications should transparently see the new server. And for the most part, this is how things have gone: mostly, success.
After the conversion:
1. A minor issue: Because I did not restore the databases in the same order that they were originally defined on the old server, the default database assigned to most of the logins had changed, based on the old mapping of the 'sysxlogins.dbid' values. To fix this, I created a new database named 'oldmaster' from the first backup I'd made of 'master' (in which all the databases were still attached), and finally ran an update of the 'sysxlogins' table so each row would reflect the correct 'dbid'.
2. Then, as we know, the Windows 'developers' groups lost connectivity. Here's the error message:
"Server: Msg 18452, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user '(null)'. Reason: not associated with a trusted SQL Server connection."
Theory:
I'm still working on this. Unless otherwise reliably informed, I'm going to proceed on the assumption that the problem may have occurred because SQL Server 2000 was installed on the new box before it was renamed and re-IPed. Maybe there is something, somewhere, within the DBMS or the Registry that caches the name of the server upon installation, and uses that cache to identify Windows groups. And if so, that might perhaps explain the confusion. I admit it's reaching, I just don't have any better ideas. Does anyone else? Tonight, I plan on detaching the databases, re-installing SQL Server 2000, and then re-attaching the databases -- unless I get better advice.
If you read through all of this, thanks!
Edited by - Lee Dise on 07/30/2003 10:41:43 AM
-
First, run sp_helpserver to see whether you still have server name 'SQLBOX_NEW'. After you rename machine name, you have to run following commands.
sp_dropserver 'SQLBOX_NEW'
go
sp_addserver 'SQLBOX', Local
go
-
quote:
First, run sp_helpserver to see whether you still have server name 'SQLBOX_NEW'....I ran it, and it is showing the new -- that is, the correct -- server name, with an id of 0 and a status of 'rpc,rpc out'.
Edited by - Lee Dise on 07/30/2003 12:30:42 PM
-
The correct server name should be your machine name (SQLBOX) from sp_helpserver.
-
quote:
The correct server name should be your machine name (SQLBOX) from sp_helpserver.That's what it shows.
Edited by - Lee Dise on 07/30/2003 1:01:06 PM
-
quote:
. A minor issue: Because I did not restore the databases in the same order that they were originally defined on the old server, the default database assigned to most of the logins had changed, based on the old mapping of the 'sysxlogins.dbid' values. To fix this, I created a new database named 'oldmaster' from the first backup I'd made of 'master' (in which all the databases were still attached), and finally ran an update of the 'sysxlogins' table so each row would reflect the correct 'dbid'.I am not sure this is the problem but it is definitely not good practice.
Try EXEC sp_change_users_login 'Report' to lists the users and corresponding security identifiers (SID) in each databases that are not linked to any login. Remove the developers group from admin group before you run it.
-
quote:
I am not sure this is the problem but it is definitely not good practice.I doubt that this is the problem. For good measure, I had already tried deleting and re-adding the developers group from the server logins, so any previous mappings would be gone anyway.
quote:
Try EXEC sp_change_users_login 'Report' to lists the users and corresponding security identifiers (SID) in each databases that are not linked to any login. Remove the developers group from admin group before you run it.I'm not seeing any orphaned users.
Edited by - Lee Dise on 07/30/2003 1:26:28 PM
-
Have you tried to add one of developer's NT login to your SQL Server and grant proper permissions to see whether it works?
-
quote:
Have you tried to add one of developer's NT login to your SQL Server and grant proper permissions to see whether it works?Yes. I've also checked the SID values of the developer's group with the SID values showing for that group on the other servers, where the authentication still works. It's the same value. It strikes me that the problem seems like it is not inside of SQL Server, but outside on the server itself, or in the handshake that occurs between the server and the DBMS. If I knew for sure, of course, I would not be making this appeal to the readers of SQL Server Central.
-
Regarding windows-authentication, check if the 'Developer'-group is added to the local\groups\guests or users of your sqlbox (or a group they are member of).
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
quote:
Regarding windows-authentication, check if the 'Developer'-group is added to the local\groups\guests or users of your sqlbox (or a group they are member of).Since yesterday, I've learned that the problem does not just exist for members of our development group, but for any non-admin user or group.
I needed a way to test, so I had one of my own logins stripped of admin privileges, so I could "feel the pain" of the developers, so to speak.
Then, just to test basic system permissions, we set up a share on the server upon which permissions were granted only to my own user id. Then, logged in as that user on my own workstation, I tried to map a drive letter to that share. Lo! It failed, even though my user id is a domain user, and the server and my workstation are both on the domain. There seems to be something deeper here than a mere SQL Server installation issue.
Contributing to the body of evidence, I added my user id to the SQL Server as a Windows-autheticated user, with database admin privs. Then, I tried connecting through 'isql' via trusted connection ('isql -E') and got the following message:
quote:
Msg 18452, Level 14, State 1:Login failed for user 'ldise'. Reason: Not associated with a trusted SQL
Server connection.
DB-Library: Login incorrect.
User 'ldise' is me. Question: Where is the domain prefix? There has to be an earth-shattering domain prefix! The server refuses to see me as a member of the domain, and I think that's the crux of the issue.
(I still don't have an explanation of why members of the domain admins group can log in, but it certainly refuses anyone who is not an admin. I think I need a Windows guru or a network admin shaman.)
Edited by - Lee Dise on 07/31/2003 06:47:32 AM
-
Did you check the sqlserver-server's local (nt/w2k)-group members for guests and/or users to contain the needed domain-usergroups ? (e.g. yourdomain\domain users)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
quote:
Did you check the sqlserver-server's local (nt/w2k)-group members for guests and/or users to contain the needed domain-usergroups ? (e.g. yourdomain\domain users)We resolved the issue. The suggestion above was on the right track; however, in my organization, server security is not in my bailiwick, but rather in the system and network admin's. It's hard to debug by proxy.
The net admin guys had changed some of the security settings on the new box from what they were on the old box. I can't really get more specific than that, but it resulted in our connection problems for non-admin users. I doubted from the beginning that this was a problem in our SQL Server installation, and turns out now that I was right about that.
Thanks to everyone for their suggestions and willingness to help!
Edited by - Lee Dise on 08/01/2003 07:15:08 AM
-
This is "nice to know"-stuff
that helps to get you good credibility. But because it all gets more integrated, the "nice to know" tends to be changing in "need to know" when you're in troubles.Glad we could help.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
that helps to get you good credibility. But because it all gets more integrated, the "nice to know" tends to be changing in "need to know" when you're in troubles.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply