June 28, 2005 at 4:13 pm
Based on vendor requirements, SQL has to be installed with Mixed Mode Authentication. Several instances of SQL are installed with each instance being for a unique set of users. These users are external and access databases through a front end product. At no time have they had direct access to their databases, but this may change.
Because of the possible change, I would like to get security locked down more-so than it is now. Because users come in through Citrix, they have valid Windows logins. Since Mixed Mode is used, both the windows users (or groups) and SQL users have to have some strictness applied.
I'm a bit puzzled at today's findings. I was testing windows authentication and groups and found that I could not get this to work. I created a windows group, added myself and one other and went through the steps to add the group as a login in SQL. In this group, with admin permissions and in the System Administrators Server Roles, I was not able to log in with Windows mode. But, removing the windows group and adding just my login, i get in just fine. Domain\Group does not work: Domain\Dave does work.???
So, my questions:
1. What am I missing above? Why does the group not work?
2. Am I on the right track for completing what needs to be done?
Thanks,
Dave
June 29, 2005 at 7:07 am
When you add the group, what are you loggin in with? Grooup name or your user id?
June 29, 2005 at 7:32 am
I am logged into my machine with my network login. I'm logged into SQL with the sa username and password.
At this point it makes absolutely no sense. If I add Domain Admins to SQL Login I can use windows auth. If I create a group, add myself and make it a member of Administrators, I cannot login with windows auth.
Confused.
June 30, 2005 at 3:11 pm
If I understand this correctly, you created a domain group and added it to Sql server logins? Does this group have Login from the network policy to the windows server that hosts the sqlserver? Domain Admins can login from the network so this might explain why this works. The group you created might not.
Also, logging in with sa account doesn't not indicate that you are using windows auth. The contrary.
July 1, 2005 at 5:52 am
Turns out the problem was workstation related.
I went to a coworker's machine and asked him to add the group to the logins. It worked perfectly for him as he was a memeber of the group. I asked him to log off and then I logged into his machine. The login worked for me as well.
I came back to my machine, tried to get into SQL and it did not work. I rebooted and then tried again and it worked fine.
So, I have no clue what happened, but the reboot worked.
Thank you for looking into this.
Dave
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply