http://technet.microsoft.com/en-us/library/bb735885.aspx
Kerberos Event ID: 529 is logged when you use a local user account to verify security access or group membership on a Windows Server 2003-based Kerberos client:
http://support.microsoft.com/kb/890477
Kerberos Authentication for SQL Cluster:
http://technet.microsoft.com/en-us/library/cc738070.aspx
How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005:
http://support.microsoft.com/default.aspx/kb/909801
Allow a User to be trusted for delegation:
http://technet.microsoft.com/en-us/library/cc739474.aspx
Kerberos Authentication - Tools and Settings:
http://technet.microsoft.com/en-us/library/cc738673.aspx
SQL Protocols:
http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx
RTFM - Edited for brevity, pivotal info could be omitted : )
You can use Kerberos authentication with Microsoft SQL Server 2005 stand-alone instances or with failover cluster instances running on a virtual server The following section describes how to connect to a server that is running Microsoft Internet Information Services (IIS) to make a Kerberos connection to a server that is running SQL Server
Prerequisites
Before you perform the Setup procedure, download the Kerbtray.
SQL Server only uses Kerberos if the client uses the TCP/IP protocol to connect to SQL Server. Connect to a server that is running Microsoft Internet Information Services and make a Kerberos connection to SQL Server 2005
Step 1: Configure the domain controller
In Active Directory Users and Computers: Trust this computer for delegation. If the computer that is running SQL Server is what appears to be the last computer contacted but that computer has a linked server, it must also granted delegation permissions. If it is not the last computer in the chain, all the computers that are intermediaries must be trusted for delegation.
Grant delegation permission to the SQL Server service account domain user account. You must have a domain user account for clustered SQL Server installations (this step is not required for computers that are running SQL Server that are using a local system account):
In the Users folder, right-click the user account, and then click Properties.
In the user account properties dialog box, click the Account tab.
Under Account Options, click to select the Account is Trusted for Delegation check box. Make sure that the Account is sensitive and cannot be delegated check box is cleared for this account.
Step 2: Configure the IIS services server
Replace the default Web site Wwwroot files with the sample .asp files. To create the sample .asp files, use the code that is provided in the "ASP test script for SQL Server data retrieval" section.
Add the file to the Wwwroot folder. To do so, use the sample code in the "ASP Test Script for SQL Server Data Retrieval" section. Save the file as Default.asp.
Reconfigure the Web server to use Integrated Windows Authentication only:
Right-click the default Web server, and then click the Security folder.
In the Security folder, make the correct changes, and then click to clear anonymous access.
From a command prompt, run this command:cscript C:\Inetpub\Adminscripts\adsutil.vbs get w3svc/NTAuthenticationProvidersIf Negotiate is enabled, the following is returned: NTAuthenticationProviders : (STRING) Negotiate,NTLMFor additional information on configuring IIS to support both Kerberos and NTLM authentication, see the Knowledge Base article, "How to Configure IIS to Support Both Kerberos and NTLM Authentication."
Note:
You must install Microsoft Data Access (MDAC) 2.8 SP1, or later, on the IIS Services server. To do so (and to make the tools available for testing).
Verify that the HKLM\SW\MS\MSSQLSERVER\Client\DSQUERY value is present in the registry. If the value is not displayed, add it as DSQUERY:Reg_SZ:DBNETLIB.
Use the Kerbtray.exe utility to verify that Kerberos tickets were received from the domain controller and host:
Right-click the Kerbtray icon in the notification area, and then click purge tickets.Wait for the green Kerbtray icon to change from green to yellow. As soon as this occurs, open a command prompt window and run this command:net session* /dThis will drop the existing sessions, and force a new session to be established and a Kerberos ticket received.
Step 3: Create an SPN for SQL Server
If you have multiple instances of SQL Server on a computer, you must configure a Server Principal Name (SPN) for each instance of SQL Server because each instance of SQL Server uses a unique TCP-IP port.
Important:
If the SQL Server service runs under a domain user account, you must manually configure an SPN. To do so, follow these steps.
Note the domain user account that the instance of SQL Server is running under. In the following examples, this account is named
Ping the computer that is running SQL Server to determine its IP address:
C:\>ping MySQLServer
Pinging MySQLServer.MyDomain.com [10.10.10.10] with 32 bytes of data:
Reply from 10.10.10.10: bytes=32 time=1ms TTL=128
Use ping -a to perform a reverse lookup of the IP address to make sure that the FQDN is correctly returned by the Domain Name System (DNS) protocol:
C:\>ping -a 10.10.10.10Pinging MySQLServer.MyDomain.com [10.10.10.10] with 32 bytes of data:
Reply from 10.10.10.10: bytes=32 time<1ms ttl="128">
Ping the virtual SQL Server name to obtain the IP address, and then run ping -a to make sure that the FQDN is correctly returned by the DNS.
Note:
If you are using SQL Server failover clustering, you use the FQDN for the virtual SQL Server. Note the exact TCP/IP port that the instance of SQL Server uses. To determine this information, open SQL Server Configuration Manager on the computer that is running SQL Server, click the instance of SQL Server, and then view the properties for the TCP/IP protocol (default port).
You must be a member of the Domain Administrators group to run the SETSPN command.
If you are using SQL Server failover clustering, run the following SETSPN command:
setspn -A MSSQLSvc/
For example, if MySQLServer.MyDomain.com is running under the domain user account SQLSVC, where MySQLServer.MyDomain.com is the name of the instance of SQL Server 2005 that is clustered, run the following command:
setspn -A MSSQLSvc/MySQLServer.MyDomain.com SQLSVC
For both clustered and non-clustered computers that are running SQL Server, run the following SETSPN command to register an SPN for the port that the computer that is running SQL Server is using:
setspn -A MSSQLSvc/
Run SETSPN -L
If you are using SQL Server failover clustering, you must register a SPN without the port number and another SPN with the port number. With a typical, non-clustered computer that is running SQL Server, you only have to register the SPN with the port number. However, if you have an additional SPN without the port number, it will not cause any problems with non-clustered computers.
Step 4: Configure the client computers
For each client that will connect, verify that Microsoft Internet Explorer is configured to use Windows Authentication:
In Internet Explorer, on the Tools menu, click Internet Options.
Click the Advanced tab.Under Security, click to select Enable Integrated Windows Authentication (requires restart), and then click OK.
Step 5: Test the configuration
For each computer that is involved:
Log on to the computer, and then use Kerbtray.exe to verify that the computer can obtain a valid Kerberos ticket from the domain controller.
Use Kerbtray.exe to remove all tickets on the computer.
Create and connect to the Web page that returns the SQL Server data.
Note:
Replace SQLSERVERNAME with the name of the computer that is running SQL Server:
If data is returned, this page displays the authentication type Negotiate, and the SQL Server data for the result of the sp_helpdb stored procedure that should return a list of the databases on the server that is being connecting to through the .ASP page.
If you have auditing turned on in SQL Server, in the Application log you will see that the connection is "trusted".
No comments:
Post a Comment