Case sensitive user IDs in SQL

David_Singleton

I have an occasional issue that I am having problems diagnosing.


Basically I have cases where I create a SQL backup from a clients server then restore it to my local machine, I can not use DB logins to connect to that database. I am trying to narrow this down, but its not easy. basically it seems that I only get the issue if:

a/ The clients system was originally Native and had been converted to SQL. (Well more correctly stated, I have never had the problem with a Database that started life as SQL).
b/ The case of the USER id is different from. By that I mean that if you create a lowercase I in Native, it automatically converts to Uppercase in Navision, but SQL will allow you to create a user id in upper or lower case.

What I am guessing is that somewhere the password that was stored in the Native database, and that should no longer exist in the Converted SQL database, is actually still there, but I assume does not matter. But maybe somehow there is some "memory" of the case issue.

I had the issue last week, and went into SQL, deleted the original user (eg: johnsmith) and recreated a new user (JOHNSMITH) and then was able to log in. Of course it might just be revalidation of the password, but I had tried numerous times to change the password and that did not help.

Anyway its not a major issue, since its pretty easy to just create a new user, but its been niggling at me or months now and I feel I need to resolve this.

garak

The problem with password i never had.

That on a new server only the dbowner / sa can login and not a other database user is normal (in my eyes).
Because on the old Server this dbuser has a other internal ID then on the new server.
Thats also if on the new server a database user exist with the same user name (it still a different id).
And that the Database user id is case sensitive is also a sql server feature.

viewtopic.php?f=5&t=23830

dmccrae

The password from native does not matter, since its' not used on the SQL version at all.

I'm thinking it might be related to your SQL Server instance's collation - meaning specifically if the server is case sensitive or not. This is chosen at SQL Server installation time (and is pretty invisible) and the impact is on identifiers (database names, login names) stored in the master database (and other system databases).

If I create the SQL login 'dean' on a case-insensitive server, I can connect as 'DEAN'; on a case-sensitive server the connection is refused.

David_Singleton

dmccrae wrote:

The password from native does not matter, since its' not used on the SQL version at all.

I'm thinking it might be related to your SQL Server instance's collation - meaning specifically if the server is case sensitive or not. This is chosen at SQL Server installation time (and is pretty invisible) and the impact is on identifiers (database names, login names) stored in the master database (and other system databases).

If I create the SQL login 'dean' on a case-insensitive server, I can connect as 'DEAN'; on a case-sensitive server the connection is refused.

So it might be different colation on the server that the backup was created on.

It happens only every now and then, so I am having trouble specifically identifying it, the issue, but i will check this the next time it happens. The problem is that its normally easier just to create a Windows login and get back to work. But curiosity is wanting me to resolve this.

BTW the user is always DBO so it doesn't look like a DBO issue.

pdj

Isn't it just because the encrypted passwords from the original Native DB still exists in the User table after migration to SQL and then are brought to life again when you restore the db to a Native db?

kine

as dmccrae wrote, it depends on the Server collation (Master database collation). And it doesn't matter with which collation you create the NAV db. Logging is done through Master database and thus depending on the case sensitivity of the Master database. I have met this problem few times too...