August 14, 2018 at 11:54 am
We just replaced a C-NAME with an AG listener and users who were able to bulk insert started failing with Access Denied. We finally determined that this only happens through the listener and if the primary replica is accessed directly by host name bulk insert works. Kerbos delegation has been set on both the service account for the SQL server and on the computer account for the listener. I have not found much other information on the topic so far and just curious if anyone has run into this issue and solved it.
August 14, 2018 at 12:20 pm
I haven't run into this, but hope the below references have something useful for you to troubleshoot the issue.
https://dba.stackexchange.com/questions/165501/configure-unconstrained-delegation-for-bulk-insert
https://thesqldude.com/2011/12/30/how-to-sql-server-bulk-insert-with-constrained-delegation-access-is-denied/
August 15, 2018 at 7:55 am
I finally got bulk insert working through the AG listener; unfortunately I don't know exactly which of the changes made the difference. Many of the changes were in AD so they did not take effect immediately, but I will list everything that is in place now.
SQL Service account has full control in the directory the bulk insert file is located (I don't think this is necessary?)
SQL Service account has kerbos delegation
SQL Service account has netbios and FQDN SPNs for the server and all AG replicas
Listener computer account has kerbos delegation
All replica computer accounts have kerbos delegation
August 20, 2018 at 2:14 pm
Odd behavior, but good to know. Do bulk inserts via listener update both nodes at same time or something?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply