Crazy login problem
-
Hi
I have been using SQL 2K and 2K5 for a while, but we seem to have hit a very strange login problem on one of our servers. First a bit of background:
Server: Windows 2003, with SQL Server 2K (actually it's MSDE)
Client 1: Windows XP Pro SP2
Client 2: Windows XP Pro SP2
I can connect to the SQL 2K server fine if I log in to the client machine with one username and use either Windows integrated security or SQL Server authentication (e.g. "sa"). BUT if I log in to Windows on the same client machine with another account, then I can connect to the SQL server if I use SQL Server authentication with user sa, but can't get any administrative rights. I can't even connect at all if I try to use windows integrated authentication - I get the well-known "Login failed for user '(null)'" error. Both Windows accounts seem to be identical - both are members of the administrators group for example. There are also equivalent user accounts (same names etc) on the Windows 2003 server machine too, also with admin privileges.
I also get the same behaviour if I try it from a different client laptop (also Win XP Pro, SP2, etc.). This makes me think that the problem is at the server side somewhere. But I am confused that there is a difference when I am using SQL Server authentication, just because my client PC window login is different. Does SQL Server even look at my client Windows login if I use SQL Server authentication?
Finally, both the client machines also have SQL 2K5 Express server instances running on them as well, and I have no problems connecting between the two client machines in either direction using either of the windows accounts and using either windows integrated security stuff or SQL Server authentication.
I have done a trawl round various forums and KB articles, but all the references that I can find seem to be referring to IIS and web/application services and the like - I am not trying to do any of that but just simply connect to the database using *any* tool such as Management Studio or Enterprise Manager or ODBC or anything. The fact that it works fine from one windows login and not another seems bizarre.
Finally, I believe that this was working up to about a week ago (but I can't prove it - grr!)- maybe there has been a security update that has messed me up here. Anybody out there got any good ideas what else to check? This is driving me nuts.
-
Hi Tim, are the machines in a Domain or Workgroup? If it's a domain, I don't have much useful to add I'm afraid, but if it's a workgroup then I've come across something similar in the past. I've had to add duplicate Windows accounts for each user to the server, then browse to the server using windows explorer from the client, forcing a log on, telling the log on process to remember the login details, and after that I've been able to use integrated windows security in SQL Server. Don't know if that's of any use to you though sorry.
-
First off, enable success and failure auditing in MSDE. Check your application log to see exactly which accounts are being used, and what is failing.
"if I use SQL Server authentication with user sa, but can't get any administrative rights"
This can't happen - what is your test for administrative access?
"Both Windows accounts seem to be identical - both are members of the administrators group for example. There are also equivalent user accounts (same names etc) on the Windows 2003 server machine too, also with admin privileges. "
This will bring confusion, disable the duplicate accounts - if anything changes in your test results it means you're using duplicated accounts to grant access.
Only if the accounts you are logged in on Client 1 are members of Administrators on the Server is this useful information.
"I also get the same behaviour if I try it from a different client laptop"
This indicates that you are indeed using domain accounts to log in to your clients. Ensure that Domain\User1 and Domain\User2 have access to SQL by installing EM on the server (MSDE doesn't have it by default) and looking at logins. Either they should have an entry there or, more properly a group that they are a member of should have an entry there.
"Finally, I believe that this was working up to about a week ago (but I can't prove it - grr!)- maybe there has been a security update that has messed me up here. "
Yet another good reason to have full auditing in SQL (and in Windows) - gives you evidence of the crime
Essentially, whatever the problem is, you can clean things up by making both your users members of a domain group. Grant this access and permissions in SQL - tidy everything else up (duplicate accounts, uneccessary logins and groups).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply