question

kbrumbaugh avatar image
0 Votes"
kbrumbaugh asked kbrumbaugh commented

SPNs on non-SQL service accounts

I have been doing some research but I still cannot get straight if there is a true requirement for an SPN on a standard user account that is NOT acting as a service account running an SQL service?

I read through his article but I didn't see this specifically answered about a requirement for the SPN to remain: https://blogs.msdn.microsoft.com/psssql/2010/03/09/what-spn-do-i-use-and-how-does-it-get-there/

"Automatic SPN Registration

When an instance of the SQL Server Database Engine starts, SQL Server tries to register the SPN for the SQL Server service. When the instance is stopped, SQL Server tries to unregister the SPN. For a TCP/IP connection the SPN is registered in the format MSSQLSvc/<FQDN>:<tcpport>.Both named instances and the default instance are registered as MSSQLSvc, relying on the <tcpport> value to differentiate the instances.

For other connections that support Kerberos the SPN is registered in the format MSSQLSvc/<FQDN>:<instancename> for a named instance. The format for registering the default instance is MSSQLSvc/<FQDN>.

Manual intervention might be required to register or unregister the SPN if the service account lacks the permissions that are required for these actions."


I know the admin account that had the SPN did not have the permissions to register SPNs on the domain but was involved in the setup and service restarts of the SQL installation or configuration.

The background on this is managing AD and I'm trying to reduce risk around unnecessary SPNs in our environment susceptible to Kerberoasting. I could update these user accounts by flagging them as able to support AES encryption (and having them reset the password 2x) but I was also just as curious if I could remove the SPN entirely and basically move on to other accounts that have true requirements (like SQL service accounts)?

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered kbrumbaugh commented

The SPN is for the service, not an account. There are not SPNs defined FOR a user accessing SQL Server, only the SQL Server service entirely.

The user referenced is the user trying to CREATE the SPN. The user creating the SPN must be a Domain Administrator to create the SQL Server service SPN.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you, that's what I needed to know. Appreciate the insight.

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi Kbrumbaugh,

Welcome to Microsoft Q&A.

if there is a true requirement for an SPN on a standard user account that is NOT acting as a service account running an SQL service?

I’m afraid we cannot. A Service Principal Name (SPN) must be registered for the SQL Server service account in order for Kerberos authentication to work. When the Database Engine service starts, it attempts to register the Service Principal Name (SPN).
If you don’t want to use the Kerberos authentication and SPN, you can try to change the SQL Server Service account to an account that doesn't have permission to register an SPN. In that case, this call fails, and a warning message is logged in the Application event log as well as the SQL Server error log.
Please refer to this article for more details.

Best Regards,
Amelia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

kbrumbaugh avatar image
0 Votes"
kbrumbaugh answered kbrumbaugh commented

@AmeliaGu-msft Thank you for your reply!

For clarification when I say "standard user account" I'm typically talking about an admin of the system that may log into the server to restart services, etc.

The "Admin of the server" does NOT need the SPN but the SERVICE account for the SQL service DOES?

Thanks again for your input!.

Kyle

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Kbrumbaugh,
Thanks for your reply.

The "Admin of the server" does NOT need the SPN but the SERVICE account for the SQL service DOES?

Yes, you are right. As others mentioned, the SPN maps to the Windows account that started the SQL Server instance service.

Best Regards,
Amelia

1 Vote 1 ·

Thanks, I wish I could give you all credit for this answer, you were all helpful

0 Votes 0 ·
cthivierge avatar image
0 Votes"
cthivierge answered kbrumbaugh commented

A Service Principal Name has to be set on an account (it could be a user account, a group managed service account, a computer account...) that is running the service.

For example in a case of a SQL server, a SPN has to be configured on the account that the SQL Service is running.
If your SQL Server engine is running with a service account named yourdomain\svc-sql, then you must register the SPN on this account.

If it's running with "Local System", in that case, the SPN will have to be registered on the computer account.

Always register the SPN on the account where the service is running.

The required permissions to create the SPN is either "Domain Admins" or you can delegate permissions at the domain level or on a specific OU.

hth

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for this additional clarification!

0 Votes 0 ·