??? SQL Server ???????? ?????????? ?? ????? ???? ????

• ????: SQL ????? ?? ????? ????????? ??????? ????? ???? Kerberos ?????????? ????? ????
• ???? ?? ????? ??? ?? ??????

????? ?? ???? ????

????? ?? ???? ????

Microsoft ??????? ????? ???? ?? ??? ????? ?? ?????? ?? ?? SQL Server 2000 ?? ??? ??? Kerberos ??????? ?????

??? 1: ???????? ????? ????????

1. ?? ???????? (IIS ???? ?????) ?? ??? ??? ???? ?????, ?? ?? ?? ??? ???? ?? ??? ????? ???? ?? ???????? ?? ????-????? ???????????? ?? ??? ?? ???????? ?? ??????? ????. ??? SQL Server ??? ??? ???????? ?? ???? ????? ???????? ?????? ???? ?? ??? ????? ???? ?? ????? ?? ???????? ?? ??? ???? ??? ?? ????? ??, ?? ?????? ?? ???????? ?? ?????? ?? ??? ??? ?? ???????? ??? ????? ???????? ???? ??, ?? intermediaries ??? ?????????? ???????? ?? ??? ????????? ???? ??????
2. SQL ????? ???? ???? ????? ?????????? ???? ?? ??? ???????? ?? ?????? ???? ??? ????? ?????????? ???? ????? SQL ????? ??????? ?? ??? (?? ??? ???? ?? ?? ?????????? SQL ????? ?? ??? ??? ?? ???? ??????? ?????? ???? ?? ????? ???? ?? ??? ??????) ???? ?????? ??:
   1. ?????Users?????? ?? ???, ?????????? ???? ?? ????-????? ????, ?? ???? ??????.
   2. ?????????? ???? ??? ????? ????? ??? ????? ??????????? ?? ????? ????..
   3. ?? ??????????? ?? ??????, ?? ??? ???? ?? ??? ????? ???????? ?? ??? ????????? ?? ??????????? ?????. ????????? ???? ?? ?????? ????????? ?? ?? ???????????? ???? ???? ?? ?????? ???? ?? ??? ??? ????? ???? ?? ???
      
      ???:???? ?? ?? ??? delegating ??????????? ?????? SQL ????? ?? ???? ?????? SQL ????? ???? ?????? ???????? (???? ??? ?? ????? ??????) Windows ?????????? ?? ????? ???? ???? ?? ??? ??? ????? hop ???????? ??? '???? ???????? ?? ??? ????????? ??' ???? ?? SQL ????? ???? ???? ?? ??? ?????? ???
   ???:???? Windows 2000 ????? ?? ??? ?? ????? ?? ???? ????? ??? ?? Windows Server 2003 ?? ????? ?? ??? ???, ?? ????? Microsoft ?????? ??????? (MSDN) ??? ???? ?? ????:
   HTTP://technet2.Microsoft.com/windowsserver/EN/Library/bef202b0-c8e9-4999-9af7-f56b991a4fd41033.mspx (http://technet2.microsoft.com/windowsserver/en/library/bef202b0-c8e9-4999-9af7-f56b991a4fd41033.mspx)
3. ?????? ???? ?? ??? Kerbtray.exe ?????? ?? ????? ?? Kerberos ???? ????? ???????? ?? ????? ?? ??????? ??? ??:
   1. ????? ??????? ??? Kerbtray ????? ?? ????-????? ????, ?? ???? ??????? purge.
   2. ???? ??? ?? ????? ?? ??? ??? Kerbtray ????? ?? ??? ????????? ????? ???? ?? ?? ???? ??, ?? ????? ????????? ????? ????? ?? ?? ?? ???? ?????:
      ???? NET * /d
      ?? ???? ??????? ???? ?? ????, ?? ??? ??? ???? ??? ??????? ???? ?? ???? ?? ??? Kerberos ???? ??????? ?????????

??? 2: IIS ???? ????? ???????? ????

1. ?????. asp ??????? ?? ??? ??????? ??? ???? Wwwroot ??????? ?? ???????????? ????? ?????. asp ??????? ?????, "ASP ??????? SQL Server ???? ????????????? ?? ??? ????????? ????" ??? ??? ??? ?? ??? ?? ????? ?????
2. Wwwroot ??????? ??? ????? ??????? ??? ???? ?? ???, "ASP ??????? ????????? ?? ??? SQL Server ???? ????????" ??? ??? ????? ??? ?? ????? ????? ????? ?? Default.asp ?? ??? ??? ???????
3. ?????? Windows ?? ????? ???? ?? ??? ??? ????? re-CONFIGURE ???? ???????:
   1. ??????? ??? ????? ?? ????-????? ????, ?? ???? ??? ??????? ??????? ????? ?????
   2. ??????? ??????? ??? ??? ???????? ????, ?? ?? ??? ???? ?? ??? ????? ???????? ?????.
   3. ?? ????? ????????? ?? ?? ???? ?????:
      cscript C:\Inetpub\Adminscripts\adsutil.vbs get w3svc/NTAuthenticationProviders
      ???Negotiateis enabled, the following is returned:

       NTAuthenticationProviders : (STRING) Negotiate,NTLM

      ???? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ?????? ????? ????::
      215383  (http://support.microsoft.com/kb/215383/ ) ??????? ??????? ?? ??? Kerberos ????????? ?? NTLM ????????? ?? ?????? ???? ?? ??? IIS ?? ???????? ???? ????
   ???
   • You must install Microsoft Data Access (MDAC) 2.6, or later, on the IIS Services server. To do so (and to make the tools available for testing), install the SQL Server 2000 client tools to the Web server. To install only MDAC 2.6, or later (without installing the client tools), visit the following Microsoft Web site:
     HTTP://msdn2.Microsoft.com/en-us/DATA/aa937730.aspx (http://msdn2.microsoft.com/en-us/data/aa937730.aspx)
   • IIS is a common middle tier system. However, IIS is not the only middle tier system. If IIS is not the middle tier system in your environment, follow the appropriate steps for your middle tier system.
4. ?????? ???? ??
   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
   .
5. Use the Kerbtray.exe utility to verify that Kerberos tickets were received from the domain controller and host:
   1. Right-click the Kerbtray icon in the notification area, and then clickpurge tickets.
   2. 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 * /d
      This will drop the existing sessions, and force a new session to be established and a Kerberos ticket received.

Step 3: Configure the SQL Server service to create SPNs dynamically

• ServicePrincipalName ?????
• ServicePrincipalName ?????

• ??? ?? ?????? ?????????? ???? ??????? (ADSI) ?????? ?????-??, LDP ?????? ?? LDAP 3 ??????? ?? ????? ?? ?????? ??????? ??? ??? ?? ?????? ?????????? ???????? ?? ????????? ???, ?? ????? ???????? ??? ?? ???????? ?? ?? ???? ?? ??? ?? Microsoft Exchange 2000 Server ?? Microsoft Exchange Server 2003 ?? ???????????? ???? ??? ???? ??? ??? ?????? ???, ?? Microsoft Windows 2000 ????? ?? Microsoft Windows Server 2003 ?? ???????????? ???? ?? Exchange 2000 Server ?? Exchange Server 2003 ?? ??? ?? ???????????? ???? ??? ???? ??? ?? ?????? ?? ???? ?? ?? ?? ???????? ?? ?? ???? ???? ?? ????????? ?? ???? ????? ?? ??????? ?????
• ?? ??? ?? ???? ????? ???? ????? ?????????? ?? ??? ???? ???????? ??? ??, ?? ???? ????? ?????????? ?? ???? ????????? ?? SQL Server ????????? ???? ?? ??? ??????? ?????????? ?????? ???? ????? ???? ?????

1. ????? ????,???????????? ????,???????????:Adsiedit.msc?? ????-????? ????, ?? ???? ???OK.
   
   ???:ADSIEdit ????? ??? ????? ?? Windows ?????? ?????? Windows ?????? ????? ?? ??????? ???? ?? ??? ????? Microsoft ??? ???? ?? ????:
   HTTP://www.Microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en (http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en)
2. ADSI ?????? ?????-?? ???, ??????? ????????? [DomainName], ??????? ????DC =RootDomainName, ??????? ????CN ?????????? =, ????-????? ????CN =AccountName?? ????-????? ????, ?? ???? ??????.
   
   ???
   • DomainName????? ?? ??? ?? ??? ?? ??????????? ???
   • RootDomainName??? ????? ?? ??? ?? ??? ?? ??????????? ???
   • AccountNameSQL ????? ???? ?? ??????? ???? ?? ??? ????????? ???? ?? ??? ?? ??????????? ???
   • ??? SQL ????? ???? ?? ??????? ???? ?? ??? ??????? ?????? ???? ????????? ????AccountNameMicrosoft Windows ?? ??? ?? ???? ?? ????? ???? ???? ???? ?? ??? ?? ??????????? ???
   • ??? SQL ????? ???? ?? ??????? ???? ?? ??? ??? ????? ?????????? ???? ????????? ????AccountName????? ?????????? ???? ?? ??? ?? ??????????? ???
3. ?????CN =AccountName???????? ????? ???, ????? ?????????????? ?? ????? ????..
4. ????? ?????????????? ??,?????.
5. ?????????? ??????? ????????????? ????? ???, ????????? ???? ?? ????????? ??????? ???????? ???????? ????????????.
   
   ?????????? ???? ??? ????, ????? ????add, ?? ???? ??? ??????????.
6. ?? ????????????? ????????????????? ????,??????? ????-????? ????, ?? ???? ?????????.
7. ??????????? ?????????????? ????? ???, ????? ?????????? ?? ????? ????..
8. ????? ?????????? ??,?? ??????????????? ???? ????????, ?? ???? ??? ?? ?? ??????? ????? ????????? ?? ??? ??? ????? ?? ??? ???? ?? ????????????:
   • ServicePrincipalName ?????
   • ServicePrincipalName ?????
9. ????? ????,OK?? ?? ??? ????? ????..
   
   ???:?? ????????? ?? ??? ??? ?? ??? ?????? ?????????? ?????? ?????? ?? ?????? ????, ?? ?? Microsoft ???????? ???? mention.
   
   ???:??? self ???? ????? ServicePrincipalName ?????? ?? ?? ????????? ???? ?? ??? dsacls ????? ?? ????? ????, ?? ?????dsacls????? ???????? ????? ??:

   dsacls <distinguished_Name_of_service_account>

   ??? self ???? ?? ????? ServicePrincipalName ?????? ??, ?? ?? ????? ?????? ????? ???:

   Allow NT Authority\SELF SPECIAL ACCESS for Validated Write to Service principal name
   WRITE PROPERTY

   Dsacls ????? ?? ?????? ????? ?? ??? ???
10. ?????CN =AccountName???????? ????? ???, ????? ??????????? ??????.
11. ?? ????????????????????? ????,servicePrincipalName?????????????????, ?? ???? ?????????.
12. ????????-??? ???????? ??????????? ????? ???, ?? SQL ????? ???? ???? ?? ????? ???? ???? SQL ????? ?? ?????? ?? ??? ???? principle ??? (SPNs) ?? ????????
    
    ???????:?? ?? ??????? ??? ????? ?? ??? ??? ?? SQL ????? ?? ?????? ?? ??? ???? SPNs ????? ?????? ???? ?????????? SQL Server ?? ???? ?? ?? ???? ?? ????? ???? ??? ?? ??????? ?? ?????????? ?? ???? ??? ?? ?? ?? ?????????? ??????? SPNs ??????? ??? ????? ??????
13. ADSI ?????? ?????-?? ?? ???? ???????

??? 4: ??????? ???????? ???????? ????

1. ???????? ??????? ?????? ???? ??, ?? ??? ???? ???? ?? Microsoft Internet Explorer Windows ?????????? ?? ????? ???? ?? ??? ???????? ???? ??? ??:
   1. Internet Explorer ???,????????? ??,??????? ??????.
   2. ????? ???????????? ?? ????? ????..
   3. ?? ??????????????, ?? ??? ???? ?? ??? ????? ?????????? Windows ?????????? (???????? ??????) ????? ?????? ????-????? ????, ?? ???? ???OK.

??? 5: ??????? ???????????

1. ???????? ?? ??? ?? ????, ?? ???? ??? ?????? ???? ?? ???????? ??????? ?? ???? ????? Kerberos ???? ???? ????? ???????? ?? ???? ?? ??? Kerbtray.exe ?? ????? ?????
2. ???????? ?? ??? ???? ??????? ?? ??? Kerbtray.exe ?? ????? ?????
3. ????? ?? SQL ???? ?? ?? ??? ??? ?? ??? ?????? ????? ?????
   
   ???:?????SQLSERVERNAME?? SQL ????? ?? ??? ?? ?? ???????? ?? ???:
   • ??? ???? ???? ??? ??, ?? ????? ??????? ?????? ????????? ???? ??Negotiate, ?? ?? ?????? ?? ??? SQL ????? ????sp_helpdb????????? ???? ?? ??? ?? ?? ????? ?? ??????? ?? ?? ???? ?? ???? ???? ????? ?? ???????? .ASP ????? ?? ?????? ?? ?????? ???? ?? ????
   • ??? ???? ??? ??????? ???? SQL ????? ???, ????????? ??? ??? ?? ??????? ?? ??????? "?????????" ???

????? ?? ???? ????

SQL ????? ???? ????????????? ?? ??? ASP ??????? ?????????

<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<%="'auth_user' is" & request.servervariables("auth_user")%>
<P>
<%="'auth_type' is" & request.servervariables("auth_type")%>
<P>
Connections string is <B>" Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pubs;Data Source=SQLSERVERNAME </B>
<P>
<%
	set rs = Server.CreateObject("ADODB.Recordset")
	set cn = Server.CreateObject("ADODB.Connection")
	cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pubs;Data Source=SQLSERVERNAME"
	rs.open "MASTER..sp_helpdb",cn
	Response.Write cstr(rs.Fields.Count) +"<BR>"
	while not rs.EOF
		Response.Write cstr(rs(0))+"<BR>"
		rs.MoveNext
	wend
	rs.Close
	cn.Close
	set rs = nothing ' Frees memory reserved by the recordset.
	set cn = nothing ' Frees memory reserved by the connection.
%>
</BODY>
</HTML>
					

????? ?? ???? ????

?????? ?????????? ????? principle ??? ?? ??????? ?? ???? ?? ??????? ???? ?? ??? ???? ????

• ???????????? ?? ?? ????? ??, ????? ?? ??????? ???? ?? ?? ???? ??? ?? ?????? ?????????? ????? ?? ??? ???
• "???????????????????? ?? ???"?? ???? ??? ?? ????? ????? SPN ????? ??????? ???

????? ?? ???? ????

NewouputUsers.txt ?? ???? ?????? ?????

	dn: CN=User Name,CN=Users,DC=betaland
	changetype: add
	servicePrincipalName: MSSQLSvc/CLUSTERDEFAULT.betaland:1257
	servicePrincipalName: MSSQLSvc/INST3.betaland:3616
	servicePrincipalName: MSSQLSvc/INST2.betaland:3490
	servicePrincipalName: MSSQLSvc/SQLMAN.betaland:1433
	servicePrincipalName: MSSQLSvc/VSS1.betaland:1433
	servicePrincipalName: MSSQLSvc/INST1.betaland:2536
	servicePrincipalName: MSSQLSvc/INST4.betaland:3967
	servicePrincipalName: MSSQLSvc/SQLVIRTUAL1.betaland:1434
	servicePrincipalName: MSSQLSvc/SQLVIRTUAL.betaland:1433
	servicePrincipalName: MSSQLSvc/SQLBUSTER.betaland:1315

????? ?? ???? ????