Case-sensitive User-ID :-(

pdj

NAV5SP1 with upd. 1 - SQL 2005 SP2 with upd. 8

I have a client using Database Authentication and therefore needs to create users in Server Mgt. Studio before creating them in NAV. But when users login they need to enter their user-id exactly (uppercase/lowercase) as created in Server Mgt. Studio. I guess this is because of the collation used in the Master database, which is Danish_Norwegian_CS_AS.

Will it solve my problem if I change the collation of the system databases to a non-case-sensitive collation? And how do I do it?

In the NAV database I use the Danish_Norwegian_CI_AS collation. Is there any problem in NAV using a different collations as in the system databases? (I fear the master and tempdb might cause problems)

Divya

If u see File --> Database --> Alter --> Collation Tab --> Case sensitive option is there...

Please check that

pdj

Divya wrote:

If u see File --> Database --> Alter --> Collation Tab --> Case sensitive option is there...

This is the NAV DB collation, but not collation of the master or tempDB databases.

DenSter

If you enter the user ID's in uppercase in SSMS, your problem will go away.

pdj

DenSter wrote:

If you enter the user ID's in uppercase in SSMS, your problem will go away.

Except that the user has to specify the user-id in uppercase each time they login. It is not a big problem, but it is still a problem for the users. Wouldn't the problem disappear is the master database was using a CI collation?

stryk

Hi!

Obviously when installing the SQL Server the collation was set to "Case Sensitive" which creates the "master" db that way - CS.
Hence, all logings - which are defined within "master" - are treated "case sensitive" - that's why your system behaves like this.

To get rid of this problem, you need to re-install the "master" database!
The "Books Online" describe how to perform this - but caution: backup your NAV database first, then detach it before fiddling with the master & co. db.
Afterwards you have to re-create all logins ...

Good Luck!

pdj

Thanks Stryk, this was the kind of answer I feared
Well, I'll estimate the task and the customer will have to determine how big a problem it is...

But do you (or anybody else?) have any experience with different collations in NAV db and TempDB?

stryk

Basically having different collations should not be a problem; e.g. you could have "master" on Latin1_General_CI_AS and "NAV" on Latin1_General_CS_AS - actually different collations.
SQL is designed to be able to also deal with very different collations using different code pages - usually it is a matter of the application if it could handle that.
I think you will not encounter any problems when running just NAV - except the annoying thing with the case sensitive logins ...

But: if you have a relatively new installation where you have this problem, I recommend to re-set the system/master - actually it's not a big deal, but double-effort - so you could give you customer a "clean" start with NAV ...