November 8, 2007 at 12:23 pm
Sort of. The old system had 4 databases - Live, Training, Test and Backup. The latter 3 are refreshed periodically from the Live database, so are essentially the same, and have all the same users.
To prepare for the Live migration, I set up a new server with SS2005, ran the sp_help_revlogin process to create the logins on SS2005, then backed up Live from SS2000 and restored it as Live and also as Test on SS2005. I then did all my 2005 testing using the Test database. At the final Live upgrade time, I did a further backup of Live on SS2000, and restored it as both Live and Training on SS2005 (leaving the 2005 Test version intact). The 2000 Backup database was not migrated and has since been deleted. I also have Lumigent installed on the 2000 system, but that database was not migrated nor installed on the 2005 system (yet). The system databases were not migrated (of course).
November 10, 2007 at 10:12 am
What I was thinking of is if there was a database that was being used actively on the old system but hadn't been migrated to the new system. In those cases, if the database connection specifies said unavailable database, the connection fails. However, this doesn't sound like the case. This is a head scratcher. Are the clients using the SQL Native Client to connect to the database? If not, you could try running packet traces to investigate if there is anything different about the login packets as they go back and forth with the SQL Server.
K. Brian Kelley
@kbriankelley
November 12, 2007 at 11:27 am
You're right - that isn't the case, and it is a head scratcher. The database connection is made through 3rd-party software, which prompts for the user ID and password. It even remembers the password during a windows session, so that the user doesn't have to re-enter it for each program access. This is how we know that the same password is accepted and then later rejected, then yet later accepted, because the user is not re-entering the password and (possibly) keying it incorrectly.
About packet tracing - I don't know anything about how to do this. I'll look into BOL to see if I can learn anything there. Thanks for the suggestion.
November 12, 2007 at 12:19 pm
Is there a way for you to log exactly what the application is sending? From your description in your last post I'd suspect that the application is somehow botching what it sends to SQL Server. I'd even think of trying a direct connect / login from the client machines.
Back before 2K5 and it's fancy fix users sproc, I had a home-grown version that would go something like this:
SELECT *
FROM sysusers
WHERE name = 'ajames'
sp_addlogin @loginame = 'nancyd', @sid = 0xC461827655C8BE47B9072951D641437B
sp_dropalias 'nancyd'
sp_dropuser 'ajames'
sp_droplogin 'nancyd'
--(where ajames was the troubled user and nancyd was a fake)
Also take a look at:
select * from sys.database_principals
November 12, 2007 at 6:58 pm
Packet tracing won't be found in Books Online. if you have a network engineer or a server admin who has done network traces before, have them look at the traffic from a particular system. They'll also want to understand the protocol SQL Server uses, which is Tabular Data Stream (TDS). Information on TDS can be found here: FreeTDS.org[/url].
K. Brian Kelley
@kbriankelley
November 13, 2007 at 8:19 am
Ken,
Are you using a DNS entry from the App to SQL Server? Are you using same DNS entry as previous db server used (for minimal impact to users you've updated an entry) - I'm thinking it could be an issue with your Active Directory replication. Can you actually see logs of the failed logins on the SQL Server? - If not then they are not getting to db server and that is what is causing the failure not a bad password.
November 13, 2007 at 10:02 am
Yes, DNS is involved, since the app uses the server name, not IP address. The old and new servers have different names and addresses.
Yes, I can see the failed login messages in the SS log, so they're not going to the wrong server.
The ongoing problem is that some users can log in, then later can't, then later can, using the same password (see my post # 421154 for explanation of how I know the same password is being used)
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply