Question :
I have a backup repository on a different domain than the one where I have SQL Server. Is there any way I can define the credentials to access the repository on the backup itself? (domains are isolated from each other)
Answer :
Your best bet is to try and make the file share accessible to the service account that’s running SQL Server. This is the easiest and most reliable method. But if you can’t do that, this is the only thing I can think of that will work. It assumes you are on windows but it may be even easier if you are on linux.
This link: https://sqlsunday.com/2021/09/02/set-up-access-to-network-shares/ has an excellent guide to how to do this. Essentially, you need to add the Domain2user credentials to the local windows credential store (which is maintained per user). Then when the service account on SQL tries to access the file share, the credential you’ve stored will be used.
The tricky part is making sure that the SQL Server Service Account is used when adding the credential to windows, but the article goes into detail on a few ways to do it. My basic steps are:
Step 1: Get the appropriate Domain2 user and password, make sure it has permissions to the target.
Step 2: craft the CMDKEY command:
cmdkey /add:someserver.mycompany.local /user:networkuser /pass:strongPas$w0rdHere
NOTE: I prefer to save this as a batch file temporarily for step 3.
Step 3: make a CMDEXEC job and make it run the batch file above. Delete the job and batch file when done.
Step 4: Run backup job.
Step 5: Success.