Microsoft mind games
by Volker Weber
Microsoft calls this a Simplified Explanation:
If you run the SQL Server service under the LocalSystem account, the SPN is automatically registered and Kerberos interacts successfully with the computer that is running SQL Server. However, if you run the SQL Server service under a domain account or under a local account, the attempt to create the SPN will fail in most cases because the domain account and the local account do not have the right to set their own SPNs. When the SPN creation is not successful, this means that no SPN is set up for the computer that is running SQL Server. If you test using a domain administrator account as the SQL Server service account, the SPN is successfully created because the domain administrator-level credentials that you must have to create an SPN are present.
Because you might not use a domain administrator account to run the SQL Server service (to prevent security risk), the computer that is running SQL Server cannot create its own SPN. Therefore, you must manually create an SPN for your computer that is running SQL Server if you want to use Kerberos when you connect to a computer that is running SQL Server. This is true if you are running SQL Server under a domain user account or under a local user account. The SPN you create must be assigned to the service account of the SQL Server service on that particular computer. The SPN cannot be assigned to the computer container unless the computer that is running SQL Server starts with local system. There must be one and only one SPN, and it must be assigned to the appropriate container. Typically, this is the current SQL Server service account. However, this is the computer account with local system.
The last sentence just blows my mind away. And if you read between the lines, it screams "just run it with admin privileges, you SOB."
Comments
Reading that, which was not easy, gave me a headache.
Interesting read... now I need to go take some aspirin and a nap.
It's as clear as mud.
those guy smoke a really strange weed...
I do not dare to imagine what the Complex Explanation might have been.
The last sentence looks like a leftover from a failed copy & paste operation. :-) The rest reads like "Sorry that we did not do our homework, but let us explain how you can do it for us.".
If there actually is a (however painstaking) method to create a valid SPN and assign it to the limited account SQL Server runs in, the UI should ask for Admin credentials and just do it.
Its easy to create a spn yourself , either use the sql utility or setspn frmo the resource kit.
running sql as local system or domain admin is not best practice.
To manually create a domain user Service Principle Name (SPN) for the SQL Server service account
Click Start, click Run and then enter cmd in the Run dialog box.
From the command line, navigate to Windows Server support tools installation directory. By default, these tools are located in the C:\Program Files\Support Tools directory.
Enter a valid command to create the SPN. The command should be in the form of: setspn –A MSSQLSvc/:1433 .
