BCP works in 2016 but not in 2022 during migration.

Question

BCP works in 2016 but not in 2022 during migration.

Jeff Moden

SSC Guru

Points: 1004392
More actions
June 30, 2023 at 4:06 pm

#4223577

We're migrating from SQL Server 2016 to 2022.
Our method is to stand up a new box and use the backup restore method of migration along with privs transfers, job copies, etc.
We were successful in doing so on our Dev box.
We're not successful on our staging box. The old 2016 box works fine. The new 2022 box give us the following error when running procs that make a call to xp_CmdShell to run BCP. Here's the error.
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.
And, BCP was setup with a proxy, the stored procedure contains WITH EXECUTE AS OWNER, and the database owner is "sa".
And, yes, we did the SETSPN thing to make sure. The old box still runs correctly but the new box doesn't when it comes to BCP via an sp_CmdShell call. And, we need it to work that way because we have way too much code that would need to be changed and tested to do anything different at this point.
I'm at wits end and could sure use some ideas. Thanks for any help.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16162 More actions · Answer 1

loopback? have a look at https://blog.sqlauthority.com/2017/04/18/sql-server-login-failed-login-untrusted-domain-cannot-used-windows-authentication/

and https://arslan-bobir.medium.com/sql-server-troubleshooting-resolving-the-login-is-from-an-untrusted-domain-and-cannot-be-used-7a1a6c715817

and... probably not but worth looking at.

https://learn.microsoft.com/en-us/windows/security/threat-protection/security-policy-settings/network-security-configure-encryption-types-allowed-for-kerberos - if a new server it is likely that RC4_HMAC_MD5 is disabled - if so try enabling it.

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 2

Thanks, Frederico. One of the other ones I looked at said to change all the code to use a name and password. The one for Pinal Dave with the download link (which I found previously) looks promising and I'll make the download to see if it tells us anything that we don't already know.

I really appreciate the response.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 3

Well, we solved it.

To summarize, everything with the actual migration worked perfectly except for one thing (ok, call it two.. I bunched it together as "comms with other machines"). Neither BCP nor BULK INSERT would work when trying to get data from a different machine when trying to use an alias on the new machine so that people wouldn't have to change what or how they do things in code. The alias was dropped from the old machine and created on the new machine.

We also follow the "Best Practice" policy of disabling the SQL Browser service and we do so during the initial "standup" of the "box". It turns out that turning it off too early was the issue.

With that in mind and to make a really long story mush shorter...

Before you create a new alias on a machine, start the SQL Server Browser.
This is the step that was missing for us. Everything else below is what just about everyone already knows. It is speculated that this step is required so the alias being setup is registered on the Domain Controller as a "trusted machine".
Add the new alias.
Do the necessary SETSPN things (definitely not in my wheel house where I work)
We rebooted the box after that. Not sure a reboot is necessary but the rebooted it anyway.
Once back up, we stopped and disabled the browser.
With the browser disabled, we rebooted the box once more just to prove that the alias would continue to work even though the browser was disabled, and it did. YAAAAAAY!!!!

The steps to create an alias are out there on the internet but the "official" ones and most of the takeoffs from those don't contain that critical first step of making sure that the SQL SERVER BROWSER service is running before you start. I think this omission might be because SQL Server defaults to being "Running" after installation of SQL Server.

I say "most" above but haven't found a one to be an exception from "All". I only say "most" because one of our infrastructure folks said he thinks he remembers some answer to a post somewhere saying that it needed to be on.

We're not done testing everything, yet, but what absolutely wasn't working suddenly started working after the steps above. I'm hopefully optimistic, to say the least!

Frederico... thank you again for trying to lend a hand. I appreciate it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)