none
Untrusted domain problem RRS feed

  • Question

  • Hi,
    I have an SSIS 2005 package which connects to a bunch of data sources including a SQL Server 2008 instance, which creates problems. It has been running fine for months now. All of a sudden, I started to get the following error:

    Error: 2009-07-29 21:00:01.97     Code: 0xC0202009       Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication .".  End Error  Error: 2009-07-29 21:00:01.97     Code: 0xC020801C     Source: factSales OLE DB Source [5610]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "dw1.Sales" failed with error code 0xC0202009.  There may be error messages posted before this with more ...  The package execution fa...  The step failed.,00:00:02,0,0,,,,0

    Does anyone have any experience with this problem?

    Your help is much appreciated, thanks!
    Thursday, July 30, 2009 5:13 PM

All replies

  • How are u executing ur package?
    are u able to run the package from BIDS?
    Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.
    Thursday, July 30, 2009 5:18 PM
  • What is your protection level of the package?
    are you using a password to open your package
    how are you calling yopur package?

    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post
    Thursday, July 30, 2009 5:22 PM
  • I haven't seen that error before and my first thought is to look into possible changes around the connection string the package is using, or changes to the server running the package that for some reason is causing the sql server DB to see it as out of its AD domain.

    Probably doing some research on that DB error could shed some other light. A quick search revealed this:

    http://blogs.msdn.com/sql_protocols/archive/2008/05/03/understanding-the-error-message-login-failed-for-user-the-user-is-not-associated-with-a-trusted-sql-server-connection.aspx


    Rafael Salas | Don’t forget to mark the post(s) that answered your question http://rafael-salas.blogspot.com/
    Thursday, July 30, 2009 6:55 PM
    Moderator
  • Do you have a domain adminstrator who can check it out?

    Test this: Connect to that SQL 2008 server outside of SSIS using the context that SSIS would be running under (be it the SQL Agent service account, or a Proxy account) to connect to the same server as in your "factSales OLE DB Source" connection.

             RunAs /user:domain\user sqlcmd.exe -Syour2008servername -E -Q"Select @@servername"
             (you will be promted for the user's password.)

    If sqlcmd gets the same login failure message, then your windows authentication is not working to that particular server.

    The message is pretty rare, but in support we have seen it from time to time.

    1. Maybe the domains trust was changed in the domain controller - is the remote SQL 2008 in a separate domain than the machine where SSIS is running from?
    You can ping the 2008 servers's machine name, and the domain name will be returned.

    2. Domain authentication with kerberos requires the SPN for the SQL 2008 instance you connect to to be working.

    Wierd authentication things happen when you have a SPN for the SQL 2008 server that is missing, incorrect, or duplicated.
    You can download SetSPN from here http://www.microsoft.com/downloads/details.aspx?FamilyID=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46&displaylang=en

    More about kerberos and SPNs
    http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx

    To list the SPN's for the SQL 2008 instance, do "SetSpn -L your2008serviceaccount"
    You would need to have a domain admin set the SPN up as per these guidelines, 1 based on the SQL Server's Fully qualified domain name w/Tcpip port, 1 based on the Sql Server's netbios name w/tcpip port, both referencing the SQL Server service startup account as listed in services.msc. http://msdn.microsoft.com/en-us/library/ms191153.aspx

    3. Could be that there are multiple domain controllers, and the replication between then is out of sync. "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication" The domain admin could check it out. Wait an hour and if it still happens, probably not replication being behind.

    If all else fails, call support, and we can check your SPN configuration, run a netmon trace, and check the security event log for other clues.

    Thx, Jason
    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance
    Friday, July 31, 2009 5:02 AM
  • Years late, but I popped up with a similar problem.

    In my case I was using a network alias for the server.  SQL was fine with the alias but when I would run a DTS package from a scheduled job using the SQL Agent account, I would get the LOGIN FAILED, UNTRUSTED DOMAIN message.

    In this case, it turns out the alias had not been made a trusted member of the domain (since it wasn't a real server).  I'm guessing since it was run outside of the SQL envriroment it required an additional NT Security check which failed not because the login or NT token were invalid, but because that SERVER ALIAS was not allowed to submit a login request to a Network resource. 

    I was looking at logins when I should have been checking that my runat Server was a trusted member of the Domain.

    • Proposed as answer by Joe Zamora Wednesday, October 10, 2012 1:18 AM
    Tuesday, January 24, 2012 8:28 PM
  • We also faced the same problem, Executed from BIDS working fine but when we deployed it in SQL Server, it didn't. Replaced SQL Server name with IP address then this issue got resolved.

    Wednesday, January 20, 2016 10:52 PM
  • This is probably because your SPN of SQL Server is registered in Active Directory with some other name and you trying to connect with a name that resolves to the IP of SQL Server but don`t recognize it.

    Reference: https://support.software.dell.com/pt-br/kb/92135


    Felipe Lauffer MCSA: SQL Server | MCP


    • Edited by FLauffer Thursday, January 21, 2016 1:14 AM
    Thursday, January 21, 2016 1:13 AM