Security problem, changing db collation to fix?
-
i've recently discovered that a 3rd party db on one of our shared servers has a different collation (Latin1_General_CI_AS) than the server (SQL_Latin1_General_CP1_CI_AS). we are experiencing some issues with users not able to connect in the application and i'm left wondering if it could be related.
using win auth, the users belong to a windows group which is added to sql security, so no individual access for users, everything is controlled by groups. within this group, some but not all users can't connect to the db. if i add them as individual logins, they can connect without problem.
while investigating the login problem, while checking the group logins, i stumbled upon an error message when clicking on (in management studio) security / logins / <the group login> / securables. i get the error:
Cannot resolve the collation conflict between Latin1_General_CI_AS and SQL_Latin1_General_CP1_CI_AS in the UNION operation. (Microsoft SQL Server, Error: 468)
any idea if this collation problem is causing the user access problem? it's strange that it would occur for only a couple users within a group that has dozens. plus we have at least a dozen other groups, each with many users, who are not having a problem to connect.
secondly, what's the best way to change the collation of a db? i've read that simply changing the db collation property does not fix it. you must drop/rebuild all data tables and indexes. how's this for a fix:
1 - backup/restore the prod database on a test server
2 - change db collation on test server
3 - drop all tables on test db
4 - import all tables from prod to test db
5 - backup test db / restore to prod server
this would keep all the other db objects such as stored procs, functions, etc and eliminate the need to transfer these from one db to another.
-
another thing to note: when clicking on the login properties, if i open properties and directly click on securables, i do not get an error. but if i open properties and click on user mapping then securables, i get an error. if i click on securables first, then user mapping, then back to securables, i do not get the error. it seems that only certain orders of clicking on the properties triggers the error. weird.
-
I don't think that the user access issue is related to the collation conflict.
The collation conflict error you get in SSMS is due to the queries issued by SSMS itself to retrieve the user mapping info, but does not affect the permissions.
On the collation change thing: it's very tricky.
You can read a post of mine here[/url].
Basically, you would have to micgrate the db to a different server with the same collation as the database, then change the server collation to the collation of your production server with the method described in my post and then copy it back.
-- Gianluca Sartori
-
ciao gianluca,
the article seems to deal with changing the collation of the system databases. i don't want to change the server, only the database. does my method above seem correct?
regarding the security problem, if only one or two users of a windows group are having a problem, where could the problem be? seems like it should be an all-or-nothing situation. i can't resolve why certain users would have problems.
i have spent a good time with trace and watching a user login without success. trace returns a audit login fail event:
Reason: Failed to open the database specified in the login properties.
i have tried the following without success:
changed the default db for the windows group login to the app db instead of master
made the windows group login sysadmin
removed him and readded him to the windows group
each returns the same login failed error. it makes no sense because there are at least 20 other users in the group that have no problem. it works for him if i add him as an indivuid
-
the article seems to deal with changing the collation of the system databases. i don't want to change the server, only the database. does my method above seem correct?
You're right: the method described in the article deals with changing the collation of the whole instance, but not only. One of the side effects of the "undocumented" method is the change of the collation for each char-based column in all user databases.
AFAIK, this is the only painless method to perform such a change. Otherwise, you would have to change collation on each column manually, taking care of dependant objects and the like.
You could set up a small test instance on your desktop/laptop just for the purpose of the collation change and then uninstall it.
Reason: Failed to open the database specified in the login properties.
This is a meaningful message to start investigating from. Have you tried adding the login directly to the database users? I suspect it could be a group related issue.
-- Gianluca Sartori
-
how can i see the collation at the column levels? i want to check if the db i exported to on a test server has the same collation on the columns as the prod server, or has changed to the test server's collation.
for the login, it works if the user is added as an individual win auth login. some, but not all members of the group have a problem to connect.
-
You could query INFORMATION_SCHEMA.COLUMNS for the column level collation info.
However, I don't think that the collation thing is the cause of your security issue.
Have you tried deleting the database user for the logins that can't connect and then add it back trough user mapping?
-- Gianluca Sartori
-
i haven't deleted the group because there are other users connecting thru it.
i did create a duplicate group with the same properties and db mappings and added my problem users to it. No luck. They have the same connect problem.
-
However, you should see a database user for each single user in the windows group. Have you tried deleting them?
-- Gianluca Sartori
-
where should i see individual users? in SSMS, i expand security / users and i only see the groups listed.
-
OLDCHAPPY (8/26/2011)
i haven't deleted the group because there are other users connecting thru it.i did create a duplicate group with the same properties and db mappings and added my problem users to it. No luck. They have the same connect problem.
Have you try removing the problem users from the Windows Group and adding them again? Is there any change that the specific users have been denied access to that database?
-
I mean under database users.
Do you have a database user for any of those logins in the group?
-- Gianluca Sartori
-
Ignacio A. Salom Rangel (8/26/2011)Have you try removing the problem users from the Windows Group and adding them again? Is there any change that the specific users have been denied access to that database?
we've tried removing and readding the users to the groups but no luck. we created a new group, added these users, and they still cannot connect.
Gianluca Sartori (8/26/2011)
I mean under database users.Do you have a database user for any of those logins in the group?
no individual logins exist for these users. they are only in the groups.
-
OLDCHAPPY (8/26/2011)
Ignacio A. Salom Rangel (8/26/2011)Have you try removing the problem users from the Windows Group and adding them again? Is there any change that the specific users have been denied access to that database?
we've tried removing and readding the users to the groups but no luck. we created a new group, added these users, and they still cannot connect.
Try to give the new group you create in the AD rights on another database and see if then you can connect to the other database (On the same server).
-
Where the user ever able to log into the database? and Did you or one of your colleagues change anything on the SQL Server?
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply