April 5, 2006 at 1:00 pm
Hello,
I need some advice...
I am running SQL Server 2000... I've installed a new instance of this on a
new server (to migrate from server1 to server2).
On server1, i have a group set up (domain/SQLGroup)... This works with the current permissions.
On server2, i have the exact same group set up... unfortunately all the
users in this group doesn't get recognized by my new sql installation. I
get the error above (SQL Error 18456, State '28000') when they try and
login. (Access, VB progs, etc)...
I can change permissions for all the users in this group to have "Domain
Admins", but obviously i don't want to do that... by doing this though, all
access works as before...
SP4 is installed on server2, but not on server1...
Server1 is the Domain Controller...running sbs2000 (windows 2000 server).
Server2 is just windows 2003, and is dedicated to sql server 2000...
Does anyone have any suggestions on how to proceed with this?
Thanks.
April 5, 2006 at 2:56 pm
Sounds like you haven't gone into SQL Server and granted the gropu rights to login and any other permissions they should have.
April 5, 2006 at 3:36 pm
Hi,
I did do that in fact... i ran an export of all the logins from the first instance, and ran it on the second instance, and also ran the script (sp_change_users_login) that checks the GUID to make sure everything is right...
April 6, 2006 at 3:31 pm
Same domain? Other than OS, anything different between the two SQL Servers? Any corresponding Audit Failure errors in the Security event log (OS)?
K. Brian Kelley
@kbriankelley
April 7, 2006 at 11:03 am
April 10, 2006 at 9:55 am
When you get the error, does it indicate the login failed for that particular user or does it give a different error message? When you say "all the users in the group" does that mean some can get in who aren't domain admins or do you mean everyone is getting the same error message?
K. Brian Kelley
@kbriankelley
April 10, 2006 at 10:41 am
Hi there,
The error i get says the login failed for that particular user, but it is based on the user that is logging in (DOMAIN/User). This happens to every user in the SQLGroup that isn't a Domain Admin...
I am using Trusted Connection for the connection to SQL (using a DNS)...
Thank you,
...Robin
April 10, 2006 at 1:03 pm
Is that user account a part of any other groups. NT has a most restrictive processing handle. Could be in another group specifically denied.
April 10, 2006 at 1:25 pm
Hi,
No, it isn't part of any other group that could be specifically denied... in fact, i don't believe i have any Windows Groups that are specifically denied...
If that was the case though, then on my server1 box, i'd have problems with this as well... this deny of login only occurs on the win2003 server box...
April 10, 2006 at 1:55 pm
1) Have you tried deleting the account and readding?
2) Try doing so using sp_grantlogin instead then use EM to fix the parameters.
3) Are you using SQL 2000 EM or SQL 2005 Microsoft SQL Server Management Studio to admin this server?
Similar issue reported here is ahwy I ask.
April 10, 2006 at 3:16 pm
Hi,
1. Yes i have tried to delete the account, and readding it...
2. I did use sp_grantlogin to grant login to this group, and i still get the same error. Even when i try and access the server via the DNS (ODBC connection via Admin) i get the error when testing the access.
3. I am using SQL 2000 EM... i don't even have SQL 2005...
Thanks for the link... i looked at it, but the problem was easily solvable because they are using SQL 2005... (even beta...)
April 12, 2006 at 10:08 am
Hi,
Does anyone else have any suggestions? Because of this issue, i'm unable to continue with making this server live...
I was thinking of making the server a domain controller, but don't know the implications of that...
There weren't very many reported issues with this when i searched... so this is quite a unique issue i imagine...
April 12, 2006 at 2:26 pm
Can you login to Server 2 (the W2k3 server) with any account that is a memeber of the SQL Group account that isn't a domain admin successfully. To the server no SQL Server.
You know the accounts work on the old SQL Server as a member of the group and that the Domain Admin can login to the new SQL Server so it obviously can see the domain. Test to see an account to connect to the new server but not the SQL Server. If so then you are left with the issue being something in your SQL setup.
I know you said you used sp_change_users_login to fix the spid to match with the servers login. Try setting the group to have access to a phoney test database by default and see if they can connect. If so then remove the group from th database in queston and add back again then set back to being the default and see if corrects the issue.
There is going to be some simple thing that is being overlooked here. It is just a matter of what.
April 12, 2006 at 3:00 pm
Hi, thank you for continuing to help me out with this issue...
Yes... i just tried, and can log onto the server (console) itself with one of the accounts that is in the SQL Group.
As per your suggestion, i created a new database (called it Test), and set it as the default database for that Group. Unable to connect to that database as well (using ODBC Connection in the Admin Tools...
April 12, 2006 at 3:13 pm
OK so the issue can be pinpointed specifically to SQL Server. OK one last idea to see what may be going on.
Create a dummy user account on the Domain.
Create a dummy group on the Domain.
Assign that Domain group rights to login to the SQL Server and speicifcally the test database you created in my previous suggestion. And make that DB the default DB for it.
Test to see if you can logon to the Win 2K3 server alone with his account, then try logging on to th SQL Server and see if the issue is a duplicate of the other.
I am still researching anything that I can think of but this should now seperate possibly if the issue is SQL related or Group related.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply