5

I have a SSIS package which is perform data migration between MySQL database and SQL server database. When I manually run it from visual studio or from SQL Server Management Studio, it ran successfully. But when i schedule it using SQL server agent job, an error occur.

Description: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Source: .Net SqlClient Data Provider

What is this error? Why this happen only when scheduled? How to overcome this problem?

3
  • 1
    If it's only happening when you schedule it, then the user you have associated to the job is the issue. You'll need to fix that login. The other thought is the configuration path in the job needs to be adjusted to match the SSIS configuration either in the catalog or in visual studios, wherever you are running it successfully. Apr 18 '18 at 13:23
  • 1
    how is your infrastructure setup? are you 'hopping' between more than two machines? The "ANONYMOUS LOGON" error can suggest a delegation issue. It may be worth checking your SPN's?
    – DimUser
    Apr 18 '18 at 13:29
  • 1
    This might help. blog.sqlauthority.com/2015/06/13/… Apr 20 '18 at 2:46
2

You could be dealing with a delegation/impersonation problem as DimUser suggested.

If your SSIS is just fetching data from one DB server and delivers it to the other the solution is much easier and should be to set up an SQL Server Agent Proxy.

If you are executing the SSIS Package from Visual Studio or SSMS by hand the package will try your Windows credentials to log on to the SQL Server. If your account has the correct rights it will succeed.

If you set up an SQL Server Agent Job to execute that package the service account running SQL Server Agent executes this package. He does not have your Windows credentials so he will try to use anonymous login resulting in this error message.

To enable SQL Server Agent to execute a package using a different account you have to set up three things:

  1. Create a credential
  2. Set up a proxy account
  3. Configure the SQL Server Agent step to use the proxy account

Create a credential Connect to the SQL Server that should execute your package. Right-click Security and select "New Credential...". Enter a descriptive name (Credential name), select the Windows account you intend to use (Identity) and enter the password. Click OK.

For initial testing, you can use your own Windows account. For production use, I would suggest creating a dedicated AD Service Account (with minimal permissions).

Set up a proxy account Expand the SQL Server Agent folder. Open Proxies and right click on "SSIS Package Execution" selecting "New Proxy...". Enter a descriptive proxy name and use the credential you created earlier.

Configure the SQL Server Agent step to use the proxy account Open your Agent Job, select the properties of your step executing the SSIS Package. Now you can select your proxy account in the "Run as:" drop-down list.

Additional setup: If your package is deployed to the SSIS Catalog you need to grant the Windows login you used for the credential the "SSIS_Admin" role on SSISDB as well.

For that, you need to create the account as a regular Windows login in SQL Server (Public) and map the user to SSISDB using the SSIS_Admin role.

Not the answer you're looking for? Browse other questions tagged or ask your own question.