SQL Server Agent - Untrusted domain message

Question

Running SQL Server 2016. Currently have a solution that is hosted in one domain and of course our access point is in another. we need to pull data in an automated fashion.

We have added a windows credential with the credential manager which collects endpoint information and a set of credentials.

e.g.

• Internet or Networkaddress: mydatabase.remotedomain.com
• Username : remotedomain\username
• Password : password

This solution works with many tools, Excel, SSMS direct query, Visual Studio. The user enters the endpoint (server url or IP/port) and uses windows integrated security. the connection is made and credential store does the trick and user is authenticated.

SSMS example

• Server name: mydatabase.remotedomain.com
• Authentication: Windows Authentication

My challenge is SSIS and SQL Agent. The SSIS package runs in VS2015. deploy the package to Integration Services Catalog - highlight package and execute and it runs.

Create a SQL Server Agent Job and execute the job and I receive this gift.....

Login Failed: The login is from an untrusted domain and cannot be used with Windows authentication

I have created a SQL credential, created a Proxy (SSIS Package Execution), created a job that uses the Run As with the Credential but this ends with the same result. The credential has to be in my local domain or the job wont run....and of course localdomain\username does not authenticate against the remote data connection. So Proxy does not help the situation.

What I was expecting is that the windows credential manager would swap the credentials as it does when the job is run manually, or through excel or a number of any other ways...

Answer 1

I opened a ticket with Microsoft and worked with one of their senior resources on this.

this appears to be a bug in SQL Agent. There is no known reason or issue that prevents SQL Agent from picking up the remote credential from the Windows Credential Store, but it is not.

A working alternative was to use the command line utility DTEXEC. some slight modification to the SSIS project to make sure all connection managers are at the package level instead of project (created a reference issue).

this solution is not ideal, but it worked and DTEXEC allowed SSIS to pickup the required credential in the store and execute without issue.

I will follow up once Microsoft completes their research and gets back to me, the ticket is still open.

Answer 2

Sorry but changing the group to Global or Universal for the local AD account is not having any effect. I am bit lost on how making a change on the local account in use for SQL Agent will make any difference. The solution works in all the tools by local account substitution with the remote account setup in Credential Manager. If I missed it, and making this change should work, please provide an example of the setup if possible.

Again it appears this process is not being executed/followed by SQL Server Agent since it works everywhere else but not in a job executed by the Agent.

so again my hope is somebody has seen something like this before and has a solution.

my end goal is to just automate pulling data from a remote SQL server where there is no trust. I was hopeful that the proxy solution would work, but when you set the credentials to the remote domain\username, the job wont even execute.

Is there a way to setup my connection in the SSIS package to expressly set the credentials to the remote domain\username\pwd. I took a stab at that and couldnt get that to fly either. if so, an example is priceless to me.

I dont care how i get to the goal line, just need to...thanks all for the help

Answer 3

Your window credential account should be an AD user account which is in a Group with a scope of either Global or Universal. Universal groups are useful where you have multiple domains.

The process will execute in whatever context it's called in (i.e. by you, SQL Agent, or the proxy account). It doesn't change executable context as it calls different processes, unless you pragmatically make it, and that's bad idea anyway...

Had similar issue and it was a nightmare to resolve! Learned a lot of fun AD configuration tips along the way.

Understanding User and Group Accounts states the following:

Groups can have different scopes—domain local, built-in local, global, and universal. That is, the groups have different areas in which they are valid.

Global groups:

Groups that are used to grant permissions to objects in any domain in the domain tree or forest. Members of global groups can include only accounts and groups from the domain in which they are defined.

Universal groups:

Groups that are used to grant permissions on a wide scale throughout a domain tree or forest. Members of global groups include accounts and groups from any domain in the domain tree or forest.

EDIT

If it's just a data pull from one domain to another, can the data be first exported to a csv in the untrusted domain and then SFTP'd into your environment where the TL(Transform and Load) of the ETL(Extract-Transform-Load) process could take place?

SSIS would be a good tool for this, but C# and Powershell could also be used.