locked
Problem Authenticating (SQL Server 2008) RRS feed

  • Question

  • Hello,

    I am having an issues when i run reports on SQL Server 2008 Reporting
    Services and when running Maintenance plans with in the database
    engine.  My database engine,reporting and agent are all running as the
    same domain account and it is for the same domain that the server and
    all client machines are on.

    I get the following error when ever I try to run a report or execute a
    maintenance plan: 


    "Login failed. The login is from an untrusted domain and cannot be
    used with Windows authentication." 



    I do not know whey it would be reporting that the domain is
    untrusted...it is the domain that the system belongs to so there
    should be a trust relationship there and there is only the single
    domain in our environment. 


    Thanks for your help,
    Patrick
    Friday, October 10, 2008 6:18 PM

All replies

  • Your connection strings are probably not fully qualified. i.e. Doman\Login

     

    It's probably just reading the login on the local machine.

     

    A.D.T.

     

    Friday, October 10, 2008 6:21 PM
  • I have confirmed that when I run reports they either running as the service account (which is a domain account that is fully qualified) or as a fully qualified domain account. The same is true for the maintenance plan.  I have included the full error message from the Maintenance Plan history below.  the user ADS\dbact is the service account for this instance of SQL Server.

    Thanks,
    Patrick



    Date        10/10/2008 2:43:53 PM
    Log        Job History (Daily Full User DB Backups.Subplan_1)

    Step ID        1
    Server        SQL08-TEST
    Job Name        Daily Full User DB Backups.Subplan_1
    Step Name        Subplan_1
    Duration        00:00:04
    Sql Severity        0
    Sql Message ID        0
    Operator Emailed       
    Operator Net sent       
    Operator Paged       
    Retries Attempted        0

    Message
    Executed as user: ADS\dbact. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.1600.22 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  2:43:53 PM  Error: 2008-10-10 14:43:56.87     Code: 0xC00291EC     Source: {4E3EF9A8-5D21-46F6-AAA9-AC6918845FD4} Execute SQL Task     Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection.  End Error  Warning: 2008-10-10 14:43:56.87     Code: 0x80019002     Source: OnPreExecute      Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.  End Warning  Error: 2008-10-10 14:43:57.21     Code: 0xC0024104     Source: Back Up Database Task      Description: The Execute method on the task returned error code 0x80131904 (Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.). The Execute method must succeed, and indicate the result using an "out" parameter.  End Error  Error: 2008-10-10 14:43:57.24     Code: 0xC0024104     Source: {4B65BEDE-D274-4B06-8CA7-A7DB4DE235C3}      Description: The Execute method on the task returned error code 0x80131904 (Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.). The Execute method must succeed, and indicate the result using an "out" parameter.  End Error  Warning: 2008-10-10 14:43:57.24     Code: 0x80019002     Source: OnPostExecute      Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.  End Warning  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  2:43:53 PM  Finished: 2:43:57 PM  Elapsed:  3.421 seconds.  The package execution failed.  The step failed.
    Friday, October 10, 2008 6:49 PM
  • Has anyone run into this issue before and/or now what the cause is?
    Monday, October 13, 2008 3:40 PM
  • I created a new SQL Login (not a domain or local machine account) and
    made it part of the sysadmin role.  Then I went into the connection
    properties for my db connection in the Maintenance Plan and changed
    the log in credientials from using Windows NT Integrated
    Authentication to using the new user i just created.  After doing so
    everything worked as it should.

    I am really confused...
    Thursday, October 16, 2008 7:43 PM
  • Hello, I have the same problem, but I'm going to give you a possible clue:

    I have SQL Server 2005 installed in 3 machines.

    I've updated them to SQL 2008.

    I have no problem to connect to any of them since my VB6 application.

    But I have an instance who has been installed as SQL 2008 at once.

    This instance, has the problem you've descripted.

    I have to use this provider in SQL 2008: SQLNCLI10.1

    because the provider I use in SQL 2005 doesn't work (SQLOLEDB.1)

    Another strange thing is: when I make a UDL file (you can do it by creating a text file and changing its extension from txt to udl, then you can fill it by doble-click on it), the UDL connects with the database, but when I try to use that udl to connect throw a VB6 application, I see the error you've descripted.

    Please, if you find the answear, send it !!

    Thanks.

    Wednesday, June 17, 2009 7:42 PM
  • Hi !!!
    I've found the solution !!
    :) :) :)
    When I try to connect to SQL Server 2008 I make an UDL file to do that. You can make an UDL file by creating a text file and changing its extension to udl. Then you can open it and try to connect to your database. If you press Ok, the data is saved into the udl file like if you were written the ConnectionString.
    To make the connection to a SQL 2008 instance which wasn't an update from SQL2005 I can't use 'SQLOLEDB.1' provider (I don't know why), I have to use 'SQLNCLI10.1' instead.
    The only i've found on the net related to this subject is that SQL Server has been configured to use only Windows accounts for connecting to the database. But my SQL 2008 instance has the mixed configuration.
    The problem was the udl doesn't save the ConnectionString well.
    There were several errors:
    - The password wasn't saved.
      To solve this, you have to edit the udl with notepad and add this: Password=xxx
    (Where xxx is your password)
    - When you choose "Allow saving password"
      the UDL has to save this: "Persist Security Info=True"
      but instead of that it saves this: "Persist Security Info=False"
      To solve this, you have to correct it.
    - And at last, this is saved:Integrated Security="".
      To correct it, delete it.

    The final ConnectionString have to be like this:
    Provider=SQLNCLI10.1;Password=xxx;Persist Security Info=True;User ID=yyy;Initial Catalog=zzz;Data Source=sss;Initial File Name="";Server SPN=""

    where xxx is your password,
    yyy the user and
    sss the sqlInstance
     
    I hope this could help other people !!
    See you. Bye

    Wednesday, June 17, 2009 9:49 PM
  • Yes, that helped me very much. Connection string needs to be in the form:

    Provider=SQLNCLI10.1;Persist Security Info=True;Database=dbname;Data Source=IPaddressOrComputerName\SQLEXPRESSinstance;Uid=userid; Pwd=yyyyyy;

    I am not using UDL and am using this connection string in VB6 code with ADO.

    Thursday, June 24, 2010 7:40 PM
  • There is a whitepaper that covers this. See Manage Kerberos Authentication Issues in a Reporting Services Environment at http://technet.microsoft.com/en-us/library/ff679930(SQL.100).aspx.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Friday, June 25, 2010 6:06 PM
  • Hello experts,

    I've got the same issue if I try to connect my DEV server from my local machine through SQL Management Studio 2008.

    I didn't set any connection string or something like that. Could someone transfer this solution for me, please?

    Thanks!

    Tuesday, February 22, 2011 7:45 AM