Post reply

Question regarding ANONYMOUS login within a Backup and Restore Package

  • November 26, 2015 at 3:55 am

    #306669

    Hello

    I have a package with 3 tasks

    1. Backup a database to a specific location on our network (Back up Database Task)

    2. Restore the database to another SQL instance (Execute SQL Task)

    3. Apply some user based security to the instance on the other server (Execute SQL Task)

    Both 1 and 2 use connections that Use Windows Authentication

    I've deployed this package and it's visible, as a package, within Integration Services Catalogs

    If I run this on the server, from Visual Studio (literally right click and execute the package), it runs with no issues

    From the server, If I run this via Integration Services Catalogs (again, right click and Execute), it runs with no issues

    If I run this via Integration Services Catalogs, from a locally installed SSMS instance with a connection to the server, it fails when performing the restore

    If I add this package as a step within a job, the restore task fails

    I get the following issue:

    SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.

    An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D

    Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.".

    A bit of research suggested setting NT AUTHORITY\ANONYMOUS LOGON up with a server role as SysAdmin

    That actually works and the full package executes without any problem, but this solution doesn't seem right to me

    Is it the right approach?

    If not, is there something else I should be doing?

    Thanks

    Damian.

    - Damian

  • November 26, 2015 at 7:59 am

    #1842514

    Looks like the restore part does not work after all

    I have absolutely no idea why??

    For the restore part, I am now getting "Login failed for user '...$'

    I have no idea where it is picking this up from though

    Any thoughts?

    Thanks

    - Damian

  • November 26, 2015 at 8:12 am

    #1842515

    First, It is not advisable for you to mention your server/ credential names in a public forum 🙂

    The issue happening for you here is the delegation does not work for the package when connecting from a different server.

    you can read more here.

    https://www.mssqltips.com/sqlservertip/2312/understanding-when-sql-server-kerberos-delegation-is-needed/

    as a temporary work around, why don't use saved credentials.

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • November 26, 2015 at 8:22 am

    #1842519

    Thanks

    When you say saved credentials, do you mean SQL authentication?

    - Damian

  • November 27, 2015 at 7:43 am

    #1842632

    sorry for replying late.

    As a temporary workaround you can use SQL authentication saved inside your connection manager. Delegation issues happen only when you connect from a different machine. Why don't you try creating an SQL agent job on the server and try running it manually through ssms

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

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

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