SQL Server - register Service Principle Name without service restart?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty{ margin-bottom:0;
}

up vote
2
down vote

favorite

I have a live SQL Server 2012 environment that had no SPN registered - this has previously not been an issue as Kerberos authentication wasn't required. But now it is required.

I have configured everything correctly, but after these changes I want to ensure that SQL Server can register the SPN's successfully- preferably without a service restart as this is a live environment.

Is there a way of doing this?

P.S. I know I can register the SPN's manually, but I want to make certain that they can be registered automatically.

sql-server kerberos spn

share|improve this question

asked yesterday

paulH

7851230

add a comment | 

up vote
2
down vote

favorite

I have a live SQL Server 2012 environment that had no SPN registered - this has previously not been an issue as Kerberos authentication wasn't required. But now it is required.

I have configured everything correctly, but after these changes I want to ensure that SQL Server can register the SPN's successfully- preferably without a service restart as this is a live environment.

Is there a way of doing this?

P.S. I know I can register the SPN's manually, but I want to make certain that they can be registered automatically.

sql-server kerberos spn

share|improve this question

asked yesterday

paulH

7851230

add a comment | 

up vote
2
down vote

favorite

up vote
2
down vote

favorite

I have a live SQL Server 2012 environment that had no SPN registered - this has previously not been an issue as Kerberos authentication wasn't required. But now it is required.

I have configured everything correctly, but after these changes I want to ensure that SQL Server can register the SPN's successfully- preferably without a service restart as this is a live environment.

Is there a way of doing this?

P.S. I know I can register the SPN's manually, but I want to make certain that they can be registered automatically.

sql-server kerberos spn

share|improve this question

asked yesterday

paulH

7851230

I have a live SQL Server 2012 environment that had no SPN registered - this has previously not been an issue as Kerberos authentication wasn't required. But now it is required.

I have configured everything correctly, but after these changes I want to ensure that SQL Server can register the SPN's successfully- preferably without a service restart as this is a live environment.

Is there a way of doing this?

P.S. I know I can register the SPN's manually, but I want to make certain that they can be registered automatically.

sql-server kerberos spn

sql-server kerberos spn

share|improve this question

asked yesterday

paulH

7851230

share|improve this question

asked yesterday

paulH

7851230

share|improve this question

share|improve this question

asked yesterday

paulH

7851230

asked yesterday

paulH

7851230

asked yesterday

paulH

7851230

7851230

add a comment | 

add a comment | 

1 Answer
1

active

oldest

votes

up vote
3
down vote



accepted

Quick answer

Question:

I have configured everything correctly, but after these changes I want to ensure that SQL Server can register the SPN's successfully- preferably without a service restart as this is a live environment.

Is there a way of doing this?

Answer:

No.

Explanation

From the official documentation Register a Service Principal Name for Kerberos Connections

A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it is registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration has not been performed or fails, the Windows security layer cannot determine the account associated with the SPN, and Kerberos authentication will not be used.

and

If the server cannot automatically register the SPN, the SPN must be registered manually. See Manual SPN Registration.

and further down in the section Automatic SPN Registration you can find

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/:.Both named instances and the default instance are registered as MSSQLSvc, relying on the value to differentiate the instances.

Summary

The SQL Server Service essentially regsisters the SPN on service start automatically. If you cannot start the SQL Server Service then you will have to perform this task manually.

Considerations

Ensure the service account that start the SQL Server Service is allowed to read and write SPNs.

To do this, you must grant the following access control settings for the SQL Server service account in the Active Directory directory service:

• Read servicePrincipalName


• Write servicePrincipalName

^{Reference: How to use Kerberos authentication in SQL Server (Microsoft Support)}

share|improve this answer

edited yesterday

answered yesterday

hot2use

7,91852052

• 
  
  
  

  
  

  
  
  
  
  
  

  
  As I was expecting, but was hoping otherwise...
  – paulH
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  I know, because I'm currently fighting for Read servicePrincipalName and Write servicePrincipalName permissions at my current company. Once the services have been cycled during a maintenance window, everything should be better. You could test beforehand with a test instance. Assign the rights to the service account and then cycle the service. You should then find a Service Principal Name under the service account by issuing: setspn -l <DOMAIN><ACCOUNT> (Replace <DOMAIN> and <ACCOUNT> with the corresponding service account)
  – hot2use
  yesterday
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f222873%2fsql-server-register-service-principle-name-without-service-restart%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

active

oldest

votes

active

oldest

votes

up vote
3
down vote



accepted

Quick answer

Question:

I have configured everything correctly, but after these changes I want to ensure that SQL Server can register the SPN's successfully- preferably without a service restart as this is a live environment.

Is there a way of doing this?

Answer:

No.

Explanation

From the official documentation Register a Service Principal Name for Kerberos Connections

A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it is registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration has not been performed or fails, the Windows security layer cannot determine the account associated with the SPN, and Kerberos authentication will not be used.

and

If the server cannot automatically register the SPN, the SPN must be registered manually. See Manual SPN Registration.

and further down in the section Automatic SPN Registration you can find

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/:.Both named instances and the default instance are registered as MSSQLSvc, relying on the value to differentiate the instances.

Summary

The SQL Server Service essentially regsisters the SPN on service start automatically. If you cannot start the SQL Server Service then you will have to perform this task manually.

Considerations

Ensure the service account that start the SQL Server Service is allowed to read and write SPNs.

To do this, you must grant the following access control settings for the SQL Server service account in the Active Directory directory service:

• Read servicePrincipalName


• Write servicePrincipalName

^{Reference: How to use Kerberos authentication in SQL Server (Microsoft Support)}

share|improve this answer

edited yesterday

answered yesterday

hot2use

7,91852052

• 
  
  
  

  
  

  
  
  
  
  
  

  
  As I was expecting, but was hoping otherwise...
  – paulH
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  I know, because I'm currently fighting for Read servicePrincipalName and Write servicePrincipalName permissions at my current company. Once the services have been cycled during a maintenance window, everything should be better. You could test beforehand with a test instance. Assign the rights to the service account and then cycle the service. You should then find a Service Principal Name under the service account by issuing: setspn -l <DOMAIN><ACCOUNT> (Replace <DOMAIN> and <ACCOUNT> with the corresponding service account)
  – hot2use
  yesterday
  

  
  
  
  

add a comment | 

up vote
3
down vote



accepted

Quick answer

Question:

I have configured everything correctly, but after these changes I want to ensure that SQL Server can register the SPN's successfully- preferably without a service restart as this is a live environment.

Is there a way of doing this?

Answer:

No.

Explanation

From the official documentation Register a Service Principal Name for Kerberos Connections

A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it is registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration has not been performed or fails, the Windows security layer cannot determine the account associated with the SPN, and Kerberos authentication will not be used.

and

If the server cannot automatically register the SPN, the SPN must be registered manually. See Manual SPN Registration.

and further down in the section Automatic SPN Registration you can find

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/:.Both named instances and the default instance are registered as MSSQLSvc, relying on the value to differentiate the instances.

Summary

The SQL Server Service essentially regsisters the SPN on service start automatically. If you cannot start the SQL Server Service then you will have to perform this task manually.

Considerations

Ensure the service account that start the SQL Server Service is allowed to read and write SPNs.

To do this, you must grant the following access control settings for the SQL Server service account in the Active Directory directory service:

• Read servicePrincipalName


• Write servicePrincipalName

^{Reference: How to use Kerberos authentication in SQL Server (Microsoft Support)}

share|improve this answer

edited yesterday

answered yesterday

hot2use

7,91852052

• 
  
  
  

  
  

  
  
  
  
  
  

  
  As I was expecting, but was hoping otherwise...
  – paulH
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  I know, because I'm currently fighting for Read servicePrincipalName and Write servicePrincipalName permissions at my current company. Once the services have been cycled during a maintenance window, everything should be better. You could test beforehand with a test instance. Assign the rights to the service account and then cycle the service. You should then find a Service Principal Name under the service account by issuing: setspn -l <DOMAIN><ACCOUNT> (Replace <DOMAIN> and <ACCOUNT> with the corresponding service account)
  – hot2use
  yesterday
  

  
  
  
  

add a comment | 

up vote
3
down vote



accepted

up vote
3
down vote



accepted

Quick answer

Question:

I have configured everything correctly, but after these changes I want to ensure that SQL Server can register the SPN's successfully- preferably without a service restart as this is a live environment.

Is there a way of doing this?

Answer:

No.

Explanation

From the official documentation Register a Service Principal Name for Kerberos Connections

A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it is registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration has not been performed or fails, the Windows security layer cannot determine the account associated with the SPN, and Kerberos authentication will not be used.

and

If the server cannot automatically register the SPN, the SPN must be registered manually. See Manual SPN Registration.

and further down in the section Automatic SPN Registration you can find

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/:.Both named instances and the default instance are registered as MSSQLSvc, relying on the value to differentiate the instances.

Summary

The SQL Server Service essentially regsisters the SPN on service start automatically. If you cannot start the SQL Server Service then you will have to perform this task manually.

Considerations

Ensure the service account that start the SQL Server Service is allowed to read and write SPNs.

To do this, you must grant the following access control settings for the SQL Server service account in the Active Directory directory service:

• Read servicePrincipalName


• Write servicePrincipalName

^{Reference: How to use Kerberos authentication in SQL Server (Microsoft Support)}

share|improve this answer

edited yesterday

answered yesterday

hot2use

7,91852052

Quick answer

Question:

I have configured everything correctly, but after these changes I want to ensure that SQL Server can register the SPN's successfully- preferably without a service restart as this is a live environment.

Is there a way of doing this?

Answer:

No.

Explanation

From the official documentation Register a Service Principal Name for Kerberos Connections

A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it is registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration has not been performed or fails, the Windows security layer cannot determine the account associated with the SPN, and Kerberos authentication will not be used.

and

If the server cannot automatically register the SPN, the SPN must be registered manually. See Manual SPN Registration.

and further down in the section Automatic SPN Registration you can find

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/:.Both named instances and the default instance are registered as MSSQLSvc, relying on the value to differentiate the instances.

Summary

The SQL Server Service essentially regsisters the SPN on service start automatically. If you cannot start the SQL Server Service then you will have to perform this task manually.

Considerations

Ensure the service account that start the SQL Server Service is allowed to read and write SPNs.

To do this, you must grant the following access control settings for the SQL Server service account in the Active Directory directory service:

• Read servicePrincipalName


• Write servicePrincipalName

^{Reference: How to use Kerberos authentication in SQL Server (Microsoft Support)}

share|improve this answer

edited yesterday

answered yesterday

hot2use

7,91852052

share|improve this answer

share|improve this answer

edited yesterday

edited yesterday

edited yesterday

answered yesterday

hot2use

7,91852052

answered yesterday

hot2use

7,91852052

answered yesterday

hot2use

7,91852052

7,91852052

• 
  
  
  

  
  

  
  
  
  
  
  

  
  As I was expecting, but was hoping otherwise...
  – paulH
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  I know, because I'm currently fighting for Read servicePrincipalName and Write servicePrincipalName permissions at my current company. Once the services have been cycled during a maintenance window, everything should be better. You could test beforehand with a test instance. Assign the rights to the service account and then cycle the service. You should then find a Service Principal Name under the service account by issuing: setspn -l <DOMAIN><ACCOUNT> (Replace <DOMAIN> and <ACCOUNT> with the corresponding service account)
  – hot2use
  yesterday
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  As I was expecting, but was hoping otherwise...
  – paulH
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  I know, because I'm currently fighting for Read servicePrincipalName and Write servicePrincipalName permissions at my current company. Once the services have been cycled during a maintenance window, everything should be better. You could test beforehand with a test instance. Assign the rights to the service account and then cycle the service. You should then find a Service Principal Name under the service account by issuing: setspn -l <DOMAIN><ACCOUNT> (Replace <DOMAIN> and <ACCOUNT> with the corresponding service account)
  – hot2use
  yesterday
  

  
  
  
  

As I was expecting, but was hoping otherwise...
– paulH
yesterday

As I was expecting, but was hoping otherwise...
– paulH
yesterday

I know, because I'm currently fighting for Read servicePrincipalName and Write servicePrincipalName permissions at my current company. Once the services have been cycled during a maintenance window, everything should be better. You could test beforehand with a test instance. Assign the rights to the service account and then cycle the service. You should then find a Service Principal Name under the service account by issuing: setspn -l <DOMAIN><ACCOUNT> (Replace <DOMAIN> and <ACCOUNT> with the corresponding service account)
– hot2use
yesterday

I know, because I'm currently fighting for Read servicePrincipalName and Write servicePrincipalName permissions at my current company. Once the services have been cycled during a maintenance window, everything should be better. You could test beforehand with a test instance. Assign the rights to the service account and then cycle the service. You should then find a Service Principal Name under the service account by issuing: setspn -l <DOMAIN><ACCOUNT> (Replace <DOMAIN> and <ACCOUNT> with the corresponding service account)
– hot2use
yesterday

add a comment | 

 

draft saved

draft discarded

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f222873%2fsql-server-register-service-principle-name-without-service-restart%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

This page is only for reference, If you need detailed information, please check here