This is the accumulated hands-on knowledge of days of work and hours of technical Kerberos videos that I didn't (and still do not) understand.
Imagine we have the four machines below:
| Machine |
Description |
</th>
| DC1 |
A domain controller running DOMAIN1 (www.domain1.com) |
| CLIENT1 |
A client machine with a user logged on as DOMAIN1DOMAINUSER1 |
| SQL1 |
A server running SQL Server with an INSTANCE1 instance. You can login with an SQL account (SQLUSER1) or a domain account (DOMAIN1DOMAINUSER). |
| FS1 |
A file server with files shared out under SHARE1. |
</table>
You want to use CLIENT1 to execute a Bulk Insert T-SQL command against SQL1INSTANCE1 which reads its data from FS1SHARE1; and they are all part of DOMAIN1 on DC1.
If CLIENT1 logs onto SQL1INSTANCE1 as an SQL user (SQLUSER1) there will never be any complicated problem. SQL1INSTANCE1 uses the account credentials the SQL Server service is running under (SERVICEUSER1) to authenticate with FS1SHARE1. As long as the share and the file permissions are set up to allow access everything will work (for once[tm]).
But if CLIENT1 logs into SQL1 with their DOMAIN1DOMAINUSER1 domain account a double-hop issue will often occur. SQL1INSTANCE1 will try to use DOMAIN1DOMAINUSER1 credentials to authenticate with FS1SHARE1, it isn't allowed to, so it doesn't work and it will return an error that leaves you scratching your head (like usual[tm]).
So how do you get it running, and fast?
## Automatically
- Remote desktop onto DC1.
- Start up "Active Directory Users and Computers". Under the "Computers" section find the entry for SQL1. Open Properties, go to the Delegation tab, and "Trust this computer for delegation to any service (Kerberos only)". You may need to do the same with the account for FS1 (mine was already turned on).
![][1]
This just lets SQL1 pass credentials onto FS1.
- Start up "ADSI Edit". Select the domain, then "CN=Users", and then "CN=Service User 1″ (this is whatever account your SQL Server service runs under). Go into Properties, the Security tab, select SELF, then Advanced.
![][2]
Now open the Permissions tab, click Add, the user is SELF, Ok, then go to the Properties tab. Find "Read servicePrincipalName" and "Write servicePrincipalName", and Allow both.
![][3]
The reason for all of this is that for Kerberos to work some SPNs are needed. Those are most easily created by the SQL Server process on startup; which we just gave it access to do.
- Remote desktop onto SQL1 and restart the SQL Server (INSTANCE1) service.
## Manually
The above should automatically create the SPNs needed for Kerberos to work. If you can't restart the service or it doesn't work, you can remote desktop onto SQL1 and create the endpoints manually from the command-line. You need to do this for the port or the instance, and both the short name and FQDN names:
```
# For instances
SETSPN –A MSSQLSVC/SQL1:INSTANCE1 DOMAIN1SERVICEUSER1
SETSPN –A MSSQLSVC/SQL1.DOMAIN1.local:INSTANCE1 DOMAIN1SERVICEUSER1
# For ports
SETSPN –A MSSQLSVC/SQL1:port DOMAIN1SERVICEUSER1
SETSPN –A MSSQLSVC/SQL1.DOMAIN1.local:port DOMAIN1SERVICEUSER1</li> </ol>
```
## Testing
Once this is all done, start a new SQL Server connection from CLIENT1 to SQL1INSTANCE1 logging on as DOMAIN1DOMAINUSER1, and run this T-SQL statement:
``` sql
Select auth_scheme
From sys.dm_exec_connections
Where session_id = @@spid
-- It should return KERBEROS
```
Now if you were to Bulk Insert from a file on FS1SHARE1 in this session, SQL1INSTANCE1 will use your DOMAINDOMAINUSER1 account to check share and file access permissions, and as it has permission to do so it will all just work[tm].
[1]: /images/2013/09/01.jpg
[2]: /images/2013/09/02.jpg
[3]: /images/2013/09/03.jpg