June 18, 2012 at 2:41 am
hi,
Can we possible disable case sensitive in SQL 2K8 & R2?
recently migrated database SQL2K8 from SQL2K, In SQL2K user password lower case and upper case mention at web.cnfig file in application side, so there is no issue in SQL2K could worked lower & upper case password even database side lowercase password. in this scenario not worked with SQL2K8 & R2 due to case sensitive.
June 18, 2012 at 6:04 am
Check the collation sequence of the database and tables CS/CI
June 18, 2012 at 8:46 am
Not sure I totally understand your question. Are you talking passwords stored in the database that are used for an application or the passwords used for sql logins?
SQL logins are case sensitive, that can't be changed.
If you are talking about application type passwords that you use to authenticate users then I suspect you have them stored in plain text or the case issue would not even come up. You really should store these passwords as a hash (with salt) even if this application itself does not contain sensitive information. If it is stored as plain text you can make it case insensitive with collation. You could set this on the database or even as granular as the column. You can even specify collation during a select statement.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2012 at 10:49 pm
by default installing SQL server 2008 or R2, Database Engine collation SQL_Latin1_General_CP1_CI_AS
Please confirm, can we changed SQL2000 collation at SQL 2008 database? if changed SQL2000 collation then SQL server 2008 features will be work?
June 19, 2012 at 7:10 am
ananda.murugesan (6/18/2012)
by default installing SQL server 2008 or R2, Database Engine collation SQL_Latin1_General_CP1_CI_ASPlease confirm, can we changed SQL2000 collation at SQL 2008 database? if changed SQL2000 collation then SQL server 2008 features will be work?
I don't totally understand your question but if you set the collation to SQL_Latin1_General_CP1_CI_AS then your database is not case sensitive.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 8:30 am
Sean Lange (6/19/2012)
ananda.murugesan (6/18/2012)
by default installing SQL server 2008 or R2, Database Engine collation SQL_Latin1_General_CP1_CI_ASPlease confirm, can we changed SQL2000 collation at SQL 2008 database? if changed SQL2000 collation then SQL server 2008 features will be work?
I don't totally understand your question but if you set the collation to SQL_Latin1_General_CP1_CI_AS then your database is not case sensitive.
Thanks for reply...
I want to remove case sensitive in SQL 2008 Database, currently server collation is SQL_Latin1_General_CP1_CI_AS, but all database working with case sensitive in SQL 2008. could you tell me, which is the correct server collation if remove case sensitive in database?
Thanks
ananda
June 19, 2012 at 8:42 am
ananda.murugesan (6/19/2012)
Sean Lange (6/19/2012)
ananda.murugesan (6/18/2012)
by default installing SQL server 2008 or R2, Database Engine collation SQL_Latin1_General_CP1_CI_ASPlease confirm, can we changed SQL2000 collation at SQL 2008 database? if changed SQL2000 collation then SQL server 2008 features will be work?
I don't totally understand your question but if you set the collation to SQL_Latin1_General_CP1_CI_AS then your database is not case sensitive.
Thanks for reply...
I want to remove case sensitive in SQL 2008 Database, currently server collation is SQL_Latin1_General_CP1_CI_AS, but all database working with case sensitive in SQL 2008. could you tell me, which is the correct server collation if remove case sensitive in database?
Thanks
ananda
Collation can be set at the server level. This defines the default collation for all new databases. The collation can be different at the database level. You can check and change the collation for a given database in SSMS. Right click on the database in Object Explorer. Then go to Options. There will be a dropdown at the very top of this window where you can change the collation to whatever you want it to be for the currently selected database.
I don't know which collation would be best for you but this link has a full list of all available collations. http://msdn.microsoft.com/en-us/library/ms180175
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 20, 2012 at 3:46 am
Collation can also be set at the column level, if that's any help.
However, I'm slightly confused as to how the collation of an existing column could have changed during migration. If you were to upgrade an existing instance, then the database collation wouldn't change, and if you were to detach/attach or backup/restore then it would also retain the collation of the old database. You can of course change the collation of a database, but that only affects new columns, and doesn't change existing ones. I know because I had a database where the collation had changed at one point in its life so some columns were one collation and other columns were a different collation. Changing it to be all the same collation was extremely difficult and time consuming as it involved changing individual columns.
June 20, 2012 at 4:48 am
I have done side by side migration activity not in same server, By using restored all existing database from SQL 2000 to SQL 2008 new setup. In this case existing server/database collation does in new SQL 2008 new setup. as of now all the database sql 2008 collation is SQL_Latin1_General_CP1_CI_AS but still database user password working as case senstive only.. how to reslove user password case insenstive? which were created password as SQL 2000.
http://msdn.microsoft.com/en-us/library/ms180175
CaseSensitivity
CI specifies case-insensitive,
CS specifies case-sensitive.
thanks
June 20, 2012 at 5:10 am
ananda.murugesan (6/20/2012)
I have done side by side migration activity not in same server, By using restored all existing database from SQL 2000 to SQL 2008 new setup. In this case existing server/database collation does in new SQL 2008 new setup. as of now all the database sql 2008 collation is SQL_Latin1_General_CP1_CI_AS but still database user password working as case senstive only.. how to reslove user password case insenstive? which were created password as SQL 2000.http://msdn.microsoft.com/en-us/library/ms180175
CaseSensitivity
CI specifies case-insensitive,
CS specifies case-sensitive.
thanks
You're sure the case problem is in the database and not the application?
Assuming it is in the database, try the following:
Run this script to get the database collation:
SELECT DATABASEPROPERTYEX('<Your_Database>', 'Collation')
GO
Then run this script to get the collation of the password column
USE <Your_Database>
GO
SELECT collation_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<Table_Name>' AND column_name = '<Column_Name>'
Obviously replacing the bits in <> with whatever the right values are in your system.
Post the results back here, and we should get a better idea of what is going on.
June 20, 2012 at 5:34 am
changing collation for all the columns can sometimes be a pain, especially if there are contraints or defaults on fields that are agetting their collation changed;
things like CHECK columnname IN ('Y','N') defaults, and stuff have to be changed too. even foreign keys if thgey happen to be using a varchar columns being unique;
I wrote a prototype for changing the database collation for that once, it's about 1000+ lines of code; lots of work.
Lowell
June 20, 2012 at 7:16 am
ananda.murugesan (6/20/2012)
as of now all the database sql 2008 collation is SQL_Latin1_General_CP1_CI_AS but still database user password working as case senstive only.. how to reslove user password case insenstive? which were created password as SQL 2000.
In 2000 the case sensitivity of the password was the same as your default collation in the instance (the default is case insensitive). This changed in 2005. Passwords will always now be case sensitive regardless of collation. You can't change this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 20, 2012 at 7:28 am
Sean Lange (6/20/2012)
ananda.murugesan (6/20/2012)
as of now all the database sql 2008 collation is SQL_Latin1_General_CP1_CI_AS but still database user password working as case senstive only.. how to reslove user password case insenstive? which were created password as SQL 2000.In 2000 the case sensitivity of the password was the same as your default collation in the instance (the default is case insensitive). This changed in 2005. Passwords will always now be case sensitive regardless of collation. You can't change this.
Oh, I get it now. It's the password of the SQL Server login, not a password stored in a table. Makes much more sense now. :hehe:
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply