If you are interested in allowing users to use Windows Authentication across multiple SQL Servers via linked servers, then Kerberos Constrained Deletgation is a feature in Active Directory Domain Services that can help you achieve the goal. In order to use Kerberos, you must have the Service Principal Names(SPNs) set, and have Kerberos contrained deletegation configured. When the SQL Server service starts, an attemp to register the SPN in Active Directory Domain Services is attempted. By default, only the following accounts have permission to register SPN: Local System, Network Service, and Domain Admin.
In SQL Server 2012, by default Virtual Account is assigned and created when installing SQL Server instance, Virtual Account can access the network in a domain environment. To configure Kerberos, referencing the following steps:
1. Login to domain controller.
2. Go to Administrative Tools -> Acitve Directory Users and Computers.
3. Right-click the source machine object that hosts SQL Server -> Properties -> Delegation tab
4. Click Trust this computer for delegation to specified services only, and leave Use Kerberos only by default.
-> click Add button
-> Users or Computers
-> Advanced
-> Find Now
-> choose the destination machine(s)
-> choose the objects under MSSQLSvc service type, each instance has two objects under this type, please note that the port values respresent the default instance are displayed blank and 1433 for instance name and default port respectively.
If you use Managed Service Account(MSA) to start on SQL Server service, you need to grant sufficient permission to the account so that it can register SPN, and also you must configure Kerberos by accounts in domain controller via the following steps:
1. By default the Delegation tab is hidden in Users object. You must run the following sample of command to make it visiable: setspn -a MSSQLSvc/SQL-A.Contso.com spiner_tsai
2. Go to Administrative Tools -> Acitve Directory Users and Computers.
3. Right-click the account -> Properties -> Delegation tab
4. Same action as step 4 above.
No comments:
Post a Comment