Post reply

SSIS connection strings for non domain machines

  • February 8, 2008 at 6:57 am

    #186357

    We have an SSIS package that im creating which connects to a sql server that isnt on the domain (192.168.0.2). In the package on the connection i put in the username and password, click save password and all ok. I can run the job from BIDS no issue.

    When i import this into our main sql box (using file system storage) if i try and validate the package it fails with "Executed as user: WILMSLOW\SYSSQL. ...on 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 13:38:34 Error: 2008-02-08 13:38:34.95 Code: 0xC0202009 Source: Export Mortgage CreditRisk Connection manager "Connection 1" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Login failed for user 'sa'."

    It just doesnt seem to store the password whatever i try.

    I know its probably really simple but can someone help me out?

    Cheers, John

  • February 8, 2008 at 7:12 am

    #776405

    Store the package in msdb and use the "Rely on server storage and roles for access control" option 😀

    Tommy

    Follow @sqlscribe

  • February 8, 2008 at 7:15 am

    #776412

    Tommy Bollhofer (2/8/2008)

    Store the package in msdb and use the "Rely on server storage and roles for access control" option 😀

    Cheers tommy. I will try that. Is that the only way to do it though? Thats a real disappointment if it wont work as a file stored package.

  • February 8, 2008 at 7:26 am

    #776419

    There are several alternatives. Have a look at this article -

    http://www.databasejournal.com/features/mssql/article.php/3619166

    Tommy

    Follow @sqlscribe

  • February 8, 2008 at 9:05 am

    #776479

    Tommy, i think ive sorted it. I read the link you sent me and a few of the articles around it and even though it didnt help directly i got around it. I changed my packagesecurity to be passwordDriven which i quite like. The thing that did the trick in it remembering the password was entering the password through the properties window on the right. before i was trying to save it by right clicking on the connection and going to properties and ticking "save password". this doesnt seem to work once you leave the test session.

    Thanks for your advice.

  • February 8, 2008 at 9:10 am

    #776482

    Glad to hear it 🙂

    Tommy

    Follow @sqlscribe

  • February 28, 2008 at 7:30 pm

    #783565

    Hello guys, i'm getting a similar kind of error. I've 2 packages, the first one calls the other, both are password driven and have the same pwd. The first package is kicking off correctly, executes several tasks and after it calls the next package using "execute package" task, the package starts running , it successfully executes 1st step which is a script task and then it fails trying to execute a "execute sql task' with the following error:

    Error code: 0x80040E4D. An OLE DB record is available.....login failed for user id...blah blah blah...

    any help is greatly appreciated.

    Thanks

  • February 29, 2008 at 9:29 am

    #783903

    Are you using SQL or SSPI authentication for the OLEDB connection in your second package? Are you storing the package in msdb or on the file system?

    Tommy

    Follow @sqlscribe

Viewing 8 posts - 1 through 7 (of 7 total)

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