June 7, 2008 at 6:08 am
Hi all,
I'm running SQL Server 2005 standard edition and have the "Server Authentication" set to "SQL Server and Windows Authentication mode" in Server properties. However, when connecting to the server, I can still select "Windows Authentication" and connect to the server without specifying a user name and password. The username and password that I used to log into Windows XP doesn't exist as a login in SQL Server. Why is SQL Server letting me log in simply by selecting "Windows Authentication" without specifying a username and password and without even being logged into Windows with a username and password that matches a login and password in SQL Server? How can I prevent someone from logging in with "Windows Authentication" and enforce that they log in using "SQL Server Authentication" and enforce that they enter a valid username and password?
Pls reply me as soon as possible
Regards,
Sunil
June 7, 2008 at 6:30 am
If you a member of the local administrators group and there a SQL Server login named BUILTIN\Administrators, then you can connect with sysadmin privileges.
BUILTIN\Administrators and "SA" are created as part of the installation.
SQL = Scarcely Qualifies as a Language
June 12, 2008 at 10:19 pm
To expand upon what was said...
SQL Server can handle two types of logins:
- SQL Server logins
- Windows logins
SQL Server logins are the ones where the username and password combinations are stored in SQL Server. Windows logins come from the operating system or from Active Directory. With respect to Windows logins, you can either grant rights to an individual Windows user (like the one you use to log in to your workstation) or to a Windows group. If a particular Windows user belongs to a Windows group that has been permitted to log in, the user is able to do so because of the group. In addition, the user has all the rights the group does. SQL Server is handling the situation in the same way the operating system does, such as for folder or share permissions.
By default, the BUILTIN\Administrators group is granted the ability to log in to SQL Server. Also by default, it is made a member of the sysadmin fixed server role. The BUILTIN\Administrators group corresponds to the servers local Administrators group at the OS level. Therefore, if a Windows user is a member of that group, or a member of a group that has been placed in the Administrators group, the Windows user has the ability to log in and execute commands just as sa would.
The reason you don't see a password exchange is because it happens behind the scenes, in much the same way as it does when you connect to a network share or a network printer on the same domain. In SQL Server 2000 it is easy to see the network traffic when this occurs. SQL Server 2005 encrypts the packets with regards to authentication, so it's not visible in that case, but authentication still happens, even if you aren't being prompted.
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply