SQL Server Access


There are two ways, called “authentication modes,” into a SQL Server 2005 party If you have “connections,” you can enter by special invitation. This special invitation requires that you know someone on the inside, usually the Boss, aka. the SA. To gain access, you need to go to the back door, where you will be asked for your login name and password. If you can match both, you can get in. This mechanism was once the primary way into SQL Server (at least for people who carried some firepower).

This first access mechanism was once known as standard mode (version 6.5 and earlier). It is “nontrusted” access. In other words, anyone that comes to the door is suspect and has to be authenticated on the spot; they do not have a referral but need to match a login name with a password. From SQL Server 7.0 and later, this access mechanism was downgraded to the least-preferred method of gaining access.

The second way to get into SQL Server is through a referral network, a system that supports the tenet that a friend of my friend is also my friend. To gain access via the referral system, you need to go to the front door and present a vouchsafe to secure your access. The “bouncer” will recognize the vouchsafe and let you in, or toss you out onto the street.

The vouchsafe in question is the access token assigned to you when you successfully logged in to the network. And advanced notice of it, using the security identifier, is stored in the SQL Server trusted login information. This second access mechanism is known as Windows Authentication (formerly Windows NT Authentication). If SQL Server does not recognize the vouchsafe, it has no (or incorrect) SID information, and you do not have a SQL Server login and password to offer as an alternative, you are politely declined entry.

There are more advanced or secure means of implementing trusted access that can be achieved by SQL Server, but for now you should understand the basics. There are thus only two ways to configure SQL Server 2005 access. The preferred method is via Windows Authentication (no back doors). The other, for nontrusted access, is called Mixed Mode Authentication. The latter lets you in via Windows Authentication or via SQL Server Authentication. Both methods are illustrated in Figure 5–6.

image from book
Figure 5–6: Different methods of authenticating to SQL Server

In the past there has been some debate concerning which method is more secure. Many DBAs felt that Windows Authentication, although defined as trusted access, was less secure. The weaknesses in Windows NT 4.0 security, via the services of NTLM, a challenge-response mechanism, were primarily responsible for this perception. There is good reason: Once you are authenticated to the network or domain, there is very little, aside from auditing, to retry or retest your trustworthiness. To many, the ability to just “slide” into SQL Server by virtue of being logged into the network using a login known by SQL Server is scary. And many NT networks are as secure as a child’s shoelaces. The network administrator might have groups and permissions out the wazoo, only every ACL has the Everyone group with full access embedded in it for posterity.

On the other hand, how secure is SQL Server security? How secure is a lock if you have its key or the combination number? The subject of Windows Server 2003 domains is different story, however. Thanks to Kerberos and its system of tickets, the debate of trusted versus nontrusted security can finally rest in peace. To understand how it secures SQL Server 2005, you need to understand a little about Kerberos tickets.

Changing Authentication Modes

You can set and change the authentication modes described here as follows:

  1. Open SQL Server Management Studio (SSMS) as described in Chapter 4, in the section “Introducing SQL Server Tools,” right-click the server name, and select Properties.

  2. Next click the Security page. Under Server Authentication you can select the authentication mode you wish to use. Choose either Windows Authentication or Windows and SQL Server Authentication. Advice: choose Windows and SQL Server Authentication.

  3. Click OK to save the setting. You will see a message box suggesting that the service should be restarted before the setting affects the server.

  4. To restart the service right there in SSMS, simply right-click the server in Object Explorer and choose Restart. You will need to do this off peak or outside production hours if the server is already accessed by users.




Microsoft SQL Server 2005. The Complete Reference
Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features
ISBN: 0072261528
EAN: 2147483647
Year: 2006
Pages: 239

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net