October 31, 2007 at 7:58 am
Using method 2 in the following article
http://support.microsoft.com/kb/246133
I converted my SQL 2000 logins over to my new SQL 2005 server.
After doing this I try to log in using one of my SQL Logins but it won't work. After several minutes of trying I finally realize that my password which did have an upper case letter in it is now all lower case.
Does anyone have a fix for the sp_hexadecimal stored procedure that will keep the case in my passwords?
Here is the sp_hexadecimal SP that I used. (From the KB above)
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
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]
October 31, 2007 at 11:18 am
I thought it was me who was going nuts. Obviously, I was not.
I am moving the installation from 2000 and 2005. Last week the superuser and myself tested the application with moving a couple of test logins. She said, the logins did not work. I did a lower case and it worked because I normally do lower case for these specific logins. I thought she was just mistaken. But after reading your post I connected to the current production with these 2 logins and it is a mixed case. I tested the output of sp_help_revlogin for these 2 logins on yet another 2005 and it was converted to the lower case.
Let me work on it. I know somebody else used this scripts and they had no problems, but maybe their pws were lower case.
ADDITIONALLY:
I found out that the logins that script creates do have CHECK_POLICY checked. And our domain policy does not allow all lower case. So I had to create another script that goes towards sys.server_principals to uncheck this thing
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE login_curs CURSOR FOR
SELECT name,type from master.sys.server_principals
OPEN login_curs
FETCH NEXT FROM login_curs INTO @name,@type
WHILE (@@fetch_status <> -1)
BEGIN
IF (@type ='S')
BEGIN -- SQL Server authentication
PRINT 'ALTER LOGIN ' + @name + ' WITH CHECK_POLICY = OFF,CHECK_EXPIRATION = OFF;'
END
FETCH NEXT FROM login_curs INTO @name,@type
END
CLOSE login_curs
DEALLOCATE login_curs
ADDITIONALLY # 2
It does not transfer the default database for a login
Regards,Yelena Varsha
October 31, 2007 at 12:09 pm
If you look at method 2 on the KB article (towards the bottom) you will see an SP called sp_help_revlogin_2000_to_2005. It includes both CHECK_POLICY=OFF and setting the default database.
I did notice one thing that may be my problem. In 2000 it doesn't care if I use upper or lower case. So I'm thinking that the password I'm using (its a login shared by the DBA group for testing) may have been created using all lower case.
In fact I just tested it by changing the password on my 2000 box to have the upper case letter like I expect it .. then re-created it on the 2005 box and the password works like I expect it.
Do you know if in 2000 passwords were case-insensative and in 2005 they are case-sensative?
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]
October 31, 2007 at 12:54 pm
they are case sensitive in 2000 and 2005
I just tried moving 2 test logins,both with the mixed case passwords, one moved in the mixed case and one moved in the lower case.
I am re-testing the whole thing
Regards,Yelena Varsha
October 31, 2007 at 1:00 pm
Actually it seems in 2000 by default they care case-insensative but can be changed to be case-sensative.
Here is an interesting article on the subject
http://secretgeek.net/sql2k_2k5.asp
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]
October 31, 2007 at 1:36 pm
I just read this article. This is wrong. I support SQL Server starting from version 6.5 and I have at least 30 SQL Server 2000s that I connect. All of them are installed with US Collation which is
English (United States)
0x409
SQL_Latin1_General_CP1_CI_AS
1252
which is CI_AS - Case Insensitive, Accent Sensitive
and ALL passwords are case - sensitive
Regards,Yelena Varsha
October 31, 2007 at 2:35 pm
I don't know what to tell you. My server/databases are set with collation SQL_Latin1_General_CP1_CI_AS but when I try to log in with my password I can log in with any case setting.
As a test I logged in once with all lower case, then again with the same username and password but with the caps-lock on. In both cases I was able to log in just fine. I also tried this on 2 different servers with the same results.
Here is an exceprt from a Microsoft KB article. Admitedly it is for 7.0. A 2000 article is referenced below as well. Its not as specific though.
Path: http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/intro/part2/75515c02.mspx?mfr=true
Effect on Passwords of Changing Sort Orders
In SQL Server Authentication, when a login ID is added or a password is changed, the password encryption algorithm takes the supplied password and generates a numeric representation, which is stored in the syslogins table.
If the password is generated on a case-sensitive server, the original password text, as typed, is supplied to the password algorithm. When the password is subsequently checked, the user must supply the same characters in the appropriate case for authentication.
If the password is generated on a case-insensitive server, the password is converted to uppercase before generating and checking the encrypted form. When the password is subsequently checked, the user can supply the characters in upper-case or lowercase.
When changing sort orders, if a user transfers syslogins out of the server with one sort order and into a new server with another sort order, the encrypted passwords are moved as generated. This has the following effects:
• If moving from case-insensitive to case-sensitive servers, users must type passwords in uppercase to be authenticated.
• If moving from case-sensitive to case-insensitive servers, users cannot log in unless the password generated in the case-sensitive server was entirely in uppercase.
When changing sort orders, take the appropriate steps to ensure that users are able to log in to the server when the sort order conversion is complete. If the system administrator is not able to log in because of the effects described, he or she must restore the original sort order to correct the problem.
Here is an excerpt from a 2000 article
Path: http://msdn2.microsoft.com/en-us/library/aa214297(SQL.80).aspx
Case-sensitivity applies to SQL identifiers and passwords as well as to data. If you specify a binary or case-sensitive default sort order for an instance of SQL Server or database, all references to objects must use the same case with which they were created. For example, consider this table:
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]
October 31, 2007 at 3:19 pm
passwords in 2000 setup with "default settings" are case insensitive.
* Noel
November 9, 2007 at 10:42 am
Hi,
now I have 2 confirmed cases when passwords changed the case when moved from 2000 to 2005. All other passwords moved correctly.
What is interesting, if I assign the same password as for the first login to the third login, it gets transferred correctly. I am tracing every step of the move and use pwdcompare function. The binary version of the mixed case password is correct up to the point CREATE LOGIN statement is generated by sp_addlogin
Regards,Yelena Varsha
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply