December 17, 2013 at 9:16 am
Excellent information on a topic that causes pain for most DBAs at one time or another. Really nicely done, Derek!
Vince
December 17, 2013 at 9:50 am
SPNs (Server Principal Names) need to be generated for all servers involved. This can be automatic by giving the AD account that is used as the service account the extended permission “write public information”.
You kind of glossed over this. How do I grant those permissions?
December 17, 2013 at 10:04 am
EdgeOfKnowWare (12/17/2013)
SPNs (Server Principal Names) need to be generated for all servers involved. This can be automatic by giving the AD account that is used as the service account the extended permission “write public information”.
You kind of glossed over this. How do I grant those permissions?
Apologies for that (my 1st article). This is an AD level permission that is set for the account to be used as the service account in SQL. The permission is set in the security tab of the user account after clicking the advanced button. In my environment, I needed to work with the system administrators here on. I then used the Kerberos Configuration Manager to create the SPN's without a reboot. The SPNs are created when the service starts otherwise.
Derek
December 17, 2013 at 10:39 am
The delegation tab will also not display if the Domain Level is set too low. Windows 2000 domain level will not support constrained delegation. Even if the domain servers are Windows 2003 server, that doesn't necessarily mean that the functional level of the domain is at a 2003 level. This is something that the AD Administrators would control.
December 17, 2013 at 10:49 am
When the trust for delegation is enabled, can I, as a sysadmin, use SQL Server to impersonate the domain admin account?
We have a vendor that claimed they don't need sysadmin access for their application. However, they did need to use xp_cmdshell. I examined some of their T-SQL (as sa, I was able to un-encrypt their stored procedures). I found that they used xp_cmdshell to run a cscript which then connected back to SQL using the service account. They didn't need sysadmin access because they hacked access to it. I thought it was clever. If delegation was enabled, could they also now impersonate domain admin account?
BTW, the vendor's code was safe in this case. I never challenged the vendor, but I imagine this could have legal implications. We isolated the application so that it can't access other domain resources. If trust for delegation was enabled, would the next patch be sending our data to the web?
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
December 17, 2013 at 11:47 am
rstone (12/17/2013)
When the trust for delegation is enabled, can I, as a sysadmin, use SQL Server to impersonate the domain admin account?We have a vendor that claimed they don't need sysadmin access for their application. However, they did need to use xp_cmdshell. I examined some of their T-SQL (as sa, I was able to un-encrypt their stored procedures). I found that they used xp_cmdshell to run a cscript which then connected back to SQL using the service account. They didn't need sysadmin access because they hacked access to it. I thought it was clever. If delegation was enabled, could they also now impersonate domain admin account?
BTW, the vendor's code was safe in this case. I never challenged the vendor, but I imagine this could have legal implications. We isolated the application so that it can't access other domain resources. If trust for delegation was enabled, would the next patch be sending our data to the web?
Delegation requires the credentials to first be passed to the application or service. So if a domain admin connected, yes, it could be delegated. Highly privileged accounts like this, however, can have a flag in AD toggled which prevents such delegation.
Also remember that delegation only is necessary if you're hopping to another system. If you're staying within the same system, like your example, you don't need delegation.
BTW, the trick they tried... this is yet another reason to go with SQL Server 2008+ with Windows 2008+. You get Service Isolation meaning the connection as the service account in this manner fails.
K. Brian Kelley
@kbriankelley
December 17, 2013 at 11:48 am
Slevin (12/17/2013)
EdgeOfKnowWare (12/17/2013)
SPNs (Server Principal Names) need to be generated for all servers involved. This can be automatic by giving the AD account that is used as the service account the extended permission “write public information”.
You kind of glossed over this. How do I grant those permissions?
Apologies for that (my 1st article). This is an AD level permission that is set for the account to be used as the service account in SQL. The permission is set in the security tab of the user account after clicking the advanced button. In my environment, I needed to work with the system administrators here on. I then used the Kerberos Configuration Manager to create the SPN's without a reboot. The SPNs are created when the service starts otherwise.
Derek
Permission for SPN creation is either Domain Admin, the local System account for the computer, or for it to have been delegated to the account in question. A service account, without any additional permissions, cannot create an SPN.
K. Brian Kelley
@kbriankelley
December 17, 2013 at 11:52 am
rstone (12/17/2013)
When the trust for delegation is enabled, can I, as a sysadmin, use SQL Server to impersonate the domain admin account?We have a vendor that claimed they don't need sysadmin access for their application. However, they did need to use xp_cmdshell. I examined some of their T-SQL (as sa, I was able to un-encrypt their stored procedures). I found that they used xp_cmdshell to run a cscript which then connected back to SQL using the service account. They didn't need sysadmin access because they hacked access to it. I thought it was clever. If delegation was enabled, could they also now impersonate domain admin account?
BTW, the vendor's code was safe in this case. I never challenged the vendor, but I imagine this could have legal implications. We isolated the application so that it can't access other domain resources. If trust for delegation was enabled, would the next patch be sending our data to the web?
That is interesting! Its hard to say for certain because I am not familiar with the specifics but, to your question though. You are enabling delegation at the service account level for SQL Server. That allows the SQL Service account to impersonate the account that is accessing the linked server provided the conditions in the article are met (Windows authentication, Kerberos..etc). In your case, if the SQL Service account was a domain admin (not recommended) then the vendor would be impersonating a domain admin account as well.
Derek
December 17, 2013 at 2:27 pm
I think I was confusing delegation with impersonation. As a sysadmin, I sometimes use EXECUTE AS to impersonate a login to test permissions. In this case, once the application is using sysadmin via script, I suppose it can also impersonate a login. Would the delegation setting affect this? I have never tried to impersonate an account in the domain admin group. Does the delegation setting relate?
Also, our service accounts are not in the domain admin group.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
December 18, 2013 at 12:46 am
Quote: "SPNs (Server Principal Names) need to be generated for all servers involved. This can be automatic by giving the AD account that is used as the service account the extended permission “write public information”."
I thought Create and Delete SPN were sufficient rights for this?
I want this right (Create/Delete SPN) to be granted to all Users in a specific OU holding all our service accounts so that the SPN-part is handled automatically. I have been discussing this with our AD master but we have not found a solution. Anyone here have this access granted pr default?
December 18, 2013 at 7:12 am
patboner (12/18/2013)
Quote: "SPNs (Server Principal Names) need to be generated for all servers involved. This can be automatic by giving the AD account that is used as the service account the extended permission “write public information”."I thought Create and Delete SPN were sufficient rights for this?
I want this right (Create/Delete SPN) to be granted to all Users in a specific OU holding all our service accounts so that the SPN-part is handled automatically. I have been discussing this with our AD master but we have not found a solution. Anyone here have this access granted pr default?
I am not 100% sure, but I believe this depends on the domain functional level. We could not find that permission in our domain, but the aforementioned permission was granted to the AD account and it was able to dynamically register the SPN. Note that this was at the user account level. This article may help you understand a bit more about your situation though. As a proof of concept, did you try to set the permissions for one user account and see if you can get it to work? Don't forget that you may need to flush your credential cache while testing (klist purge).
Derek
December 18, 2013 at 7:19 am
rstone (12/17/2013)
I think I was confusing delegation with impersonation. As a sysadmin, I sometimes use EXECUTE AS to impersonate a login to test permissions. In this case, once the application is using sysadmin via script, I suppose it can also impersonate a login. Would the delegation setting affect this? I have never tried to impersonate an account in the domain admin group. Does the delegation setting relate?Also, our service accounts are not in the domain admin group.
I have not noticed any difference in impersonation, but I did not test that out. If I understand correctly, you would been a login of a domain admin who is not a sysadmin on your SQL server. You would then take your login who is a sysadmin and impersonate the domain admin's account. That should work, but I do not know to know extent. For instance, can you use xp_cmdshell to gain access to another server while impersonating this domain admin account? Awe...now I wish I had time to play!
Derek
December 18, 2013 at 7:31 am
Hi Derek!
Yes, it works for one user. My problem is that I would like this right to be granted to a OU and not to an individual user. Kind of executing Step 3 in the article for an entire OU. This so that every time a service account is created it has the necessary rights to register its SPNs. Interested in learning if other people do this manually for each service account or solve it in a completly different way.
🙂
December 18, 2013 at 7:46 am
patboner (12/18/2013)
Hi Derek!Yes, it works for one user. My problem is that I would like this right to be granted to a OU and not to an individual user. Kind of executing Step 3 in the article for an entire OU. This so that every time a service account is created it has the necessary rights to register its SPNs. Interested in learning if other people do this manually for each service account or solve it in a completly different way.
🙂
Hey!
Does the permission exist at the OU level? If so, try granting it. Assuming these user accounts are in that OU, you could go to the security tab for a user in that OU. Under effective permissions, they should have propagated down. You will want to allow a few mins for replication between domain controllers to occur as well. Can you manually create the SPNs using the Kerberos Configuration Manager that I mentioned in the article? Thats a good way to see if the permissions are applied.
Derek
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply