SQL Server Performance

Jobs fail after W2003 SP1 install

Discussion in 'SQL Server DTS-Related Questions' started by andydoyal, Mar 15, 2006.

  1. andydoyal New Member

    My company currently has several SQL Server instances running across multiple platforms, providing backend support for hundreds of custom apps, as well as Blackberry service and company wide intranet and HR data.
    We just installed Windows Server 2003 SP1 to one of our production boxes, and restarted as usual, however, now all the scheduled jobs that require external data source connections are failing with this error:

    "Executed as user: STARsvc_data12. DTSRun: Loading... Error: -2147467259 (80004005); Provider Error: 18452 (4814) Error string: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed."

    It seems like the OS on the box is taking away all the Windows account permissions to the SQL Agent service account, which is a Windows Domain account, as well as a local Admin on that box.

    Microsoft doesn't have a clue what happened. Any thoughts?
  2. dineshasanka Moderator

  3. andydoyal New Member

    Its a dedicated MSSQL box; Windows 2003 Server w/ 4GB RAM, quad processors. We are running SQL 2000 Enterprise Edition (SP3), we have not tested SP4 in the environment yet, but it is a possibility.
    Has anyone heard or experienced anything like this? I can't believe we are the only organization that installed SP1 on a W2003 Server w/ SQL 2000 on it...

    Frustrated...

    Please help!!
  4. Twan New Member

    is STARsvc_data12 a local user on the box or a domain user? where is this server placed in relation to its DC (assuming it is in a domain?) any firewalls, etc.?

    Cheers
    Twan
  5. andydoyal New Member

    The STARsvc_data12 user is a Domain account, but also a member of the Local Administrators group on the server. It is the account that starts SQLServer Service, as well as the Agent service, and is responsible for executing all the scheduled DTS jobs on the server.
    The DC is in the same data center as the servers, I'm not sure exactly what the network path consists of between the two.

    I have reset the password for the account, as well as the steps included in Microsoft KB article #319723 related to registering the SPN for the server in the Domain. I can run the DTS packages locally from the Data Transformation ServicesLocal Packages through Enterprise Manager logged in either as myself OR svc_data12, but cannot run the scheduled jobs in ManagementSQL Server AgentJobs with either account.

    This was not an issue before W2K3 SP1 was applied to the OS...!
  6. Twan New Member

    sorry, not sure what may be happening... who is the owner of the jobs? what happens if you set the job's owner to sa?

    Cheers
    Twan
  7. andydoyal New Member

    I don't believe that will accomplish anything, as the jobs are run by the Agent service, not the job owner. Technically, I am the job owner since I scheduled them once the packages were created and tested.

    I cannot change the Agent service account to a SQL only account as it has specific permissions to all the data files and other remote sources addressed in the DTS packages through the Windows Domain. Changing the account would result in other security issues on those remote sources.
  8. Twan New Member

    Hi ya,

    I realise that, but I have found that if my domain account is the owner of a job then I do get problems when trying to run the job, can't remember off hand what the error message was, but something about accessing the server... So yep only talking about the owner of the job, not the agent, and yes if it still doesn't work then it can easily be set back to yourself as the owner...

    Cheers
    Twan
  9. andydoyal New Member

    Some of the jobs are owned by me, some by another DBA I work with, and some by other developers who created the packages.
    I changed the ownership of one job to the svc_data12 account just to verify it didn't make a difference, and it still fails with the same message.

    [B)]
  10. satya Moderator

    Are you running the job using any specific user account within the SQL agent?
    It is better to run under context of SA and ensure SQLAgent has required privileges to perform the DTS package activities.

    And also ensure similar levels of MDAC version is available between the servers that are used in DTS.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  11. andydoyal New Member

    We used that option as a work around a couple years ago when a similar issue came up, but changed the policy after some security issues issue arose with the auditors. I know the jobs will execute if run by sa, but company policy will not permit the SQLAgent to be the sa account. It must be a Windows Domain account in order to access all the remote data sources throughout the organization.
    I repeat...this WAS NOT an issue before W2K3 SP1 was installed on the server. Prior to that , all jobs completed as designed and scheduled.

    As a last resort, we may have to rollback the server to before the SP1 install.
  12. satya Moderator

    This from one of the MS sites:
    What security features will Windows Server 2003 SP1 include?
    A: Windows Server 2003 SP1 will include a new roles-based Security Configuration Wizard (SCW), enhanced support for 32-Bit applications on Itanium 2 servers, support for client network isolation so that Windows 2003 SP1 machines can prevent clients from accessing a corporate network until their security state is verified, IIS 6.0 improvements, and a VPN Quarantine feature called Network Access Protection (NAP) will let remote Windows clients safely access network features.

    I would take a guess to approach MS support in this regard than rollback of SP1 on windows which is a major step backwards.

    HTH

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  13. andydoyal New Member

    UPDATE 3/23/06 -

    After spending hours on a LiveMetting session w/ MS Support and being unable to locate anything wrong, and all testing of new jobs proving to be successful, we finally just deleted the scheduled jobs and re-scheduled them, which seemed to work for most of the failing jobs.

    For those jobs that it did not resolve the issues, we found that the connection properties in the DTS packages were pointing back to the local server, but using the server domain alias name "Trinity", instead of the FQDN. On a hunch, I changed the alias to the FQDN, and miraculously the jobs completed successfully!!

    Don't have a logical explanation yet, but MS now knows the results and is investigating. We "think" that since the alias name did not have an SPN registered with the Domain, that when the SQL Agent account tried to log in to the server, it couldn't find it, even though it was a member of the server's Local Admin group.

    Beware of W2K3 SP1...

Share This Page