November 6, 2007 at 12:50 pm
Last weekend I migrated a database and application from SQL2000 to SQL2005, with the SQL2005 being on a new server. I first migrated the user logins using the sp_help_revlogin procedure (Method 2 as described by Microsoft at http://support.microsoft.com/kb/246133). Then I saved the 2000 database and restored it on the 2005 server. Now, most users can access the 2005 database, but many others cannot. If I change their password, they are then able to access the database. In some circumstances, the user can then access the database OK, but later cannot. If we get them to try again, sometimes they can get in, sometimes not. Any ideas?
I'm using SQL2005 Standard Edition, SP2, 9.00.3042.00
Ken
November 6, 2007 at 12:57 pm
Hi,
You have said that you have first created login and then restored dbs. By doing above the default db for the login will be changed to some other database, because in the login script(sp_help_revlogin) it will point to the correct database but the db is not restored in the first step, hence SQL will point to tempdb or master db.
Regards..Vidhya Sagar
SQL-Articles
November 6, 2007 at 1:05 pm
One other possiblity is that SQL 2005 passwords are case sensative. In 2000 by default it was case-insensative. If your users are used to case-insensative they may be entering the passwords differently each time. That would account for them being able to log in sometimes and not others.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 6, 2007 at 1:08 pm
The default database was master on 2000, and still is on 2005. The application login takes care of connecting to the correct database. It is the application that prompts for the user ID and password.
If this was the problem, then it should affect everybody. But the problem seems to be random.
November 6, 2007 at 1:08 pm
..or - the passwords don't meet the requirements and you have the "enforce password policy" turned on....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 6, 2007 at 2:08 pm
I've checked the password case - the users are entering the password correctly.
I did have one user for which "Enforce password policy" was checked. I don't know how that happened, because there is no policy to enforce. I unchecked that option and reset the p/w to no avail. I ended having to delete the login and recreate it.
I've also had this problem occur for me. The login is rejected, but then later it is accepted, without changing anything (e.g. not resetting the p/w).
Could there be a built-in limit to the number of connections or concurrent users? I checked the server Properties Connections page, and the max number of concurrent connections is set to 0 (unlimited).
November 6, 2007 at 10:27 pm
Have you audited your SQL Profiler or a server side trace looking for Audit Failures?
K. Brian Kelley
@kbriankelley
November 7, 2007 at 9:54 am
I'm not sure what that means. However, I have looked at the system event logs and do see Failure Audit entries in the Application log for the people reporting the connection failure problem. I had at least one user who yesterday was using the application successfully, then tried to open another window in the application, and got the error. Today she can connect without a problem. There has been no change made to her security settings, password, etc. The problem seems to come and go at random.
November 7, 2007 at 10:07 am
Is it possible there are network errors?
A couple of tests you could run:
Once someone who used to be able to log in can no longer log in try opening a query window and logging in as them directly (that avoids any network issues).
If they can log in there but not through ODBC or some other network related connection it may be the network itself.
If they can't log in directly through a query window but other people can log in it may be that you have a limit set to the number of connections each person can have (no idea if this can be done or not). If so you could run sp_who2 and see how often they are connected. Try having them log out (or kill) one connection then try connecting again.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 7, 2007 at 5:34 pm
Ken LaRoy (11/7/2007)
I'm not sure what that means. However, I have looked at the system event logs and do see Failure Audit entries in the Application log for the people reporting the connection failure problem.
If you were using Windows logins I would suspect an issue with one of the DCs, but you're talking SQL Server based logins. If it was one of two failures you could rule it out as a user fat-fingering the password, but it is too many failures for that. Is the server showing signs of being under too much stress or anything of that sort based on any errors in the SQL Server error log or in the applicaton event log?
K. Brian Kelley
@kbriankelley
November 8, 2007 at 8:42 am
Hi,
You might also want to take a look in Books Online for the system stored procedure sp_change_users_login. I think what may have happened is that the database user id is no longer mapped properly to the server login id.
Julie
November 8, 2007 at 10:17 am
Brian,
The SS log shows nothing other than these login failures (and normal activity such as scheduled jobs starting and finishing). This is on a new server which is "heftier" than the one that I migrated the SQL2000 database from, and even it was never stressed, so I'm sure this one is not being stressed. I know there are some examples of "fat fingers" and users not being aware that the password is now case-sensitive, but that explains only some of the problems. There are definitely many cases where a logon is accepted, then later rejected, then later accepted, using the same password (this has been carefully checked!).
Julie - isn't this what the sp_help_revlogins procedure for migrating the logins supposed to take care of? If it didn't, I would expect that nobody would have been able to log in, but this problem is not affecting everybody. I did have 2 examples where I created the login on the 2000 server and the 2005 server after the sp_help_revlogins migration and before the database migration, and I had to fix these on the new server after the database migration, as expected. I think this is what you are suggesting has happened to everybody. If this is in fact what is causing the problem, then I think I could lay the blame at the feet of Microsoft's sp_help_revlogins procedure for being inconsistent (flaky).
November 8, 2007 at 10:53 am
Ken,
I am not 100% positive on this because I haven't used the sp_help_revlogins procedure myself, but I think what that does is script the server logins that exist in the master database. I'm not sure if it resolves any differences in sid values between the server login and the corresponding database user. In other words, the users from the databases you restored are "orphaned". They are not associated with any server logins.
Can you humor me? 🙂 Open up a query window in Management Studio and change the database context to one of the databases you restored and are having trouble with. Execute sp_change_users_login 'report'
If any rows are returned, the user listed is "orphaned" and you will need to execute sp_change_users_login using different parameters to re-associate the database user with the server login.
Hope that helps,
Julie
November 8, 2007 at 11:24 am
Julie,
sp_change_users_login 'report' returned no rows (no orphaned users).
My understanding of the sp_help_revlogin process is that it creates logins on the target server with the same SID and password as on the source server, so that when the database is later saved from the source server and restored to the target server, all the logins match up correctly, leaving no orphans.
Since this report was empty, it seems that this process worked correctly. The remaining issue is why does SS accept a login, then later reject it, then later still accept it again, when the password is specified correctly every time? Aarrrgh!
November 8, 2007 at 12:01 pm
Were all the databases that existed on the old system migrated to the new system?
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply